I’ve got a confusing issue with dumping data from a standby PostgreSQL 9.4.5 database.
At night, on a nearly completely idle server, I run a pg_dump of a database that contains numerous small tables and one 3GB table. The dump consistently fails when reaching the 3GB table with this message:
pg_dump: Dumping the contents of table “<table>" 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 <table> (...) TO stdout;
I have replication slots enabled on the primary (“repmgr_slot_3" for the standby pg_dump source), and I’m using hot_standby_feedback. After getting the failure a couple times, I temporarily set max_standby_archive_delay and max_standby_streaming_delay to -1 to allow infinite delay on the standby, just to see if I could get the dump to complete. I still encountered the above error.
How did you set and temporarily enable the settings
I changed the settings in the postgresql.conf file, restarted the standby server, checked that there wasn't any activity on the primary or the standby, and ran the pg_dump on the standby again - which failed. I watched the xmin value on the primary pg_replication_slots, which held steady until the dump failed.
Then, I changed the delay settings back to the defaults and restarted the standby so I wouldn’t affect the replication during the next business day.