<div> </div><div> </div><div>25.11.2020, 21:46, "Laurenz Albe" <laurenz(dot)albe(at)cybertec(dot)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><br /><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>