public inbox for [email protected]help / color / mirror / Atom feed
Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? 5+ messages / 3 participants [nested] [flat]
* Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? @ 2026-06-18 11:57 hubert depesz lubaczewski <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: hubert depesz lubaczewski @ 2026-06-18 11:57 UTC (permalink / raw) To: pgsql-general Hi, we have set of clusters that run on pg14 (yes, I know, we are in process of upgrading but it's complicated). Recently we noticed that some of the DR nodes are lagging because they get stuck on some wal files, and starup process, in pg_stat_activity shows "IPC:RecoveryConflictSnapshot" as wait event. The thing is that there are no othger connections doing anything to the db. There are some idle monitor ones, running things like count(*) from pg_stat_activity, every now and then, but we're talking about pg being stuck on single wal up to an hour or so. Stuck as in: ps shows: postgres: 14/main: startup recovering 00000003000065EA000000C6 waiting What could it be, how to fix it? Best regards, depesz ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? @ 2026-06-18 13:07 Thom Brown <[email protected]> parent: hubert depesz lubaczewski <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Thom Brown @ 2026-06-18 13:07 UTC (permalink / raw) To: [email protected]; +Cc: pgsql-general On Thu, 18 Jun 2026 at 12:57, hubert depesz lubaczewski <[email protected]> wrote: > > Hi, > we have set of clusters that run on pg14 (yes, I know, we are in process > of upgrading but it's complicated). > > Recently we noticed that some of the DR nodes are lagging because they > get stuck on some wal files, and starup process, in pg_stat_activity > shows "IPC:RecoveryConflictSnapshot" as wait event. > > The thing is that there are no othger connections doing anything to the > db. There are some idle monitor ones, running things like count(*) from > pg_stat_activity, every now and then, but we're talking about pg being > stuck on single wal up to an hour or so. > > Stuck as in: > ps shows: > postgres: 14/main: startup recovering 00000003000065EA000000C6 waiting > > What could it be, how to fix it? Have you got anything in the logs that say "conflict with recovery"? Also, I know you are just running count(*), but is there a transaction being left open? SELECT pid, state, xact_start, query FROM pg_stat_activity ORDER BY xact_start NULLS LAST; Any 'idle in transaction' for too long? What is max_standby_streaming_delay and hot_standby_feedback set to? Thom ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? @ 2026-06-18 13:33 hubert depesz lubaczewski <[email protected]> parent: Thom Brown <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: hubert depesz lubaczewski @ 2026-06-18 13:33 UTC (permalink / raw) To: Thom Brown <[email protected]>; +Cc: pgsql-general On Thu, Jun 18, 2026 at 02:07:36PM +0100, Thom Brown wrote: > Have you got anything in the logs that say "conflict with recovery"? > Also, I know you are just running count(*), but is there a transaction > being left open? > SELECT pid, state, xact_start, query > FROM pg_stat_activity > ORDER BY xact_start NULLS LAST; > Any 'idle in transaction' for too long? > What is max_standby_streaming_delay and hot_standby_feedback set to? No transactions were happening then. These are our "Disaster Recovery" instances, and they are generally unused. max_standby_streaming_delay is 10 minutes hot_standby_feedback is on. Please note that these are *NOT* doing streaming replication! Best regards, depesz ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? @ 2026-06-19 07:06 Laurenz Albe <[email protected]> parent: hubert depesz lubaczewski <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Laurenz Albe @ 2026-06-19 07:06 UTC (permalink / raw) To: [email protected]; Thom Brown <[email protected]>; +Cc: pgsql-general On Thu, 2026-06-18 at 15:33 +0200, hubert depesz lubaczewski wrote: > On Thu, Jun 18, 2026 at 02:07:36PM +0100, Thom Brown wrote: > > Have you got anything in the logs that say "conflict with recovery"? > > Also, I know you are just running count(*), but is there a transaction > > being left open? > > SELECT pid, state, xact_start, query > > FROM pg_stat_activity > > ORDER BY xact_start NULLS LAST; > > Any 'idle in transaction' for too long? > > What is max_standby_streaming_delay and hot_standby_feedback set to? > > No transactions were happening then. These are our "Disaster Recovery" > instances, and they are generally unused. > > max_standby_streaming_delay is 10 minutes > hot_standby_feedback is on. > > Please note that these are *NOT* doing streaming replication! Could it be bug #19490 [1]? That would be fixed in the next minor releases. Yours, Laurenz Albe [1]: https://postgr.es/m/flat/19490-9c59c6a583513b99%40postgresql.org ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? @ 2026-06-19 10:07 hubert depesz lubaczewski <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: hubert depesz lubaczewski @ 2026-06-19 10:07 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: Thom Brown <[email protected]>; pgsql-general On Fri, Jun 19, 2026 at 09:06:12AM +0200, Laurenz Albe wrote: > On Thu, 2026-06-18 at 15:33 +0200, hubert depesz lubaczewski wrote: > > On Thu, Jun 18, 2026 at 02:07:36PM +0100, Thom Brown wrote: > > > Have you got anything in the logs that say "conflict with recovery"? > > > Also, I know you are just running count(*), but is there a transaction > > > being left open? > > > SELECT pid, state, xact_start, query > > > FROM pg_stat_activity > > > ORDER BY xact_start NULLS LAST; > > > Any 'idle in transaction' for too long? > > > What is max_standby_streaming_delay and hot_standby_feedback set to? > > > > No transactions were happening then. These are our "Disaster Recovery" > > instances, and they are generally unused. > > > > max_standby_streaming_delay is 10 minutes > > hot_standby_feedback is on. > > > > Please note that these are *NOT* doing streaming replication! > > Could it be bug #19490 [1]? > That would be fixed in the next minor releases. Maybe it's related, but there are two issues: 1. it's not streaming replication 2. waitevent/type doesn't match. in my case it's IPC/RecoveryConflictSnapshot Best regards, depesz ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-06-19 10:07 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-06-18 11:57 Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? hubert depesz lubaczewski <[email protected]> 2026-06-18 13:07 ` Thom Brown <[email protected]> 2026-06-18 13:33 ` hubert depesz lubaczewski <[email protected]> 2026-06-19 07:06 ` Laurenz Albe <[email protected]> 2026-06-19 10:07 ` hubert depesz lubaczewski <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox