Re: BUG #19047: pg_last_wal_replay_lsn() returns non-NULL after pg_promote()

On Thu, Sep 11, 2025 at 8:05 PM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19047
> Logged by: Mahadevan Ramachandran
> Email address: mahadevan(at)rapidloop(dot)com
> PostgreSQL version: 17.6
> Operating system: Debian 12, amd64
> Description:
>
> After promoting a standby to a primary using pg_promote(), it is expected
> that the server is not a primary and does not / cannot replay WALs, and
> therefore pg_last_wal_replay_lsn() should return NULL. However, it does not.
>
> To reproduce, setup a primary and a standby, and run the following commands
> on the standby:
>
> $ psql -h /tmp -p 8001 postgres
> Null display is "~".
> psql (17.6 (Debian 17.6-1.pgdg12+1))
> Type "help" for help.
>
> postgres=# select pg_last_wal_replay_lsn ();
> pg_last_wal_replay_lsn
> ------------------------
> 0/40434E0
> (1 row)
>
> postgres=# select pg_promote();
> pg_promote
> ------------
> t
> (1 row)
>
> postgres=# select pg_last_wal_replay_lsn ();
> pg_last_wal_replay_lsn
> ------------------------
> 0/40434E0
> (1 row)
>
> postgres=# select pg_is_in_recovery();
> pg_is_in_recovery
> -------------------
> f
> (1 row)
>
> At this point, the server is a primary, but still reports
> pg_last_wal_replay_lsn() as non-NULL.
>
> We're reporting this from a real customer issue on the field.

IMHO this is expected behavior as per doc[1], even if you start the
primary in crash recovery mode it will report the last replayed LSN
during crash recovery.

[1]
pg_last_wal_replay_lsn () → pg_lsn

Returns the last write-ahead log location that has been replayed
during recovery. If recovery is still in progress this will increase
monotonically. If recovery has completed then this will remain static
at the location of the last WAL record applied during recovery. When
the server has been started normally without recovery, the function
returns NULL.

--
Regards,
Dilip Kumar
Google