From: Игорь Выскорко <vyskorko.igor@yandex.ru>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Hot standby with hot_standny_feedback enabled: cancelling statement issues
Date: Wed, 25 Nov 2020 16:46:29 +0700
Message-ID: <6184231606295846@mail.yandex.ru> (raw)
<div>Hi!<br /> </div><div>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.<br />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.</div><div>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.<br />Just to clarify about "simple backup purposes": it means run pg_dump in daily basis. Our database is about 250Gb<br /> <div>2 test run of pg_dump failed with:<div>pg_dump: Dumping the contents of table "table" failed: PQgetResult() failed.</div><div>pg_dump: Error message from server: ERROR: canceling statement due to conflict with recovery</div><div>DETAIL: User was holding a relation lock for too long.</div><div>pg_dump: The command was: COPY...<br /> <div>postgres=# select * from pg_stat_database_conflicts ;</div><div> datid | datname | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock </div><div>-----------+---------------+------------------+------------+----------------+-----------------+----------------</div><div> 12445 | dbname | 0 | 2 | 0 | 0 | 0</div><div><br />Not sure about what kind of lock was holding too long...<br /><br />So, my questions are:<br />1. Why "hot_standby_feedback = on" is not helping?<br />2. What is proper way to use replica for tasks as mine?<br /><br />Thanks</div></div></div><br /><br />Our current setup:<div><div>postgres=# select version();</div><div> version </div><div>---------------------------------------------------------------------------------------------------------------------------------------</div><div> 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</div><div>(1 row)</div><div> </div><div>postgres=# select name, setting, unit from pg_settings where category = 'Replication / Standby Servers'; </div><div> name | setting | unit </div><div>------------------------------+---------+------</div><div> hot_standby | on | </div><div> hot_standby_feedback | on | </div><div> max_standby_archive_delay | 930000 | ms</div><div> max_standby_streaming_delay | 900000 | ms</div><div> wal_receiver_status_interval | 10 | s</div><div> wal_receiver_timeout | 60000 | ms</div><div> wal_retrieve_retry_interval | 5000 | ms</div><div>(7 rows)</div></div></div><div> </div>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: vyskorko.igor@yandex.ru
Subject: Re: Hot standby with hot_standny_feedback enabled: cancelling statement issues
In-Reply-To: <6184231606295846@mail.yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox