From: Игорь Выскорко <vyskorko.igor@yandex.ru>
To: Laurenz Albe <laurenz.albe@cybertec.at>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Hot standby with hot_standny_feedback enabled: cancelling statement issues
Date: Thu, 26 Nov 2020 13:54:19 +0700
Message-ID: <3955671606369583@mail.yandex.ru> (raw)
In-Reply-To: <09ff8c81a3ecb764bbfec33baf432295a884c19e.camel@cybertec.at>
References: <6184231606295846@mail.yandex.ru>
<09ff8c81a3ecb764bbfec33baf432295a884c19e.camel@cybertec.at>
<div> </div><div> </div><div>25.11.2020, 21:46, "Laurenz Albe" <laurenz.albe@cybertec.at>:</div><blockquote><p>On Wed, 2020-11-25 at 16:46 +0700, Игорь Выскорко wrote:</p><blockquote> 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<br /> almost fresh copy of data.<br /> 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 /> <br /> 2 test run of pg_dump failed with:<br /> pg_dump: Dumping the contents of table "table" failed: PQgetResult() failed.<br /> pg_dump: Error message from server: ERROR: canceling statement due to conflict with recovery<br /> DETAIL: User was holding a relation lock for too long.<br /> pg_dump: The command was: COPY...<br /> <br /> postgres=# select * from pg_stat_database_conflicts ;<br /> datid | datname | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock<br /> -----------+---------------+------------------+------------+----------------+-----------------+----------------<br /> 12445 | dbname | 0 | 2 | 0 | 0 | 0<br /> <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?</blockquote><p><br />Because is was not a conflict with a tuple deleted by VACUUM ("snapshot conflict"),<br />but with a lock.<br /><br />ACCESS EXCLUSIVE locks are replicated, because they are necessary for safe<br />replication of commands like TRUNCATE or ALTER TABLE.<br /><br />If you ran none of these commands on the primary, you probably have fallen<br />prey to "vacuum truncation", where a brief ACCESS EXCLUSIVE lock is taken on<br />a table so that VACUUM can truncate some empty pages from a table.<br /><br />These locks don't disrupt operation on the primary, but they can conflict<br />with queries on the standby.<br /> </p><blockquote> 2. What is proper way to use replica for tasks as mine?</blockquote><p><br />The best thing is to have two standbys: one for high availability that does<br />not lag (hot_standby = off), and one for pg_dump and queries.<br /><br />You may want to read my blog on the topic:<br /><a href="https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/"; rel="noopener noreferrer">https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/</a><b... /><br />Yours,<br />Laurenz Albe</p></blockquote><div><div>Thanks for reply and link to topic. It's make sense now why replication conflicts are raised.</div><div>I found hourly executed task which uses truncate operation...</div><div>So, as you suggested - the best way I see is to set hot_standby_feedback = off and set max_standby_streaming_delay to rather high value. </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, laurenz.albe@cybertec.at
Subject: Re: Hot standby with hot_standny_feedback enabled: cancelling statement issues
In-Reply-To: <3955671606369583@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