Re: Standby pg_dump Conflict with Recovery

> On Oct 16, 2015, at 9:35 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> On 10/15/2015 03:30 PM, Louis Battuello wrote:
>>
>>> On Oct 15, 2015, at 6:16 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>>> <mailto:adrian(dot)klaver(at)aklaver(dot)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?

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

Thanks for your help.

>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>