MIME-Version: 1.0
References: <ajPdELE6JOtO7bk8@depesz.com>
In-Reply-To: <ajPdELE6JOtO7bk8@depesz.com>
From: Thom Brown <thom@linux.com>
Date: Thu, 18 Jun 2026 14:07:36 +0100
Message-ID: 
 <CAA-aLv4UZ9bOtEDbdeCa1KMmdgCxzaT-YQnEN=ZZ7LUQTp+GJw@mail.gmail.com>
Subject: Re: Why is WAL-file based replica stuck for long time (hour?) in
 IPC:RecoveryConflictSnapshot ?
To: depesz@depesz.com
Cc: pgsql-general mailing list <pgsql-general@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAA-aLv4UZ9bOtEDbdeCa1KMmdgCxzaT-YQnEN%3DZZ7LUQTp%2BGJw%40mail.gmail.com>
Precedence: bulk

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