Обсуждение: terminating connection due to conflict with recovery

Поиск
Список
Период
Сортировка

terminating connection due to conflict with recovery

От
Don Seiler
Дата:
Good morning,

I have a PG 12.4 read replica that we're using to offload short read-only queries from the primary. However this morning (and random other days/times, it seems), I see batches of these messages in the replica PG log:

FATAL:  terminating connection due to conflict with recovery

I have hot_standby_feedback enabled already, but looking further at this message, it isn't cancelling the transaction but rather terminating the connection entirely. This suggests perhaps that a session is sitting "idle in transaction" for 30 seconds (our max_standby_streaming_delay is at the default 30s)? Or that a group of sessions are "idle in transaction" and overlapping enough that recovery is blocked for 30 seconds?

When I spot check the sessions, none are ever "idle in transaction" for more than 3 seconds (most less than 1 second), and there doesn't seem to be a lot of them. They sit "idle in transaction" due to the app querying the DB and processing data with the transaction open. The devs are looking to change that but for now I want to be sure we understand what is happening on the database end.

When the sessions are terminated, I don't get any report of what query was executing at the time, which further leads me to suspect these are idle-in-transaction sessions.

I'd like to know what you folks think and/or where else I could/should look to find a root cause. Like I said this is somewhat sporadic. For example, it happend for a few hours one morning last week, then nothing until this morning from ~11:00-15:00 GMT. When it is happening, it's maybe every few minutes we'll see a couple and then we'll see 5-6 in the same second.

--
Don Seiler
www.seiler.us

Re: terminating connection due to conflict with recovery

От
Laurenz Albe
Дата:
On Thu, 2020-10-08 at 10:17 -0500, Don Seiler wrote:
> I have a PG 12.4 read replica that we're using to offload short read-only queries from the primary. However this
morning(and random other days/times, it seems), I see batches of these messages in
 
> the replica PG log:
> 
> FATAL:  terminating connection due to conflict with recovery
> 
> I have hot_standby_feedback enabled already, but looking further at this message, it isn't cancelling the transaction
butrather terminating the connection entirely. This suggests perhaps that a
 
> session is sitting "idle in transaction" for 30 seconds (our max_standby_streaming_delay is at the default 30s)? Or
thata group of sessions are "idle in transaction" and overlapping enough that
 
> recovery is blocked for 30 seconds?

What do you find in "pg_stat_database_conflicts"?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com