pg_dump: Error message from server: ERROR: canceling statement due to conflict with recovery

I ran to this error the other day when I tried to export a dump file from a slave Postgres database.

cpg_dump: Dumping the contents of table "[table_name]" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.
pg_dump: The command was: COPY public.[table_name] ([comma-separated column names]) TO stdout;

I turns out that we can't execute long query on Hot Standby mode server. Right now, there's no perfect solution to this problem but there are some couples of workarounds.

• Make a dump from master.
• Set max_standby_archive_delay and max_standby_streaming_delay to be bigger that amount of time it needs to export a dump. This will make slave catching up master version slower. It's not likely to work for a big database.
• Pause sync, make a dump and the resume sync. You can pause sync with function pg_xlog_replay_pause() and resume with function pg_xlog_replay_resume(). See more details on this 2 functions from 9.24. System Administration Functions and New Replication and Recovery Features in PostgreSQL 9.1. Note that we should not pause it too long (> 24 hours), otherwise I might not be able to resume.

Mailing list related to this issue: Hot Standby - ERROR: canceling statement due to conflict with recovery