If you see some of the following sample error messages when backing up PostgreSQL, then it is very likely that you need to tweak your database settings to allow replica/slave backups to complete.

pg_dump: error: Dumping the contents of table "some_table" failed: PQgetResult() failed. 

pg_dump: error: Error message from server: ERROR: canceling statement due to conflict with recovery DETAIL: User was holding a relation lock for too long.

pg_dump: error: The command was: COPY public.some_table (id, created_at, ...) TO stdout;

Explanation of the issue

If you have a replica/slave configuration and you use that replica/slave for your backups, then if a query takes too long to be read, there is a possibility that the returned result no longer exists or have been changed on the master node.

Proposed solution

Edit postgresql.conf on the replica node and set the following two values:

max_standby_archive_delay = 900s 
max_standby_streaming_delay = 900s

This way queries on the slave/replica with a duration less than 900 seconds won't be cancelled quickly and give it more time to complete.

  • max_standby_streaming_delay: sets the maximum delay before canceling queries when a hot standby server is processing streamed WAL data

  • max_standby_archive_delay: sets the maximum delay before canceling queries when a hot standby server is processing archived WAL data



Did this answer your question?