PostgreSQL Physical Replication

/ home / computer / postgresql / postgresql for mysql admins / .

Details

Physical WAL-Shipping to Standby Servers
Physical WAL-Streaming to Standby Servers

Physical WAL-Shipping to Standby Servers

WAL archiving is used. This is called “warm standby” or file-base “log shipping”.

Primary is in continous archiving mode and Standby in continous recovery mode.

Low administration overhead, low performance impact on the Primary.

Asynchronous, data loss is possible (up to archive_timeout).

WAL ist pushed, not pulled! (Security!)

Mechanism:

If standby.signal exists…
Standby reads WAL from WAL archive (restore_command)
Standby reads WAL from pg_wal
Loop until
- pg_ctl promote
- pg_promote()

Prepare the WAL-Shipping Primary

Enable WAL archiving on Primary. The archive location should be accessible from the Standby even when the Primary is down! (NFS?)

Primary configuration could look as follows:

#
# postgresql.conf
#
wal_level             = 'replica'   # or 'logical'
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 && rsync -a /mnt/backup/wal_archive/%f postgres@10.223.125.83:/mnt/backup/wal_archive/'
archive_timeout       = '5 min'     # Affects RTO
max_wal_senders       = 10          # default, typically big enough
max_replication_slots = 10          # default, typically big enough

Check the Primary error log.
Take a base backup (pg_basebackup)

Setting up a Standby for WAL-Shipping Replication

Restore the base backup

Create the ${PGDATA}/standby.signal file

$ PGDATA='/var/lib/postgresql/18/main'
$ touch ${PGDATA}/standby.signal
$ chown postgres: ${PGDATA}/standby.signal

Standby configuration could look as follows:

#
# postgresql.conf
#
restore_command = 'cp /mnt/backup/wal_archive/%f %p'
recovery_target_timeline = latest   # default

Set other configuration of the Primary as well (in case of fail-over)
Start the Standby
Check the Standby error log.

Monitoring WAL-Shipping Replication

Check the error logs of Primary and Standby!

On O/S level for the Primary:

$ ps -ef | grep -e 'postgres:' -e PID
UID          PID    PPID  C STIME TTY          TIME CMD
postgres  694226  694224  0 13:37 ?        00:00:00 postgres: 18/main: io worker 0
postgres  694227  694224  0 13:37 ?        00:00:00 postgres: 18/main: io worker 1
postgres  694228  694224  0 13:37 ?        00:00:00 postgres: 18/main: io worker 2
postgres  694229  694224  0 13:37 ?        00:00:00 postgres: 18/main: checkpointer 
postgres  694230  694224  0 13:37 ?        00:00:00 postgres: 18/main: background writer 
postgres  694234  694224  0 13:37 ?        00:00:00 postgres: 18/main: walwriter 
postgres  694235  694224  0 13:37 ?        00:00:00 postgres: 18/main: autovacuum launcher 
postgres  694236  694224  0 13:37 ?        00:00:00 postgres: 18/main: archiver last was 000000010000000000000051
postgres  694237  694224  0 13:37 ?        00:00:00 postgres: 18/main: logical replication launcher

and for the Standby:

$ ps -ef | grep -e 'postgres:' -e PID
UID          PID    PPID  C STIME TTY          TIME CMD
postgres   21425   21424  0 13:49 ?        00:00:00 postgres: 18/main: io worker 0
postgres   21426   21424  0 13:49 ?        00:00:00 postgres: 18/main: io worker 1
postgres   21427   21424  0 13:49 ?        00:00:00 postgres: 18/main: io worker 2
postgres   21428   21424  0 13:49 ?        00:00:00 postgres: 18/main: checkpointer 
postgres   21429   21424  0 13:49 ?        00:00:00 postgres: 18/main: background writer 
postgres   21430   21424  0 13:49 ?        00:00:00 postgres: 18/main: startup waiting for 000000010000000000000052

On the Standby in the database:

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-28 14:29:02.62061+00 | 2026-07-28 14:25:47.008693+00 | 00:03:15.611917

The age should varray between 0 and archive_timeout.

Promote the WAL-Shipping Standby to Primary

To promote a Standby to a Primary run the following command:

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

Troubleshooting WAL-Shipping Replication

