Re: Standby pg_dump Conflict with Recovery

[Louis Battuello <louis.battuello@xxxxxxxxxxx>]

On Oct 16, 2015, at 9:35 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

On 10/15/2015 03:30 PM, Louis Battuello wrote:

On Oct 15, 2015, at 6:16 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> 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.klaver@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general