Thread

1. Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ?

   hubert depesz lubaczewski <depesz@depesz.com> — 2026-06-18T11:57:04Z

   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
   
   
   
   
   

2. Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ?

   Thom Brown <thom@linux.com> — 2026-06-18T13:07:36Z

   On Thu, 18 Jun 2026 at 12:57, hubert depesz lubaczewski
   <depesz@depesz.com> 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
   
   
   
   

3. Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ?

   hubert depesz lubaczewski <depesz@depesz.com> — 2026-06-18T13:33:56Z

   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
   
   
   
   
   

4. Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ?

   Laurenz Albe <laurenz.albe@cybertec.at> — 2026-06-19T07:06:12Z

   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
   
   
   
   

5. Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ?

   hubert depesz lubaczewski <depesz@depesz.com> — 2026-06-19T10:07:21Z

   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