test=# insert into test values (9999999, 'Some failuer', current_timestamp);
ERROR:  cannot execute INSERT in a read-only transaction

Sources:

Log-Shipping Standby Servers

Physical WAL-Streaming to Standby Servers

WAL archiving is used. This is called “warm standby” or “log streaming”.

Primary is in continous archiving mode and Standby in continous recovery mode.

Asynchronous (by default), data loss is possible (typically under 1 second).

Mechanism:

If standby.signal exists…
Standby reads WAL from WAL archive (restore_command)
Standby reads WAL from pg_wal
Standby reads WAL directly from the Primary over a TCP connection (primary_conninfo)
Loop until
- pg_ctl promote
- pg_promote()

Prepare the WAL-Streaming Primary

Enable WAL archiving.

Create a ROLE (for streaming replication)

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

Adapt pg_hba.conf:

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

postgres=# SELECT pg_reload_conf()

Primary configuration could look as follows:

#
# postgresql.conf
#
listen_addresses      = '*'
wal_level             = 'replica'   # or 'logical'
wal_keep_size         = '10 GB'   # long engough to fix Stanby problems 
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'
max_wal_senders       = 10          # default, typically big enough
max_replication_slots = 10          # default, typically big enough
tcp_keepalives_idle     = 0   # O/S default (7200 s)
tcp_keepalives_interval = 0   # O/S default (75 s)
tcp_keepalives_count    = 0   # O/S default (9)

postgres=# SELECT pg_reload_conf()

The TCP keepalive parameters of your Linux can be found as follows:

$ sysctl net.ipv4 | grep keepalive
net.ipv4.tcp_keepalive_intvl = 75
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_time = 7200

Check the Primary error log.
Take a base backup (pg_basebackup)

Setting up a Standby for WAL-Streaming Replication

Restore the base backup

Create the ${PGDATA}/standby.signal file

$ PGDATA='/var/lib/postgresql/18/main'
$ touch ${PGDATA}/standby.signal
$ chown postgres: ${PGDATA}/standby.signal

Standby configuration could look as follows:

#
# postgresql.conf
#
cluster_name = 'pg18s2'
restore_command = 'cp /mnt/backup/wal_archive/%f %p'
recovery_target_timeline = latest   # default
primary_conninfo = 'host=10.223.125.193 port=5432 user=replication options=''-c wal_sender_timeout=5000'''
archive_cleanup_command = 'pg_archivecleanup /mnt/backup/wal_archive %r'

The password can/must be set in the ~/.pgpass file.

10.223.125.193:5432:replication:replication:secret

Set other configuration of the Primary as well (in case of HA/fail-over)

Check the replication connection:

$ pg_receivewal --verbose --host=10.223.125.193 --port=5432 --user=replication --password --directory=/tmp/
$ ^C

Start the Standby
Check the Standby error log.

Monitoring WAL-Streaming Replication

Some preparation work on the Primary BEFORE inital backup is taken:

postgres=# CREATE EXTENSION IF NOT EXISTS dblink;
postgres=# CREATE ROLE monitor WITH LOGIN PASSWORD 'secret';
-- Adapt pg_hba.conf accordingly

Check the error logs of Primary and Standby!

On O/S level for the Primary:

$ ps -ef | grep -e 'postgres:' -e PID
UID          PID    PPID  C STIME TTY          TIME CMD
postgres 1304492 1304491  0 10:54 ?        00:00:00 postgres: 18/main: io worker 0
postgres 1304493 1304491  0 10:54 ?        00:00:00 postgres: 18/main: io worker 1
postgres 1304494 1304491  0 10:54 ?        00:00:00 postgres: 18/main: io worker 2
postgres 1304495 1304491  0 10:54 ?        00:00:00 postgres: 18/main: checkpointer 
postgres 1304496 1304491  0 10:54 ?        00:00:00 postgres: 18/main: background writer 
postgres 1304498 1304491  0 10:54 ?        00:00:00 postgres: 18/main: walwriter 
postgres 1304499 1304491  0 10:54 ?        00:00:00 postgres: 18/main: autovacuum launcher 
postgres 1304500 1304491  0 10:54 ?        00:00:00 postgres: 18/main: archiver last was 000000010000000000000077
postgres 1304501 1304491  0 10:54 ?        00:00:00 postgres: 18/main: logical replication launcher 
postgres 1304521 1304491  0 10:54 ?        00:00:02 postgres: 18/main: walsender replication 10.223.125.83(39314) streaming 0/780370C0

