Обсуждение: BUG #19047: pg_last_wal_replay_lsn() returns non-NULL after pg_promote()

The following bug has been logged on the website:

Bug reference:      19047
Logged by:          Mahadevan Ramachandran
Email address:      mahadevan@rapidloop.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.

Best,
-Mahadevan
(pgDash - https://pgdash.io)

On Thu, Sep 11, 2025 at 8:05 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      19047
> Logged by:          Mahadevan Ramachandran
> Email address:      mahadevan@rapidloop.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