> On Oct 15, 2015, at 6:16 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 10/15/2015 03:03 PM, Louis Battuello wrote: >> Hello All, >> >> 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. > >> >> >> postgres=# select * from pg_replication_slots ; >> slot_name | plugin | slot_type | datoid | database | active >> | xmin | catalog_xmin | restart_lsn >> ---------------+--------+-----------+--------+----------+--------+---------+--------------+------------- >> repmgr_slot_2 | | physical | | | t | >> | | A/C6502880 >> repmgr_slot_3 | | physical | | | t | >> 1356283 | | A/C6502880 >> (2 rows) >> >> Is there some other configuration setting I’m forgetting? >> >> Thanks, >> Louis >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org > <mailto:pgsql-general@postgresql.org>) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > <http://www.postgresql.org/mailpref/pgsql-general>