and for the Standby:

$ ps -ef | grep -e 'postgres:' -e PID
UID          PID    PPID  C STIME TTY          TIME CMD
postgres   30211   30210  0 10:35 ?        00:00:00 postgres: 18/main: io worker 0
postgres   30212   30210  0 10:35 ?        00:00:00 postgres: 18/main: io worker 1
postgres   30213   30210  0 10:35 ?        00:00:00 postgres: 18/main: io worker 2
postgres   30214   30210  0 10:35 ?        00:00:00 postgres: 18/main: checkpointer 
postgres   30215   30210  0 10:35 ?        00:00:00 postgres: 18/main: background writer 
postgres   30216   30210  0 10:35 ?        00:00:00 postgres: 18/main: startup recovering 000000010000000000000078
postgres   30307   30210  0 10:54 ?        00:00:02 postgres: 18/main: walreceiver streaming 0/78059A00

On the Standby in the database:

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:46:21.919321+00 | 00:00:00.004969

An important health indicator of streaming replication is the amount of WAL records generated in the primary (pg_current_wal_lsn()), but not yet applied in the standby (pg_last_wal_receive_lsn()):

On the Standby:

postgres=# SELECT pg_size_pretty(pg_current_wal_lsn - pg_last_wal_receive_lsn()::pg_lsn) AS size_bytes
  FROM dblink('host=10.223.125.193 port=5432 dbname=postgres user=monitor password=secret'
            , 'SELECT pg_current_wal_lsn()') AS t1(pg_current_wal_lsn pg_lsn)
;
 size_bytes 
------------
 224 bytes

On Primary: Large differences between pg_current_wal_lsn and sent_lsn might indicate that the primary server is under heavy load (sent_delay):

postgres=# SELECT usename, application_name, client_addr, backend_start, state, pg_current_wal_lsn(), sent_lsn
     , pg_current_wal_lsn()::pg_lsn - sent_lsn::pg_lsn AS sent_delay, sync_state
  FROM pg_stat_replication
;
   usename   | application_name |  client_addr  |         backend_start         |   state   | pg_current_wal_lsn |  sent_lsn  | sent_delay | sync_state 
-------------+------------------+---------------+-------------------------------+-----------+--------------------+------------+------------+------------
 replication | 18/main          | 10.223.125.83 | 2026-07-29 11:45:03.924125+00 | streaming | 0/811C89C8         | 0/811C89C8 |          0 | async

On the Stanby: Differences between sent_lsn on the Primay and pg_last_wal_receive_lsn() on the Standby might indicate network delay, or that the standby is under heavy load.

TODO: This is NOT correct with several Standbys!! WHERE client_addr = ‘my_ip’ check for client_name???

postgres=# SELECT pg_size_pretty(remote.sent_lsn - pg_last_wal_receive_lsn()::pg_lsn) AS size_bytes
  FROM dblink('host=10.223.125.193 port=5432 dbname=postgres user=monitor password=secret'
            , 'SELECT sent_lsn, client_addr FROM pg_stat_replication') AS remote(sent_lsn pg_lsn, client_addr inet)
-- WHERE client_addr = '10.223.125.83'::inet
;

On a hot Standby a large difference between pg_last_wal_replay_lsn and flushed_lsn indicates that WAL is being received faster than it can be replayed:

postgres=# SELECT status, slot_name, sender_host, sender_port, pg_last_wal_replay_lsn(), flushed_lsn
     , pg_last_wal_replay_lsn()::pg_lsn - flushed_lsn::pg_lsn AS replay_delay
  FROM pg_stat_wal_receiver
;
  status   | slot_name |  sender_host   | sender_port | pg_last_wal_replay_lsn | flushed_lsn | replay_delay 
-----------+-----------+----------------+-------------+------------------------+-------------+--------------
 streaming |           | 10.223.125.193 |        5432 | 0/8128BD18             | 0/8128BD18  |            0
;

