Message-ID: <09ff8c81a3ecb764bbfec33baf432295a884c19e.camel@cybertec.at>
Subject: Re: Hot standby with hot_standny_feedback enabled: cancelling
 statement issues
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: =?UTF-8?Q?=D0=98=D0=B3=D0=BE=D1=80=D1=8C_?=
	=?UTF-8?Q?=D0=92=D1=8B=D1=81=D0=BA=D0=BE=D1=80=D0=BA=D0=BE?=
	 <vyskorko.igor@yandex.ru>, "pgsql-admin@postgresql.org"
	 <pgsql-admin@postgresql.org>
Date: Wed, 25 Nov 2020 15:46:37 +0100
In-Reply-To: <6184231606295846@mail.yandex.ru>
References: <6184231606295846@mail.yandex.ru>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-1.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Precedence: bulk

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