Hi!
I'm trying to configure hot standby replica for analytics and simple backup purposes. So, I have long queries which constantly cancelled without proper configuring.
I do not want large lag on replica (more than 30 mins) so setting max_standby_streaming_delay to -1 is not my choice. I have to avoid large lag because of analytic queries which must be run on almost fresh copy of data.
I do not want large lag on replica (more than 30 mins) so setting max_standby_streaming_delay to -1 is not my choice. I have to avoid large lag because of analytic queries which must be run on almost fresh copy of data.
I found the panacea (I thought I found actually) in setting hot_standby_feedback = on (I know about possible master bloating) but it also doesn't help.
Just to clarify about "simple backup purposes": it means run pg_dump in daily basis. Our database is about 250Gb
Our current setup:
Just to clarify about "simple backup purposes": it means run pg_dump in daily basis. Our database is about 250Gb
2 test run of pg_dump failed with:
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 was holding a relation lock for too long.
pg_dump: The command was: COPY...
Not sure about what kind of lock was holding too long...
So, my questions are:
1. Why "hot_standby_feedback = on" is not helping?
2. What is proper way to use replica for tasks as mine?
Thanks
postgres=# select * from pg_stat_database_conflicts ;
datid | datname | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock
-----------+---------------+------------------+------------+----------------+-----------------+----------------
12445 | dbname | 0 | 2 | 0 | 0 | 0
Not sure about what kind of lock was holding too long...
So, my questions are:
1. Why "hot_standby_feedback = on" is not helping?
2. What is proper way to use replica for tasks as mine?
Thanks
Our current setup:
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.15 on x86_64-pc-linux-gnu (Ubuntu 9.6.15-1.pgdg18.04+1), compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
(1 row)
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 | 930000 | ms
max_standby_streaming_delay | 900000 | ms
wal_receiver_status_interval | 10 | s
wal_receiver_timeout | 60000 | ms
wal_retrieve_retry_interval | 5000 | ms
(7 rows)