Checks on Primary:

postgres=# SELECT COUNT(*) AS current_wal_senders
     , current_setting('max_wal_senders')::int AS max_wal_senders
     , CONCAT(COUNT(*)::float * 100.0 / current_setting('max_wal_senders')::float, '%') AS utilization
  FROM pg_stat_activity
 WHERE backend_type = 'walsender'
;
 current_wal_senders | max_wal_senders | utilization 
---------------------+-----------------+-------------
                   1 |              10 | 10%


postgres=# SELECT COUNT(*)
     , current_setting('max_replication_slots')::int AS max_replication_slots
     , CONCAT(COUNT(*)::float * 100.0 / current_setting('max_replication_slots')::float, '%') AS utilization
  FROM pg_stat_replication
;
 count | max_replication_slots | utilization 
-------+-----------------------+-------------
     1 |                    10 | 10%

Promote the WAL-Streaming Standby to Primary

To promote a Standby to a Primary run the following command:

postgres=# SELECT pg_promote();

LOG:  received promote request
FATAL:  terminating walreceiver process due to administrator command
cp: cannot stat '/mnt/backup/wal_archive/00000002.history': No such file or directory
cp: cannot stat '/mnt/backup/wal_archive/000000010000000000000085': No such file or directory
LOG:  unexpected pageaddr 0/80000000 in WAL segment 000000010000000000000085, LSN 0/85000000, offset 0
LOG:  redo done at 0/84000148 system usage: CPU: user: 0.95 s, system: 1.18 s, elapsed: 7387.72 s
LOG:  last completed transaction was at log time 2026-07-29 13:36:27.678643+00
cp: cannot stat '/mnt/backup/wal_archive/000000010000000000000084': No such file or directory
cp: cannot stat '/mnt/backup/wal_archive/00000002.history': No such file or directory
LOG:  selected new timeline ID: 2
cp: cannot stat '/mnt/backup/wal_archive/00000001.history': No such file or directory
LOG:  archive recovery complete
LOG:  checkpoint starting: force
LOG:  database system is ready to accept connections
LOG:  checkpoint complete: wrote 0 buffers (0.0%), wrote 2 SLRU buffers; 0 WAL file(s) added, 1 removed, 1 recycled; write=0.011 s, sync=0.004 s, total=0.053 s; sync files=2, longest=0.003 s, average=0.002 s; distance=27827 kB, estimate=27827 kB; lsn=0/850000B8, redo lsn=0/85000060

Troubleshooting WAL-Streaming Replication

How does it look like if Primary is down/dead?
- Primary cannot be reached.
- Error in Stanby error log. Difficult to detect!
- Age is increasing. Easy to detect!
- Queries with dblink will fail.
- Queries on Primary will fail.

How does it look if network has a split-brain?

Perparation:

$ nft add table inet filter
$ nft list tables
$ nft add chain inet filter input {type filter hook input priority 0 \;}
$ nft list chains
$ nft add rule inet filter input ip saddr 10.223.125.21 drop
$ nft list ruleset

Error Log on Primary:

replication@[unknown] LOG:  terminating walsender process due to replication timeout
replication@[unknown] STATEMENT:  START_REPLICATION 0/98000000 TIMELINE 1

Error Log on Standby:

FATAL:  streaming replication receiver "Primary" could not connect to the primary server:
        connection to server at "10.223.125.193", port 5432 failed: Connection timed out
        Is the server running on that host and accepting TCP/IP connections?

Age goes up
DB link hangs
replay_delay is empty
Primary: Number of used slots does not match (2 vs. 1)
Clean-up:
```
$ nft flush ruleset
```

Source:

https://www.cybertec-postgresql.com/en/monitoring-postgresql-replication/

Replication Slots

Replication slots provide an automated way to ensure

that the Primary server does not remove WAL segments until they have been received by all standbys, and
that the primary does not remove rows which could cause a recovery conflict even when the standby is disconnected.

Alterative: wal_keep_size or archiving the WALs with archive_command. Disadvantage: More WAL segments than required.

Create Replication Slots

On the Primary:

postgres=# SELECT * FROM pg_create_physical_replication_slot('slot_for_pg18s2');

Use Replication Slots

On Standby:

