terminating connection due to conflict with recovery

From: Don Seiler <don(at)seiler(dot)us>
To: Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: terminating connection due to conflict with recovery
Date: 2020-10-08 15:17:50
Message-ID: CAHJZqBBgGZdF2GuE5a6KUW4daxgNgOAwP2N=x+ZYFmzXpAJ3VQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Domen Šetar 2020-10-08 17:32:05 Vacuum not working
Previous Message lennam 2020-10-08 12:31:32 Re: How to return multiple rows by stored procedure in postgresql