Thread

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

   PG Bug reporting form <noreply@postgresql.org> — 2025-09-11T07:09:36Z

   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)
   
   

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

   Dilip Kumar <dilipbalaut@gmail.com> — 2025-09-11T14:47:24Z

   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