Обсуждение: terminating connection due to conflict with recovery
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
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
www.seiler.us
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