Artificially Delayed Replication with PostgreSQL

While working on our project titled “PostgreSQL for Dolphins and Sea Lions,” I pored over the PostgreSQL documentation on replication. What struck me was that PostgreSQL does NOT support artificially delayed replication—at least according to this documentation.

After I’d already been thinking about how one might possibly cobble together artificially delayed replication using PostgreSQL’s built-in tools, I decided to give my go-to search engine another try—and lo and behold, on the second attempt, it finally worked: I found what I was looking for! [ 2019: 1 ]

The relevant replication configuration is called: recovery_min_apply_delay [ 2 ]. Unfortunately, this is missing from the documentation on “Replication.” I’ve submitted a feature request to the PostgreSQL community. Let’s see how far I get with that…

Use Cases

But why would you want to delay replication in the first place? So far, I’ve come across various use cases for this:

• This setup is most commonly used to guard against “Oops!” queries. With normal replication, an error on the primary would immediately propagate to the standby servers and destroy the data there as well. With delayed replication, you gain time to analyze the error, potentially retrieve data from the standby server to recover the system, or allow the standby server to catch up to the point just before the Oops-query and then switch over to the standby server. See also: PostgreSQL Point-in-Time Recovery with Oops-Queries.

• Another use case is to selectively provide specific user groups with historical data, allowing them to access current data upon payment (freemium business model)—for example, in stock trading, online betting, video games, sports results, job listings or other offers, or weather data.

• A third use case would be to test how a system behaves under replication delays. With asynchronous replication, delays between the primary and standby can potentially always occur. It is often difficult to artificially generate a load high enough to simulate this behavior. With artificially delayed replication, you can test the application’s behavior in a targeted manner and, if necessary, make the application more robust.

• The final use case would be to see what the database looked like in the past without having to restore a backup. For example, if you configure replication with a one-week delay (in PostgreSQL, a maximum of just under 40 days (2³² seconds) is possible), you can still check the following week to see if the data is still correct after the changes made over the weekend.

Setting Up the Primary Instance

The Primary must also be configured as follows (database restart required!):

#
# postgresql.conf
#
cluster_name     = 'pg18p'
listen_addresses = '*'
archive_mode     = on
archive_command  = 'test ! -f /mnt/backup/wal_archive/%f && cp %p /mnt/backup/wal_archive/%f && sync /mnt/backup/wal_archive/%f'

To ensure that old WALs on the Primary aren’t deleted too early, we use a “replication slot” here. This must first be created:

postgres=# SELECT * FROM pg_create_physical_replication_slot('slot_for_pg18s1');
    slot_name    | lsn 
-----------------+-----
 slot_for_pg18s1 | 

postgres=# SELECT slot_name, slot_type, active, wal_status, failover, synced
  FROM pg_replication_slots
;
    slot_name    | slot_type | active | wal_status | failover | synced 
-----------------+-----------+--------+------------+----------+--------
 slot_for_pg18s1 | physical  | f      |            | f        | f

Of course, a replication user is also required:

postgres=# CREATE ROLE replication WITH LOGIN PASSWORD 'secret' REPLICATION;

who must, of course, be able to access the system remotely:

#
# pg_hba.conf
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replication     10.223.125.0/24         scram-sha-256

and then activate the configuration:

postgres=# SELECT pg_reload_conf();

Setting Up the Standby Instance

Next, we set up the delayed Standby as follows:

$ sudo systemctl stop postgresql
$ PGDATA='/var/lib/postgresql/18/main'
$ rm -rf ${PGDATA}/*
$ pg_basebackup --user=replication --host=10.223.125.65 --format=plain --pgdata=${PGDATA}/
$ touch ${PGDATA}/standby.signal

adjust the PostgreSQL configuration as follows:

#
# postgresql.conf
#
cluster_name = 'pg18s1'
archive_cleanup_command = 'pg_archivecleanup /mnt/backup/wal_archive %r'

recovery_target_timeline = latest   # default
primary_conninfo = 'host=10.223.125.65 port=5432 user=replication options=''-c wal_sender_timeout=5000'''
recovery_min_apply_delay = '5min'

The password can be stored in the ~/.pgpass file:

10.223.125.65:5432:replication:replication:secret

Then we start the Standby instance and finally check whether everything is working properly (wait for recovery_min_apply_delay if necessary):

postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t

postgres=# SELECT CURRENT_TIMESTAMP AS now, pg_last_xact_replay_timestamp() AS last_xact_timestamp
     , age(CURRENT_TIMESTAMP, pg_last_xact_replay_timestamp())
;
             now              |      last_xact_timestamp      |       age       
------------------------------+-------------------------------+-----------------
 2026-07-29 11:46:21.92429+00 | 2026-07-29 11:41:21.919321+00 | 00:00:00.004969

The Oops-Query

Now the Oops-Query occurs on the Primary:

test=# UPDATE test SET data = 'all broken!';
UPDATE 673

After a moment of shock, we determine the time (Note: UTC!):

test=# SELECT current_timestamp;
       current_timestamp       
-------------------------------
 2026-07-31 14:10:30.083692+00

Stopping Replication

As soon as the Oops-Query occurs, we must immediately stop replication on the Standby:

postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t

postgres=# SELECT pg_get_wal_replay_pause_state();
 pg_get_wal_replay_pause_state 
-------------------------------
 not paused

postgres=# SELECT pg_wal_replay_pause();
 pg_wal_replay_pause 
---------------------
 
(1 row)

postgres=# SELECT pg_get_wal_replay_pause_state();
 pg_get_wal_replay_pause_state 
-------------------------------
 paused

postgres=# SELECT pg_is_wal_replay_paused();
 pg_is_wal_replay_paused 
-------------------------
 t

If necessary, the application must also be paused, since continuing to run it makes no business sense.

We then have time to determine the exact time of the Oops-Query.

After further investigation and factoring in some margin, we set the time of the Oops-Query to AFTER 2026-07-31 14:10:00+00!

Catching up to the Oops-Query

Then we let the delayed Standby catch up to the Oops-Query:

#
# postgresql.conf
#
# recovery_min_apply_delay = '5min'   # Achtung: auskommentieren!
recovery_target_time = '2026-07-31 14:10:00+00'
# recovery_target_xid = '135586'
# recovery_target_lsn = '0/312DF748'
recovery_target_inclusive = off
recovery_target_action = 'pause'

And, after verification, promote the Standby database to Primary, followed by a failover:

postgres=# SELECT pg_promote();
 pg_promote 
------------
 t

In our example, we had to accept 5 rows (= 5 seconds) of data loss. If you need greater precision, you’ll have to invest more time in determining the recovery_target_* values. We explain exactly how to do this here: PostgreSQL Point-in-Time Recovery with Oops-Queries.