Comment résoudre l'erreur « annulation de l'instruction due à un conflit avec la récupération » lors de l'interrogation du réplica en lecture pour mon instance DB RDS pour PostgreSQL ?

Brève description

Cette erreur peut se produire en raison du manque de visibilité de l'instance principale sur l'activité qui se produit sur le réplica en lecture. Le conflit avec la récupération se produit lorsque les informations WAL ne peuvent pas être appliquées au réplica en lecture car les modifications peuvent entraver une activité qui se produit sur le réplica en lecture.

Par exemple, supposons que vous exécutiez une instruction DROP sur l'instance principale lorsqu'une instruction SELECT de longue durée s'exécute sur le réplica en lecture de la table supprimée sur l'instance principale. Le réplica en lecture dispose alors de deux options :

• Attendre que l'instruction SELECT soit terminée avant d'appliquer l'enregistrement WAL. Dans ce cas, le délai de réplication augmente.
• Appliquer l'enregistrement WAL, puis annulez l'instruction SELECT. Dans ce cas, vous obtenez l'erreur «annulation de la déclaration en raison d'un conflit avec la récupération ».

Le réplica en lecture résout ces conflits de réplication en fonction de la valeur des paramètres max_standby_streaming_delay et max_standby_archive_delay. Le paramètre max_standby_streaming_delay détermine la durée pendant laquelle le réplica en lecture doit attendre avant d'annuler les requêtes standby entrant en conflit avec les entrées WAL qui sont sur le point d'être appliquées. Si l'instruction conflictuelle est toujours en cours d'exécution après ce délai, PostgreSQL annule l'instruction et émet le message d'erreur suivant :

ERROR: canceling statement due to conflict with recovery

Cette erreur est généralement due à des requêtes qui mettent trop longtemps à s'exécuter sur le réplica en lecture.

Dans l'exemple précédent avec l'instruction DROP, la requête DROP est stockée dans le fichier WAL pour être appliquée ultérieurement sur le réplica en lecture par souci de cohérence. Supposons qu'une instruction SELECT soit déjà en cours d'exécution sur le réplica en lecture qui tente de récupérer les données de l'objet déposé, avec un temps d'exécution supérieur à la valeur de max_standby_streaming_delay. L'instruction SELECT est alors annulée afin que l'instruction DROP puisse être appliquée.

La session 1 (réplica en lecture) exécute une instruction SELECT sur example_table :

postgres=> SELECT * from example_table;

La session 2 (principale) exécute une instruction DROP sur example_table :

postgres=> DROP TABLE example_table;

Vous obtenez l'erreur suivante :

postgres@postgres:[27544]:ERROR:  canceling statement due to conflict with recovery
postgres@postgres:[27544]:DETAIL:  User was holding a relation lock for too long.
postgres@postgres:[27544]:STATEMENT:  select * from example_table;

En outre, un conflit de requête peut se produire lorsqu'une transaction sur le réplica en lecture lit des tuples qui sont marqués pour suppression sur l'instance principale. La suppression des tuples suivie du vacuuming (nettoyage) sur l'instance principale provoque un conflit avec la requête SELECT qui est toujours en cours d'exécution sur le réplica. Dans ce cas, la requête SELECT sur le réplica se termine avec le message d'erreur suivant :

ERROR:  canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.

Résolution

Lorsqu'un réplica en lecture rencontre un conflit et que l'erreur « annulation de l'instruction due à un conflit avec la récupération » s'affiche dans le journal des erreurs, vous pouvez définir certains paramètres personnalisés en fonction du message d'erreur de manière à réduire l'impact du conflit. Notez que les paramètres personnalisés doivent être définis sur le réplica en lecture.

Vous obtenez l'erreur « annulation de la déclaration en raison d'un conflit avec la récupération » avec « DETAIL: User was holding a relation lock for too long » (DÉTAIL : l'utilisateur a maintenu un verrou de relation pendant trop longtemps)

max_standby_streaming_delay/max_standby_archive_delay : vous pouvez utiliser ces paramètres pour laisser plus de temps avant d'annuler les instructions standby qui entrent en conflit avec les entrées WAL sur le point d'être appliquées. Ces valeurs représentent la durée totale autorisée pour appliquer les données WAL une fois que les données ont été reçues depuis l'instance principale. Ces paramètres sont spécifiés en fonction d'où les données WAL sont lues. Si les données WAL sont lues à partir de la réplication en continu, utilisez le paramètre max_standby_streaming_delay. Si les données WAL sont lues à partir de l'emplacement d'archivage dans Amazon Simple Storage Service (Amazon S3), utilisez le paramètre max_standby_archive_delay.

Lorsque vous définissez ces paramètres, tenez compte des points suivants :

• Si vous définissez les valeurs de ces paramètres à -1, l'instance de réplica est autorisée à attendre indéfiniment que les requêtes en conflit se terminent, ce qui augmente le délai de réplication.
• Si vous définissez les valeurs de ces paramètres à 0, les requêtes en conflit sont annulées et les entrées WAL sont appliquées à l'instance de réplica.
• La valeur par défaut de ces paramètres est définie sur 30 secondes.
• Si vous ne spécifiez pas l'unité lors de la définition de ces paramètres, la milliseconde est considérée comme l'unité.

Réglez les valeurs de ces paramètres pour équilibrer l'annulation des requêtes ou le retard de réplication en fonction de votre cas d'utilisation.

Remarque : si vous augmentez max_standby_archive_delay pour éviter d'annuler les requêtes qui entrent en conflit avec la lecture des entrées d'archive WAL, envisagez d'augmenter également max_standby_streaming_delay pour éviter les annulations liées à un conflit avec des entrées WAL en streaming.

Vous obtenez l'erreur « annulation de l'instruction en raison d'un conflit avec la récupération » avec « DETAIL: User query might have needed to see row versions that must be removed » (DÉTAIL : la requête utilisateur peut avoir besoin de voir les versions de ligne qui doivent être supprimées)

hot_standby_feedback : si vous activez ce paramètre, des messages de retour d'information sont envoyés à l'instance principale à partir du réplica en lecture avec les informations de la transaction active la plus ancienne. Par conséquent, l'instance principale ne supprime pas les enregistrements dont la transaction pourrait avoir besoin.

Lorsque vous activez ce paramètre sur le réplica en lecture, les requêtes de longue durée sur le réplica en lecture peuvent entraîner un gonflement de la table sur l'instance principale. En effet, les opérations de vacuum ne suppriment pas les tuples morts qui peuvent être requis par les requêtes exécutées sur le réplica en lecture. Ce paramètre est désactivé par défaut. Par conséquent, soyez prudent lors de l'activation de ce paramètre.

Vous pouvez également inspecter la vue pg_stat_database_conflicts sur le réplica en lecture pour obtenir des statistiques sur les instructions annulées en raison de conflits avec la récupération sur le réplica en lecture.

---