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.
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.”
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
