WAIT FOR WAIT FOR 7 SQL - Language Statements WAIT FOR wait for target LSN to be replayed, optionally with a timeout WAIT FOR LSN 'lsn' [ WITH ( option [, ...] ) ] where option can be: TIMEOUT 'timeout' NO_THROW Waits until recovery replays lsn. If no timeout is specified or it is set to zero, this command waits indefinitely for the lsn. On timeout, or if the server is promoted before lsn is reached, an error is emitted, unless NO_THROW is specified in the WITH clause. If NO_THROW is specified, then the command doesn't throw errors. The possible return values are success, timeout, and not in recovery. lsn Specifies the target LSN to wait for. WITH ( option [, ...] ) This clause specifies optional parameters for the wait operation. The following parameters are supported: TIMEOUT 'timeout' When specified and timeout is greater than zero, the command waits until lsn is reached or the specified timeout has elapsed. The timeout might be given as integer number of milliseconds. Also it might be given as string literal with integer number of milliseconds or a number with unit (see ). NO_THROW Specify to not throw an error in the case of timeout or running on the primary. In this case the result status can be get from the return value. success This return value denotes that we have successfully reached the target lsn. timeout This return value denotes that the timeout happened before reaching the target lsn. not in recovery This return value denotes that the database server is not in a recovery state. This might mean either the database server was not in recovery at the moment of receiving the command, or it was promoted before reaching the target lsn. WAIT FOR command waits till lsn to be replayed on standby. That is, after this command execution, the value returned by pg_last_wal_replay_lsn should be greater or equal to the lsn value. This is useful to achieve read-your-writes-consistency, while using async replica for reads and primary for writes. In that case, the lsn of the last modification should be stored on the client application side or the connection pooler side. WAIT FOR command should be called on standby. If a user runs WAIT FOR on primary, it will error out unless NO_THROW is specified in the WITH clause. However, if WAIT FOR is called on primary promoted from standby and lsn was already replayed, then the WAIT FOR command just exits immediately. You can use WAIT FOR command to wait for the pg_lsn value. For example, an application could update the movie table and get the lsn after changes just made. This example uses pg_current_wal_insert_lsn on primary server to get the lsn given that synchronous_commit could be set to off. postgres=# UPDATE movie SET genre = 'Dramatic' WHERE genre = 'Drama'; UPDATE 100 postgres=# SELECT pg_current_wal_insert_lsn(); pg_current_wal_insert_lsn -------------------- 0/306EE20 (1 row) Then an application could run WAIT FOR with the lsn obtained from primary. After that the changes made on primary should be guaranteed to be visible on replica. postgres=# WAIT FOR LSN '0/306EE20'; status -------- success (1 row) postgres=# SELECT * FROM movie WHERE genre = 'Drama'; genre ------- (0 rows) If the target LSN is not reached before the timeout, the error is thrown. postgres=# WAIT FOR LSN '0/306EE20' WITH (TIMEOUT '0.1s'); ERROR: timed out while waiting for target LSN 0/306EE20 to be replayed; current replay LSN 0/306EA60 The same example uses WAIT FOR with NO_THROW option. postgres=# WAIT FOR LSN '0/306EE20' WITH (TIMEOUT '100ms', NO_THROW); status -------- timeout (1 row)