ERROR de PostgreSQL: statement de cancelación debido a un conflicto con la recuperación

Recibo el siguiente error cuando ejecuto una consulta en un DB de PostgreSQL en modo de espera. La consulta que causa el error funciona bien durante 1 mes, pero cuando realiza una consulta durante más de 1 mes, se produce un error.

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

¿Alguna sugerencia de cómo resolverlo? Gracias

Ejecutar consultas en el servidor de espera activa es algo complicado: puede fallar, ya que durante la consulta algunas filas necesarias pueden actualizarse o eliminarse en primario. Como primario no sabe que una consulta se inicia en secundaria, cree que puede limpiar (vacío) versiones antiguas de sus filas. Luego secundaria tiene que volver a realizar esta limpieza y tiene que cancelar por la fuerza todas las consultas que pueden usar estas filas.

Las consultas más largas se cancelarán más a menudo.

Puede solucionar esto iniciando una transacción de lectura repetible en primario que hace una consulta ficticia y luego permanece inactiva mientras una consulta real se ejecuta en secundaria. Su presencia evitará pasar la aspiradora de las versiones antiguas de la fila en la primaria.

Se explica más sobre este tema y otras soluciones en la sección Hot Standby: Manejo de conflictos de consultas en la documentación.

No es necesario iniciar transacciones inactivas en el maestro. En postgresql-9.1 la forma más directa de resolver este problema es estableciendo

 hot_standby_feedback = on 

Esto hará que el maestro esté al tanto de las consultas de larga ejecución. De los documentos :

La primera opción es establecer el parámetro hot_standby_feedback, que evita que VACUUM elimine filas recientemente muertas y, por lo tanto, no se producen conflictos de limpieza.

¿Por qué no es este el valor predeterminado? Este parámetro se agregó después de la implementación inicial y es la única forma en que un modo en espera puede afectar a un maestro.

Como se indica aquí sobre hot_standby_feedback = on :

Bueno, la desventaja de esto es que el modo de espera puede abotargar al maestro, lo que puede ser sorprendente para algunas personas, también

Y aquí :

¿Con qué configuración de max_standby_streaming_delay? Prefiero predeterminar eso a -1 que el valor predeterminado hot_standby_feedback en. De esa manera, lo que haces en el modo de espera solo afecta al modo de espera

Así que agregué

 max_standby_streaming_delay = -1 

Y no hay más error pg_dump para nosotros, ni master bloat 🙂

No es necesario tocar hot_standby_feedback . Como otros han mencionado, configurarlo puede hincharse maestro. Imagina abrir transacción en un esclavo y no cerrarlo.

En su lugar, establezca max_standby_archive_delay y max_standby_streaming_delay a algún valor razonable:

 # /etc/postgresql/10/main/postgresql.conf on a slave max_standby_archive_delay = 900s max_standby_streaming_delay = 900s 

De esta forma, las consultas sobre esclavos con una duración inferior a 900 segundos no se cancelarán. Si su carga de trabajo requiere consultas más largas, simplemente configure estas opciones a un valor más alto.

Los datos de la tabla en el servidor esclavo en espera activo se modifican mientras se ejecuta una consulta de larga ejecución. Una solución (PostgreSQL 9.1+) para asegurarse de que los datos de la tabla no se modifiquen es suspender la replicación y el currículum después de la consulta:

 select pg_xlog_replay_pause(); -- suspend select * from foo; -- your query select pg_xlog_replay_resume(); --resume