When you go for replication and you don’t use synchronous replication there is always a window when data written on the primary is not yet available in the replica. This is known as “replication lag” and can be monitored using the pg_stat_replication catalog view. A recent commit to PostgreSQL 19 implements a way to wait for data to be visible on the replica without switching to synchronous replication, and this is what the “WAIT FOR” command is for.

Before we can see how that works we need a replica, because when you try to execute this command on a primary you’ll get this:

postgres=# select version();
                                        version                                        
---------------------------------------------------------------------------------------
 PostgreSQL 19devel on x86_64-linux, compiled by gcc-15.1.1, 64-bit
(1 row)

postgres=# WAIT FOR LSN '0/306EE20';
ERROR:  recovery is not in progress
HINT:  Waiting for the replay LSN can only be executed during recovery.
postgres=#

So, let’s create a replica and start it up:

postgres@:/home/postgres/ [pgdev] mkdir /var/tmp/dummy
postgres@:/home/postgres/ [pgdev] pg_basebackup --pgdata=/var/tmp/dummy --write-recovery-conf --checkpoint=fast
postgres@:/home/postgres/ [pgdev] echo "port=8888" >> /var/tmp/dummy/postgresql.auto.conf 
postgres@:/home/postgres/ [pgdev] chmod 700 /var/tmp/dummy
postgres@:/home/postgres/ [pgdev] pg_ctl --pgdata=/var/tmp/dummy start
postgres@:/home/postgres/ [pgdev] psql -p 8888 -c "select pg_is_in_recovery()"
 pg_is_in_recovery 
-------------------
 t
(1 row)

As nothing is happening on the primary right now, data on the primary and the replica is exactly the same:

postgres=# select usename,sent_lsn,write_lsn,flush_lsn,replay_lsn from pg_stat_replication;
 usename  |  sent_lsn  | write_lsn  | flush_lsn  | replay_lsn 
----------+------------+------------+------------+------------
 postgres | 0/03000060 | 0/03000060 | 0/03000060 | 0/03000060

To see how “WAIT FOR” behaves we need to a little cheating and pause WAL replaying on the replica (we could also cut the the network between the primary and the replica):

postgres@:/home/postgres/ [pgdev] psql -p 8888 -c "select * from pg_wal_replay_pause();" 
 pg_wal_replay_pause 
---------------------
 
(1 row)
postgres@:/home/postgres/ [pgdev] psql -p 8888 -c "select * from pg_is_wal_replay_paused();" 
 pg_is_wal_replay_paused 
-------------------------
 t
(1 row)

On the primary, create a table and get the current LSN:

postgres@:/home/postgres/ [pgdev] psql -c "create table t(a int)"
CREATE TABLE
postgres@:/home/postgres/ [pgdev] psql -c "insert into t values(1)"
INSERT 0 1
postgres@:/home/postgres/ [pgdev] psql -c "select pg_current_wal_insert_lsn();"
 pg_current_wal_insert_lsn 
---------------------------
 0/03018CA8
(1 row)

As WAL replay on the replica is paused, the “WAIT FOR” command will now block:

postgres@:/home/postgres/ [pgdev] psql -p 8888 -c "WAIT FOR LSN '0/03018CA8'"

This is the intended behavior as we want to make sure that we can see all the data up to this LSN. Once we resume WAL replay on the replica the “WAIT FOR” command will return success as all the data reached the replica:

postgres@:/home/postgres/ [DEV] psql -p 8888 -c "select * from pg_wal_replay_resume();"
 pg_wal_replay_resume 
----------------------
 
(1 row)

… the other session will unblock:

postgres@:/home/postgres/ [pgdev] psql -p 8888 -c "WAIT FOR LSN '0/03018CA8'" 
 status  
---------
 success
(1 row)

So, starting with PostgreSQL 19 next year, there is a way for applications to make sure that a replica reached all the data up to a specific LSN by blocking until the data is there.

Post Views: 8
Thumbnail [60x60]
by
Daniel Westermann