Re: Standby pg_dump Conflict with Recovery

> On Oct 15, 2015, at 6:16 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general <http://www.postgresql.org/mailpref/pgsql-general>