#
# postgresql.conf
#
primary_conninfo  = 'host=10.223.125.193 port=5432 user=replication application_name=slot_for_pg18s2 options=''-c wal_sender_timeout=5000'''
primary_slot_name = 'slot_for_pg18s2'

Monitor Replication Slots

Check Error Log on Primary and Standby!

On Primary:

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_pg18s2 | physical  | t      | reserved   | f        | f

Drop Replication Slot

postgres=# SELECT pg_drop_replication_slot('slot_for_pg18s2');

Cascading Replication

Standby acting as a Relay:

Reduce number of connections to the Primary.
Minimize inter-site bandwidth
Delayed Replication?

Upstream Server -> Cascading Standby -> Downstream Server

Synchronous replication settings have no effect on cascading replication.

Source:

https://www.postgresql.org/docs/current/warm-standby.html#CASCADING-REPLICATION

Synchronous Replication

Durability on a per-transaction basis (synchronous_commit).

2 different synchronous replication modes:

priority-based: FIRST <n> (...)
quorum-based: ANY <n> (...)

Configuration

`synchronous_commit`	Durability and visibility of data
`remmote_apply`	Data are visible on Standby (`wsrep_sync_wait = 1`)
`on` (default)	Data are written and flushed on Standby (survive an O/S crash, `wsrep_sync_wait = 0`)
`remote_write`	Data are written on Standby but NOT flushed (survive a PostgreSQL crash)
`local`	Data are written and flushed to WAL on Primary but not flushed (`innodb_flush_log_a_trx_commit = 1`)
`off`	Data are written to WAL on Primary but NOT flushed (`innodb_flush_log_a_trx_commit = 2`)

On Primary:

#
# postgresql.conf
#
synchronous_commit        = 'on'
synchronous_standby_names = 'FIRST 1 (pg18s1, pg18s2)'   # or ANY

Monitoring

On Primary:

replication@[unknown] LOG:  standby "pg18s2" is now a synchronous standby with priority 2
replication@[unknown] STATEMENT:  START_REPLICATION SLOT "slot_for_pg18s2" 0/B9000000 TIMELINE 1
replication@[unknown] LOG:  standby "pg18s1" is now a synchronous standby with priority 1
replication@[unknown] STATEMENT:  START_REPLICATION 0/B9000000 TIMELINE 1

On Primary:

postgres=# SELECT usename, application_name, client_addr, backend_start, state, pg_current_wal_lsn(), sent_lsn
     , pg_current_wal_lsn()::pg_lsn - sent_lsn::pg_lsn AS sent_delay, sync_state
     , write_lag, flush_lag, replay_lag, sync_priority
  FROM pg_stat_replication
;
   usename   | application_name |  client_addr  |         backend_start         |   state   | pg_current_wal_lsn |  sent_lsn  | sent_delay | sync_state |    write_lag    |    flush_lag    |   replay_lag    | sync_priority 
-------------+------------------+---------------+-------------------------------+-----------+--------------------+------------+------------+------------+-----------------+-----------------+-----------------+---------------
 replication | pg18s1           | 10.223.125.83 | 2026-07-30 09:07:48.782785+00 | streaming | 0/BB00D8E8         | 0/BB00D8E8 |          0 | sync       | 00:00:00.000184 | 00:00:00.002295 | 00:00:00.002421 |             1
 replication | pg18s2           | 10.223.125.21 | 2026-07-30 09:07:48.782647+00 | streaming | 0/BB00D8E8         | 0/BB00D8E8 |          0 | potential  | 00:00:00.00009  | 00:00:00.001104 | 00:00:00.00119  |             2

Troubleshooting

On Primary:

replication@[unknown] LOG:  standby "pg18s2" is now a synchronous standby with priority 2
replication@[unknown] STATEMENT:  START_REPLICATION SLOT "slot_for_pg18s2" 0/C1000000 TIMELINE 1
replication@[unknown] LOG:  standby "pg18s1" is now a synchronous standby with priority 1
replication@[unknown] STATEMENT:  START_REPLICATION 0/C1000000 TIMELINE 1

Sources:

Fail-over

