Обсуждение: 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.klaver@aklaver.com

On 10/15/2015 03:30 PM, Louis Battuello wrote:
>
>> On Oct 15, 2015, at 6:16 PM, Adrian Klaver <adrian.klaver@aklaver.com
>> <mailto:adrian.klaver@aklaver.com>> wrote:
>>

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

Hmm. From what I see it looks okay.

Have looked in the logs of the master to see what is going on around the
time the query is cancelled?

Also in the standby logs before and after the ERROR?

--
Adrian Klaver
adrian.klaver@aklaver.com

On 10/16/2015 07:50 AM, Louis Battuello wrote:
>
>
>> Also in the standby logs before and after the ERROR?
>
> The primary log was clean. The standby contained the same error as the
> pg_dump output log:
>
> < 2015-10-15 01:10:50 EDT [42613] : [1-1]
> user=postgres,db=<db>,remote=::1(55426) > ERROR:  canceling statement
> due to conflict with recovery
> < 2015-10-15 01:10:50 EDT [42613] : [2-1]
> user=postgres,db=<db>,remote=::1(55426) > DETAIL:  User query might have
> needed to see row versions that must be removed.
> < 2015-10-15 01:10:50 EDT [42613] : [3-1]
> user=postgres,db=<db>,remote=::1(55426) > STATEMENT:  COPY <table> (...)
> TO stdout;
>
> I ran the pg_dump process again this morning, ensuring that the standby
> parameters were set, and it completed successfully with the
> hot_standby_feedback enabled.
>
> postgres=# select name, setting, unit from pg_settings where category =
> 'Replication / Standby Servers';
>               name             | setting | unit
> ------------------------------+---------+------
>   hot_standby                  | on      |
>   hot_standby_feedback         | on      |
>   max_standby_archive_delay    | 30000   | ms
>   max_standby_streaming_delay  | 30000   | ms
>   wal_receiver_status_interval | 10      | s
>   wal_receiver_timeout         | 60000   | ms
> (6 rows)
>
> postgres=# \q
>
>
> I’m going to file this one under: ”DBA (/me/) failed to ensure the
> postgresql.conf was saved with updated parameters.”

Been there, done that, understand.

>
> Thanks for your help.
>



--
Adrian Klaver
adrian.klaver@aklaver.com