Re: Standby pg_dump Conflict with Recovery

On 10/15/2015 03:03 PM, Louis Battuello wrote:
> Hell 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?

>
>
> 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