From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Игорь Выскорко <vyskorko.igor@yandex.ru>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Hot standby with hot_standny_feedback enabled: cancelling statement issues
Date: Wed, 25 Nov 2020 15:46:37 +0100
Message-ID: <09ff8c81a3ecb764bbfec33baf432295a884c19e.camel@cybertec.at> (raw)
In-Reply-To: <6184231606295846@mail.yandex.ru>
References: <6184231606295846@mail.yandex.ru>
On Wed, 2020-11-25 at 16:46 +0700, Игорь Выскорко wrote:
> 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 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
>
> 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...
>
> 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?
Because is was not a conflict with a tuple deleted by VACUUM ("snapshot conflict"),
but with a lock.
ACCESS EXCLUSIVE locks are replicated, because they are necessary for safe
replication of commands like TRUNCATE or ALTER TABLE.
If you ran none of these commands on the primary, you probably have fallen
prey to "vacuum truncation", where a brief ACCESS EXCLUSIVE lock is taken on
a table so that VACUUM can truncate some empty pages from a table.
These locks don't disrupt operation on the primary, but they can conflict
with queries on the standby.
> 2. What is proper way to use replica for tasks as mine?
The best thing is to have two standbys: one for high availability that does
not lag (hot_standby = off), and one for pg_dump and queries.
You may want to read my blog on the topic:
https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/
Yours,
Laurenz Albe
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: laurenz.albe@cybertec.at, vyskorko.igor@yandex.ru
Subject: Re: Hot standby with hot_standny_feedback enabled: cancelling statement issues
In-Reply-To: <09ff8c81a3ecb764bbfec33baf432295a884c19e.camel@cybertec.at>
* 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