If Primary fails, Standby should begin failover procedure.
If Standby fails, no failover need to take place.
- If Standby can be restarted, recovery process will be restartet (provided that WAL are still available).
- If Standby cannot be restarted, or WAL cannot provided any more by the Primary a full new Standby should be created.

If Standby becomes new Primary, it must asure old Primary does not restart (STONITH)! Otherwiese -> split-brain!
- Rebuild the old Primary as a new Standby.

TODO

Which Standby is the most advanced (for failover)?

Check on all Stanbys:

postgres=# SELECT current_setting('cluster_name'), pg_is_in_recovery()
     , pg_last_xact_replay_timestamp(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()
;
 current_setting | pg_is_in_recovery | pg_last_xact_replay_timestamp | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn 
-----------------+-------------------+-------------------------------+-------------------------+------------------------
 pg18s1          | t                 | 2026-07-30 09:53:23.867386+00 | 0/C30000A0              | 0/C30000A0

 current_setting | pg_is_in_recovery | pg_last_xact_replay_timestamp | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn 
-----------------+-------------------+-------------------------------+-------------------------+------------------------
 pg18s2          | t                 | 2026-07-30 09:53:12.507788+00 | 0/C2000000              | 0/C204A748

Source:

https://www.postgresql.org/docs/current/warm-standby-failover.html

Switchover

A (graceful) switchover refers to the switching of roles between the Primary and the Standby server without any urgent need (for example, as part of a scheduled maintenance).

TODO –> Cluster Software? Cluster manager!

Sources:

Streaming Replication Protocol

Hot Standby

Hot Standby: Ability to conect to the server and run read-only queries while the server is in archive recovery or standby mode.

hot_standby = on (default)

Check:

postgres=# show in_hot_standby;   -- new
 in_hot_standby 
----------------
 on

postgres=# show transaction_read_only;   -- old <= v13
 transaction_read_only 
-----------------------
 on

max_standby_archive_delay = 30000 # conflict with read from WAL archive (in ms)
max_standby_streaming_delay = 30000 # conflict with read from WAL streaming (in ms)
Even VACUUM might lead to conflicts.
Long running queries on the Standby are prone to cancellation.

hot_standby_feedback prevents VACUUM from removing recently-dead row. Check on Standby:

postgres=# SELECT * FROM pg_stat_database_conflicts;
 datid |  datname  | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock | confl_active_logicalslot 
-------+-----------+------------------+------------+----------------+-----------------+----------------+--------------------------
     5 | postgres  |                0 |          0 |              0 |               0 |              0 |                        0
 16388 | enswitch  |                0 |          0 |              0 |               0 |              0 |                        0
     1 | template1 |                0 |          0 |              0 |               0 |              0 |                        0
     4 | template0 |                0 |          0 |              0 |               0 |              0 |                        0
 21498 | oli       |                0 |          0 |              0 |               0 |              0 |                        0
 21506 | test      |                0 |          0 |              0 |               0 |              0 |                        0
(6 rows)

log_recovery_conflict_waits can be set.
These parameters should be set on Standby equal or greater than on the Primary:
- max_connections
- max_prepared_transactions
- max_locks_per_transaction
- max_wal_senders
- max_worker_processes

Monitor on Primary (backend_xmin):

postgres=# SELECT usename, application_name, backend_start, query_start, wait_event_type, backend_xmin, backend_type FROM pg_stat_activity;
   usename   | application_name |         backend_start         |          query_start          | wait_event_type | backend_xmin |         backend_type         
-------------+------------------+-------------------------------+-------------------------------+-----------------+--------------+------------------------------
 postgres    | psql             | 2026-08-03 12:44:25.860219+00 | 2026-08-03 13:04:13.802232+00 |                 |         6212 | client backend
 replication | pg18s1           | 2026-08-03 12:58:04.276161+00 | 2026-08-03 12:58:04.282657+00 | Activity        |         6139 | walsender
...
(11 rows)

Caution: It takes some time (recovery_min_apply_delay?) in delayed replication until backed_xmin from walsender is reported!?!

Consider wal_receiver_timeout and wal_sender_timeout when seeing walsender Process switching on and off on a low traffic system!

LOG:  started streaming WAL from primary at 0/B000000 on timeline 1
FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 00000001000000000000000B has already been removed
LOG:  waiting for WAL to become available at 0/B002000

Source:

https://www.postgresql.org/docs/current/hot-standby.html
Christophe Pettus, PostgreSQL Experts, 2026-07-31: All Your GUCs in a Row: hot_standby_feedback

`pg_rewind`

Tool to synchonize a PostgreSQL instance with another copy of the same instance.
- Bring an old Primary server back online after failover as a standby of the new Primary.
- Use a Standby read-write for testing purposes and bring it back to Standby after the tests.
A successful rewind is analogous to a base backup of the source data directory.
- Only change blocks are copied.
- As such the rewind operation is significantly faster when the database is large and only a small fraction of blocks differ.
pg_rewind copies configuration files entirely from the source. It may be required to correct the configuration for recovery before starting the server!

Preparation

On Primary:

  postgres=# CREATE ROLE rewind WITH LOGIN PASSWORD 'secret';
  postgres=# GRANT EXECUTE ON function pg_catalog.pg_ls_dir(text, boolean, boolean) TO rewind;
  postgres=# GRANT EXECUTE ON function pg_catalog.pg_stat_file(text, boolean) TO rewind;
  postgres=# GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text) TO rewind;
  postgres=# GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text, bigint, bigint, boolean) TO rewind;

Rewind

On Standby:

$ sudo systemctl stop postgresql
$ PATH=${PATH}:/usr/lib/postgresql/18/bin
$ PGDATA='/var/lib/postgresql/18/main'
$ CONNSTR='host=10.223.125.193 port=5432 user=rewind dbname=postgres password=secret'

$ pg_rewind --target-pgdata=${PGDATA} --source-server="${CONNSTR}" --progress --config-file=/etc/postgresql/18/main/postgresql.conf --restore-target-wal --write-recovery-conf
pg_rewind: connected to server
pg_rewind: servers diverged at WAL location 0/DC00CA10 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/DB003568 on timeline 1
pg_rewind: reading source file list
pg_rewind: reading target file list
pg_rewind: reading WAL in target
pg_rewind: need to copy 1968 MB (total source directory size is 2111 MB)
2015311/2015311 kB (100%) copied
pg_rewind: creating backup label and updating control file
pg_rewind: syncing target data directory
pg_rewind: Done!
$ rm /var/lib/postgresql/18/main/postgresql.auto.conf 
$ sudo systemctl stop postgresql

Source:

https://www.postgresql.org/docs/current/app-pgrewind.html

pg_receivewal

Stream WAL from running PostgreSQL instance using the streaming replication protocol to a local directory.

for archive location or
for doing PiTR

Starting point:

Scan the local target directory to find the newest completed WAL segment.
If no starting point is found restart_lsn on replication slot is used.
If no starting point is received, latest WAL flush location is used.

$ pg_receivewal --verbose --host=10.223.125.193 --port=5432 --user=replication --password --directory=/tmp/
Password: 
pg_receivewal: starting log streaming at 0/E5000000 (timeline 1)
pg_receivewal: finished segment at 0/E6000000 (timeline 1)
pg_receivewal: finished segment at 0/E7000000 (timeline 1)
$ ^C

Source:

https://www.postgresql.org/docs/current/app-pgreceivewal.html

Delayed Replication

Artificially Delayed Replication with PostgreSQL

PostgreSQL Physical Replication

Table of Contents

Physical WAL-Shipping to Standby Servers

Prepare the WAL-Shipping Primary

Setting up a Standby for WAL-Shipping Replication

Monitoring WAL-Shipping Replication

Promote the WAL-Shipping Standby to Primary

Troubleshooting WAL-Shipping Replication

Physical WAL-Streaming to Standby Servers

Prepare the WAL-Streaming Primary

Setting up a Standby for WAL-Streaming Replication

Monitoring WAL-Streaming Replication

Promote the WAL-Streaming Standby to Primary

Troubleshooting WAL-Streaming Replication

Replication Slots

Create Replication Slots

Use Replication Slots

Monitor Replication Slots

Drop Replication Slot

Cascading Replication

Synchronous Replication

Configuration

Monitoring

Troubleshooting

Fail-over

Switchover

Hot Standby

pg_rewind

Preparation

Rewind

pg_receivewal

Delayed Replication

`pg_rewind`