Rejoining an Old Primary as a New Standby After Failover with pg_rewind and Replication Slots

Rejoining an Old Primary as a New Standby After Failover with pg_rewind and Replication Slots

Table of Contents

Environment

HostnameIPPostgreSQL VersionPostgreSQL PortcURRENT ROLEfuture role
pgdb01192.168.2.21PostgreSQL v175432Primary (Simulate failure)Recover as New Standby using pg_rewind and replication slots 
pgdb02192.168.2.22PostgreSQL v175432Standby 1To be promoted as new primary
pgdb03192.168.3.20PostgreSQL v175432Standby 2Standby 2

Pre-requistites

For pg_rewind, PostgreSQL requires one of the following

  • wal_log_hints = on (can be enabled after initialization) <—-  With wal_log_hints = on, PostgreSQL logs full-page images for hint-bit updates, allowing pg_rewind to identify modified pages accurately.

—–  OR  —–

  • Data checksums enabled when the cluster is initialized with initdb.     <—-   With data checksums, PostgreSQL can detect changed pages.

[postgres@pgdb01 ~]$ psql -c "show wal_log_hints;"
wal_log_hints
---------------
on
(1 row)

[postgres@pgdb01 ~]$ psql -c "show max_wal_size;"
max_wal_size
--------------
128MB
(1 row)

[postgres@pgdb01 ~]$ psql -c "show wal_keep_size;"
wal_keep_size
---------------
256MB
(1 row)

[postgres@pgdb01 ~]$ psql -c "show max_slot_wal_keep_size;"
max_slot_wal_keep_size
------------------------
-1
(1 row)

[postgres@pgdb01 ~]$


[postgres@pgdb01 ~]$ psql -c "select slot_name,slot_type,active,wal_status from pg_replication_slots;"
slot_name | slot_type | active | wal_status
-------------+-----------+--------+------------
pgdb03_slot | physical | t | reserved
pgdb02_slot | physical | t | reserved
(2 rows)

[postgres@pgdb01 ~]$

pg_rewind: How it works

When a standby is promoted to become the new primary, PostgreSQL creates a new timeline and writes a corresponding timeline history (.history) file in the pg_wal directory. This new timeline allows the promoted server to generate WAL independently from the old primary. Other standbys can read the .history file to understand exactly where the new timeline branched off. Tools like pg_rewind and standby recovery use this information to determine the common divergence point between servers.

Divergence Analysis:
pg_rewind reads the timeline history files and control files to find the exact Log Sequence Number (LSN) where the Target server diverged from the Source after promotion.

Change Tracking:
It scans the Target’s WAL files starting from the divergence point to identify all data blocks that were modified on the old timeline.

Delta Block Synchronization:
Instead of taking a full base backup, pg_rewind copies only the changed data blocks from the Source and overwrites the divergent blocks on the Target.

Metadata Alignment:
It updates and synchronizes essential files such as pg_control, transaction status files, and configuration files so the Target matches the Source’s current timeline.

Consistency Recovery:
After the rewind process completes, the Target is started in recovery mode, replays WAL from the Source, and becomes a consistent standby server again.

1. Verify Cluster Synchronization

[postgres@pgdb01 ~]$ psql -c "SELECT pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 f
(1 row)

[postgres@pgdb01 ~]$ psql -c "SELECT application_name, client_addr, state, sync_state, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replica_lag FROM pg_stat_replication;"
 application_name | client_addr  |   state   | sync_state | replica_lag
------------------+--------------+-----------+------------+-------------
 pgdb03           | 192.168.3.20 | streaming | async      | 0 bytes
 pgdb02           | 192.168.2.22 | streaming | async      | 0 bytes
(2 rows)

[postgres@pgdb01 ~]$ 

[postgres@pgdb01 ~]$ psql -x -c "SELECT * FROM pg_stat_replication;"
-[ RECORD 1 ]----+------------------------------
pid              | 3318
usesysid         | 16384
usename          | repuser
application_name | pgdb03
client_addr      | 192.168.3.20
client_hostname  |
client_port      | 37536
backend_start    | 2026-08-03 15:19:47.939773+08
backend_xmin     |
state            | streaming
sent_lsn         | B/DAD25488
write_lsn        | B/DAD25488
flush_lsn        | B/DAD25488
replay_lsn       | B/DAD25488
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2026-08-03 15:25:08.396784+08
-[ RECORD 2 ]----+------------------------------
pid              | 3304
usesysid         | 16384
usename          | repuser
application_name | pgdb02
client_addr      | 192.168.2.22
client_hostname  |
client_port      | 44848
backend_start    | 2026-08-03 15:13:55.003813+08
backend_xmin     |
state            | streaming
sent_lsn         | B/DAD25488
write_lsn        | B/DAD25488
flush_lsn        | B/DAD25488
replay_lsn       | B/DAD25488
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2026-08-03 15:25:15.039696+08

[postgres@pgdb01 ~]$

2. Simulate Failure on the Primary Server (pgdb01)

DISCLAIMER: The kill command given below is just for learning purposes and should only be used on testing systems. We will not take any responsibility of any consequences or loss of data caused by this command.

Note: In this demonstration, I used a simple KILL command to simulate a PostgreSQL failover. In real-world scenarios, failover events may occur due to factors such as server unavailability, disk corruption, or other critical system failures.

[postgres@pgdb01 ~]$ ps -ef | grep postgres
root        3200    3145  0 14:59 pts/0    00:00:00 su - postgres
postgres    3201    3200  0 14:59 pts/0    00:00:00 -bash
postgres    3231       1  0 14:59 ?        00:00:00 /usr/pgsql-17/bin/postgres
postgres    3232    3231  0 14:59 ?        00:00:00 postgres: logger
postgres    3233    3231  0 14:59 ?        00:00:00 postgres: checkpointer
postgres    3234    3231  0 14:59 ?        00:00:00 postgres: background writer
postgres    3267    3231  0 15:02 ?        00:00:00 postgres: walwriter
postgres    3268    3231  0 15:02 ?        00:00:00 postgres: autovacuum launcher
postgres    3269    3231  0 15:02 ?        00:00:00 postgres: archiver last was 000000130000000B000000DA.partial
postgres    3270    3231  0 15:02 ?        00:00:00 postgres: logical replication launcher
postgres    3304    3231  0 15:13 ?        00:00:00 postgres: walsender repuser 192.168.2.22(44848) streaming B/DAD25488
postgres    3318    3231  0 15:19 ?        00:00:00 postgres: walsender repuser 192.168.3.20(37536) streaming B/DAD25488
postgres    3413    3201 99 15:27 pts/0    00:00:00 ps -ef
postgres    3414    3201  0 15:27 pts/0    00:00:00 grep --color=auto postgres
[postgres@pgdb01 ~]$
[postgres@pgdb01 ~]$ kill -9 3231
[postgres@pgdb01 ~]$ 2026-08-03 15:27:48.361 +08 [3232] DEBUG:  logger shutting down

[postgres@pgdb01 ~]$ ps -ef | grep postgres
root        3200    3145  0 14:59 pts/0    00:00:00 su - postgres
postgres    3201    3200  0 14:59 pts/0    00:00:00 -bash
postgres    3415    3201  0 15:27 pts/0    00:00:00 ps -ef
postgres    3416    3201  0 15:27 pts/0    00:00:00 grep --color=auto postgres
[postgres@pgdb01 ~]$

3. Promote pgdb02 as the New Primary

pg_ctl promote -D /pgData/pgsql17/data    --- OR  ------ psql -c "select pg_promote();"


[postgres@pgdb02 ~]$ psql -c "select pg_promote();"
 pg_promote
------------
 t   <---- means successful
(1 row)

[postgres@pgdb02 ~]$
[postgres@pgdb02 ~]$ psql -c "SELECT pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 f   <----- Means this is primary server
(1 row)

[postgres@pgdb02 ~]$

PostgreSQL creates a timeline history file in pg_wal When you promote a standby as Primary

[postgres@pgdb02 ~]$ cat /pgData/pgsql17/data/pg_wal/00000015.history
1       0/FA886470      no recovery target specified

2       0/FC0005B0      no recovery target specified

3       1/13000060      no recovery target specified

5       1/180013C8      no recovery target specified

6       1/18006048      no recovery target specified

7       1/2FDEFA70      no recovery target specified

8       1/BD000E60      no recovery target specified

9       2/DF008B48      no recovery target specified

10      4/2F016DA8      no recovery target specified

11      4/2F0363C0      no recovery target specified

12      4/2F040A78      no recovery target specified

13      4/F245D850      no recovery target specified

14      4/F28BC248      no recovery target specified

15      5/8545DA10      no recovery target specified

16      5/858BBF90      no recovery target specified

17      6/A945E860      no recovery target specified

18      7/CA944790      no recovery target specified

19      B/DA4670F8      no recovery target specified

20      B/DAD25488      no recovery target specified
[postgres@pgdb02 ~]$

[postgres@pgdb02 ~]$ pg_controldata /pgData/pgsql17/data | grep "Latest checkpoint's TimeLineID"
Latest checkpoint's TimeLineID:       21
[postgres@pgdb02 ~]$

4. Create Physical Replication Slots on New Primary (pgdb02)

[postgres@pgdb02 ~]$ psql -c "select slot_name,slot_type,active,wal_status from pg_replication_slots;"
 slot_name | slot_type | active | wal_status
-----------+-----------+--------+------------
(0 rows)

[postgres@pgdb02 ~]$ psql -c "SELECT * FROM pg_create_physical_replication_slot('pgdb03_slot', true);"
  slot_name  |    lsn
-------------+------------
 pgdb03_slot | B/DAD254C0
(1 row)

[postgres@pgdb02 ~]$ psql -c "SELECT * FROM pg_create_physical_replication_slot('pgdb01_slot', true);"   # This replication slot for node pgdb01
  slot_name  |    lsn
-------------+------------
 pgdb01_slot | B/DAD254C0
(1 row)

[postgres@pgdb02 ~]$ psql -c "select slot_name,slot_type,active,wal_status from pg_replication_slots;"  # This replication slot for node pgdb03
  slot_name  | slot_type | active | wal_status
-------------+-----------+--------+------------
 pgdb03_slot | physical  | f      | reserved
 pgdb01_slot | physical  | f      | reserved
(2 rows)

[postgres@pgdb02 ~]$

5. Configure pgdb03 to Follow the New Primary (pgdb02)

on server pgdb03:

Edit postgresql.auto.conf

from:
primary_slot_name = 'pgdb03_slot'
primary_conninfo = 'host=192.168.2.21 port=5432 user=repuser application_name=pgdb03'

to:
primary_slot_name = 'pgdb03_slot'
primary_conninfo = 'host=192.168.2.22 port=5432 user=repuser application_name=pgdb03'

6. Reload pgdb03 and Verify Streaming Replication


[postgres@pgdb03 ~]$ pg_ctl reload -D /pgData/pgsql17/data/
server signaled
[postgres@pgdb03 ~]$

2026-08-03 15:50:43.541 +08 [3334] LOG:  received SIGHUP, reloading configuration files
2026-08-03 15:50:43.542 +08 [3334] LOG:  parameter "primary_conninfo" changed to "host=192.168.2.22 port=5432 user=repuser application_name=pgdb03"
2026-08-03 15:50:43.591 +08 [3762] LOG:  fetching timeline history file for timeline 21 from primary server
2026-08-03 15:50:43.597 +08 [3762] LOG:  started streaming WAL from primary at B/DA000000 on timeline 20
2026-08-03 15:50:43.969 +08 [3762] LOG:  replication terminated by primary server
2026-08-03 15:50:43.969 +08 [3762] DETAIL:  End of WAL reached on timeline 20 at B/DAD25488.
2026-08-03 15:50:43.971 +08 [3338] LOG:  new target timeline is 21
2026-08-03 15:50:43.975 +08 [3762] LOG:  restarted WAL streaming at B/DA000000 on timeline 21
2026-08-03 15:50:48.553 +08 [3336] LOG:  restartpoint starting: time
2026-08-03 15:50:48.659 +08 [3336] LOG:  restartpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.102 s, sync=0.001 s, total=0.106 s; sync files=2, longest=0.001 s, average=0.001 s; distance=3 kB, estimate=3 kB; lsn=B/DAD25550, redo lsn=B/DAD254C0
2026-08-03 15:50:48.659 +08 [3336] LOG:  recovery restart point at B/DAD254C0
2026-08-03 15:50:48.659 +08 [3336] DETAIL:  Last completed transaction was at log time 2026-08-03 15:18:14.461747+08.


[postgres@pgdb03 log]$ psql -x -c "SELECT * FROM pg_stat_wal_receiver;"
-[ RECORD 1 ]---------+-----------------------------------------------------------------
pid                   | 3762
status                | streaming
receive_start_lsn     | B/DA000000
receive_start_tli     | 21
written_lsn           | B/DAD25600
flushed_lsn           | B/DAD25600
received_tli          | 21
last_msg_send_time    | 2026-08-03 15:52:44.399955+08
last_msg_receipt_time | 2026-08-03 15:52:44.400704+08
latest_end_lsn        | B/DAD25600
latest_end_time       | 2026-08-03 15:50:43.975105+08
slot_name             | pgdb03_slot
sender_host           | 192.168.2.22
sender_port           | 5432
conninfo              | host=192.168.2.22 port=5432 user=repuser application_name=pgdb03

[postgres@pgdb03 log]$


[postgres@pgdb03 log]$ psql -c "SELECT status, written_lsn, flushed_lsn, latest_end_lsn,pg_size_pretty(pg_wal_lsn_diff(latest_end_lsn, flushed_lsn)) AS receive_lag FROM pg_stat_wal_receiver;"
  status   | written_lsn | flushed_lsn | latest_end_lsn | receive_lag
-----------+-------------+-------------+----------------+-------------
 streaming | B/DAD25600  | B/DAD25600  | B/DAD25600     | 0 bytes
(1 row)

[postgres@pgdb03 log]$

7. Verify SYNC and Replication Slot Status on New Primary (pgdb02)


[postgres@pgdb02 ~]$ psql -c "SELECT application_name, client_addr, state, sync_state, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replica_lag FROM pg_stat_replication;"
 application_name | client_addr  |   state   | sync_state | replica_lag
------------------+--------------+-----------+------------+-------------
 pgdb03           | 192.168.3.20 | streaming | async      | 0 bytes
(1 row)

[postgres@pgdb02 ~]$


[postgres@pgdb02 ~]$ psql -x -c "SELECT * FROM pg_stat_replication;"
-[ RECORD 1 ]----+------------------------------
pid              | 3721
usesysid         | 16384
usename          | repuser
application_name | pgdb03
client_addr      | 192.168.3.20
client_hostname  |
client_port      | 36568
backend_start    | 2026-08-03 15:50:43.560514+08
backend_xmin     |
state            | streaming
sent_lsn         | B/DAD25600
write_lsn        | B/DAD25600
flush_lsn        | B/DAD25600
replay_lsn       | B/DAD25600
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2026-08-03 15:53:54.434863+08

[postgres@pgdb02 ~]$


[postgres@pgdb02 ~]$ psql -c "select slot_name,slot_type,active,wal_status from pg_replication_slots;"
  slot_name  | slot_type | active | wal_status
-------------+-----------+--------+------------
 pgdb03_slot | physical  | t      | reserved
 pgdb01_slot | physical  | f      | reserved
(2 rows)

[postgres@pgdb02 ~]$

8. Generate Heavy Workload on the New Primary

[postgres@pgdb02 ~]$ psql -c "truncate table demo_wal_test;"
TRUNCATE TABLE
[postgres@pgdb02 ~]$ psql -c "INSERT INTO demo_wal_test (data) SELECT repeat('SET3-PHASE-A-', 200) FROM generate_series(1, 20000000);"
INSERT 0 20000000
[postgres@pgdb02 ~]$ psql -c "select count(*) from demo_wal_test;"
  count
----------
 20000000   <----- Total rows 
(1 row)

[postgres@pgdb02 ~]$ psql -c "SELECT slot_name, active, wal_status, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal FROM pg_replication_slots;"
  slot_name  | active | wal_status | retained_wal
-------------+--------+------------+--------------
 pgdb03_slot | t      | reserved   | 0 bytes
 pgdb01_slot | f      | extended   | 5776 MB
(2 rows)

[postgres@pgdb02 ~]$

9. Verify pgdb03 Remains Synchronized

On pgdb02 (new primary):

[postgres@pgdb02 ~]$ psql -c "SELECT application_name, client_addr, state, sync_state, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replica_lag FROM pg_stat_replication;"
 application_name | client_addr  |   state   | sync_state | replica_lag
------------------+--------------+-----------+------------+-------------
 pgdb03           | 192.168.3.20 | streaming | async      | 0 bytes
(1 row)

[postgres@pgdb02 ~]$ psql -x -c "SELECT * FROM pg_stat_replication;"
-[ RECORD 1 ]----+------------------------------
pid              | 3721
usesysid         | 16384
usename          | repuser
application_name | pgdb03
client_addr      | 192.168.3.20
client_hostname  |
client_port      | 36568
backend_start    | 2026-08-03 15:50:43.560514+08
backend_xmin     |
state            | streaming
sent_lsn         | D/43D1ACB8
write_lsn        | D/43D1ACB8
flush_lsn        | D/43D1ACB8
replay_lsn       | D/43D1ACB8
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2026-08-03 16:12:39.516343+08

[postgres@pgdb02 ~]$


On pgdb03 (standby):

[postgres@pgdb03 ~]$ psql -c "select count(*) from demo_wal_test;"
  count
----------
 20000000
(1 row)

[postgres@pgdb03 ~]$

[postgres@pgdb03 ~]$ psql -x -c "SELECT * FROM pg_stat_wal_receiver;"
-[ RECORD 1 ]---------+-----------------------------------------------------------------
pid                   | 3762
status                | streaming
receive_start_lsn     | B/DA000000
receive_start_tli     | 21
written_lsn           | D/43D1ADA0
flushed_lsn           | D/43D1ADA0
received_tli          | 21
last_msg_send_time    | 2026-08-03 16:13:57.551217+08
last_msg_receipt_time | 2026-08-03 16:13:57.551684+08
latest_end_lsn        | D/43D1ADA0
latest_end_time       | 2026-08-03 16:12:57.526891+08
slot_name             | pgdb03_slot
sender_host           | 192.168.2.22
sender_port           | 5432
conninfo              | host=192.168.2.22 port=5432 user=repuser application_name=pgdb03

[postgres@pgdb03 ~]$ psql -c "SELECT status, written_lsn, flushed_lsn, latest_end_lsn,pg_size_pretty(pg_wal_lsn_diff(latest_end_lsn, flushed_lsn)) AS receive_lag FROM pg_stat_wal_receiver;"
  status   | written_lsn | flushed_lsn | latest_end_lsn | receive_lag
-----------+-------------+-------------+----------------+-------------
 streaming | D/43D1ADA0  | D/43D1ADA0  | D/43D1ADA0     | 0 bytes
(1 row)

[postgres@pgdb03 ~]$

10. Run pg_rewind on the Old Primary (pgdb01)

[postgres@pgdb01 ~]$ cd $PGDATA
[postgres@pgdb01 data]$ ls -l
total 92
-rw-------. 1 postgres postgres   177 Aug  3 01:41 backup_label.old
drwx------. 5 postgres postgres    33 Aug  3 15:18 base
-rw-------. 1 postgres postgres    30 Aug  3 14:59 current_logfiles
drwx------. 2 postgres postgres  4096 Aug  3 14:59 global
drwx------. 2 postgres postgres   136 Aug  2 18:50 log
drwx------. 2 postgres postgres     6 Aug  2 18:50 pg_commit_ts
drwx------. 2 postgres postgres     6 Aug  2 18:50 pg_dynshmem
-rw-------. 1 postgres postgres  6315 Aug  3 01:37 pg_hba.conf
-rw-------. 1 postgres postgres  2640 Aug  3 01:37 pg_ident.conf
drwx------. 4 postgres postgres    68 Aug  3 15:18 pg_logical
drwx------. 4 postgres postgres    36 Aug  2 18:50 pg_multixact
drwx------. 2 postgres postgres     6 Aug  2 18:50 pg_notify
drwx------. 4 postgres postgres    44 Aug  3 15:04 pg_replslot
drwx------. 2 postgres postgres     6 Aug  2 18:50 pg_serial
drwx------. 2 postgres postgres     6 Aug  2 18:50 pg_snapshots
drwx------. 2 postgres postgres     6 Aug  3 14:59 pg_stat
drwx------. 2 postgres postgres     6 Aug  2 18:50 pg_stat_tmp
drwx------. 2 postgres postgres    18 Aug  3 01:54 pg_subtrans
drwx------. 2 postgres postgres     6 Aug  2 18:50 pg_tblspc
drwx------. 2 postgres postgres     6 Aug  2 18:50 pg_twophase
-rw-------. 1 postgres postgres     3 Aug  2 18:50 PG_VERSION
drwx------. 4 postgres postgres 45056 Aug  3 15:02 pg_wal
drwx------. 2 postgres postgres    18 Aug  2 18:50 pg_xact
-rw-------. 1 postgres postgres   341 Aug  3 01:53 postgresql.auto.conf
-rw-------. 1 postgres postgres    88 Aug  3 01:41 postgresql.auto.conf.bkp
-rw-------. 1 postgres postgres  1292 Aug  3 01:41 postgresql.conf
-rw-------. 1 postgres postgres 30890 Aug  3 01:41 postgresql.conf.bkp
-rw-------. 1 postgres postgres    27 Aug  3 14:59 postmaster.opts
-rw-------. 1 postgres postgres    89 Aug  3 15:02 postmaster.pid
[postgres@pgdb01 data]$
[postgres@pgdb01 data]$ pg_rewind -D /pgData/pgsql17/data --source-server="host=192.168.2.22 port=5432 user=postgres dbname=postgres" -P
pg_rewind: connected to server
pg_rewind: executing "/usr/pgsql-17/bin/postgres" for target server to complete crash recovery
2026-08-03 16:54:30.801 +08 [3576] DEBUG:  mmap(150994944) with MAP_HUGETLB failed, huge pages disabled: Cannot allocate memory
2026-08-03 16:54:30.802 +08 [3576] DEBUG:  cleaning up orphaned dynamic shared memory with ID 130321386 (reference count 2)
2026-08-03 16:54:30.802 +08 [3576] DEBUG:  cleaning up dynamic shared memory control segment with ID 1080165630
2026-08-03 16:54:30.822 +08 [3576] DEBUG:  dynamic shared memory system will support 649 segments
2026-08-03 16:54:30.823 +08 [3576] DEBUG:  created dynamic shared memory control segment 3845242700 (25976 bytes)
2026-08-03 16:54:30.824 +08 [3576] LOG:  database system was interrupted; last known up at 2026-08-03 15:18:14 +08
2026-08-03 16:54:30.824 +08 [3576] DEBUG:  removing all temporary WAL segments
2026-08-03 16:54:30.824 +08 [3576] DEBUG:  checkpoint record is at B/DAD24770
2026-08-03 16:54:30.824 +08 [3576] DEBUG:  redo record is at B/DAD24718; shutdown false
2026-08-03 16:54:30.824 +08 [3576] DEBUG:  next transaction ID: 880; next OID: 163853
2026-08-03 16:54:30.824 +08 [3576] DEBUG:  next MultiXactId: 1; next MultiXactOffset: 0
2026-08-03 16:54:30.824 +08 [3576] DEBUG:  oldest unfrozen transaction ID: 730, in database 1
2026-08-03 16:54:30.824 +08 [3576] DEBUG:  oldest MultiXactId: 1, in database 1
2026-08-03 16:54:30.824 +08 [3576] DEBUG:  commit timestamp Xid oldest/newest: 0/0
2026-08-03 16:54:30.824 +08 [3576] LOG:  database system was not properly shut down; automatic recovery in progress
2026-08-03 16:54:30.824 +08 [3576] DEBUG:  transaction ID wrap limit is 2147484377, limited by database with OID 1
2026-08-03 16:54:30.824 +08 [3576] DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 1
2026-08-03 16:54:30.824 +08 [3576] DEBUG:  starting up replication slots
2026-08-03 16:54:30.825 +08 [3576] DEBUG:  restoring replication slot from "pg_replslot/pgdb03_slot/state"
2026-08-03 16:54:30.826 +08 [3576] DEBUG:  restoring replication slot from "pg_replslot/pgdb02_slot/state"
2026-08-03 16:54:30.827 +08 [3576] DEBUG:  xmin required by slots: data 0, catalog 0
2026-08-03 16:54:30.827 +08 [3576] DEBUG:  starting up replication origin progress state
2026-08-03 16:54:30.828 +08 [3576] DEBUG:  didn't need to unlink permanent stats file "pg_stat/pgstat.stat" - didn't exist
2026-08-03 16:54:30.828 +08 [3576] DEBUG:  resetting unlogged relations: cleanup 1 init 0
2026-08-03 16:54:30.829 +08 [3576] LOG:  redo starts at B/DAD24718
2026-08-03 16:54:30.829 +08 [3576] DEBUG:  waiting for all backends to process ProcSignalBarrier generation 1
..
..
PostgreSQL stand-alone backend 17.10
backend> 2026-08-03 16:54:30.848 +08 [3576] NOTICE:  shutting down
2026-08-03 16:54:30.851 +08 [3576] LOG:  checkpoint starting: shutdown immediate
2026-08-03 16:54:30.851 +08 [3576] DEBUG:  performing replication slot checkpoint
2026-08-03 16:54:30.853 +08 [3576] DEBUG:  attempting to remove WAL segments older than log file 000000000000000B000000CA
2026-08-03 16:54:30.854 +08 [3576] DEBUG:  recycled write-ahead log file "000000130000000B000000CA"
2026-08-03 16:54:30.855 +08 [3576] DEBUG:  SlruScanDirectory invoking callback on pg_subtrans/0000
2026-08-03 16:54:30.855 +08 [3576] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.001 s, sync=0.001 s, total=0.004 s; sync files=0, longest=0.000 s, average=0.000 s; distance=2922 kB, estimate=2922 kB; lsn=B/DB000028, redo lsn=B/DB000028
2026-08-03 16:54:30.855 +08 [3576] DEBUG:  writing stats file "pg_stat/pgstat.stat"
2026-08-03 16:54:30.855 +08 [3576] DEBUG:  cleaning up orphaned dynamic shared memory with ID 3097709902
2026-08-03 16:54:30.856 +08 [3576] DEBUG:  cleaning up dynamic shared memory control segment with ID 3845242700
2026-08-03 16:54:30.860 +08 [3576] NOTICE:  database system is shut down
pg_rewind: servers diverged at WAL location B/DAD25488 on timeline 20
pg_rewind: rewinding from last common checkpoint at B/DAD24770 on timeline 20
pg_rewind: reading source file list
pg_rewind: reading target file list
pg_rewind: reading WAL in target
pg_rewind: need to copy 8228 MB (total source directory size is 8248 MB)
8426318/8426318 kB (100%) copied
pg_rewind: creating backup label and updating control file
pg_rewind: syncing target data directory
pg_rewind: Done!
[postgres@pgdb01 data]$

11. Configure pgdb01 as a Standby

[postgres@pgdb01 ~]$ touch /pgData/pgsql17/data/standby.signal

[postgres@pgdb01 data]$ ls -l /pgData/pgsql17/data/standby.signal
-rw-r--r--. 1 postgres postgres 0 Aug  3 16:56 /pgData/pgsql17/data/standby.signal
[postgres@pgdb01 data]$

12. Configure pgdb01 to Follow the New Primary (pgdb02)

On New Primary (pgdb02):

[postgres@pgdb02 ~]$ psql -c "SELECT slot_name, active, wal_status, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal FROM pg_replication_slots;"
  slot_name  | active | wal_status | retained_wal
-------------+--------+------------+--------------
 pgdb03_slot | t      | reserved   | 0 bytes
 pgdb01_slot | f      | extended   | 5776 MB    <----- Retained ALL WAL FILES FOR OLD PRIMARY PGDB01
(2 rows)

[postgres@pgdb02 ~]$

on pgdb01:

in postgresql.auto.conf


from :
primary_slot_name = 'pgdb02_slot'
primary_conninfo = 'host=192.168.2.21 port=5432 user=repuser application_name=pgdb02'

To:
primary_slot_name = 'pgdb01_slot'
primary_conninfo = 'host=192.168.2.22 port=5432 user=repuser application_name=pgdb01'

13. Start pgdb01

[postgres@pgdb01 data]$ pg_ctl start -D /pgData/pgsql17/data -w -t 300    <------ pg_ctl start with timeout 300 seconds, by default 60 seconds 
waiting for server to start....2026-08-03 17:03:07.722 +08 [3619] DEBUG:  registering background worker "logical replication launcher"
2026-08-03 17:03:07.723 +08 [3619] DEBUG:  mmap(150994944) with MAP_HUGETLB failed, huge pages disabled: Cannot allocate memory
2026-08-03 17:03:07.746 +08 [3619] DEBUG:  dynamic shared memory system will support 649 segments
2026-08-03 17:03:07.747 +08 [3619] DEBUG:  created dynamic shared memory control segment 1150144454 (25976 bytes)
2026-08-03 17:03:07.750 +08 [3619] DEBUG:  max_safe_fds = 986, usable_fds = 1000, already_open = 4
2026-08-03 17:03:07.752 +08 [3619] LOG:  redirecting log output to logging collector process
2026-08-03 17:03:07.752 +08 [3619] HINT:  Future log output will appear in directory "log".
........................................................................................................ done
server started
[postgres@pgdb01 data]$

logfile:

2026-08-03 17:03:07.752 +08 [3619] LOG:  starting PostgreSQL 17.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
..
..
2026-08-03 17:03:07.774 +08 [3621] DEBUG:  checkpointer updated shared memory configuration values
2026-08-03 17:03:07.775 +08 [3623] DEBUG:  removing all temporary WAL segments
2026-08-03 17:03:08.764 +08 [3623] DEBUG:  backup time 2026-08-03 16:55:53 +08 in file "backup_label"
2026-08-03 17:03:08.764 +08 [3623] LOG:  starting backup recovery with redo LSN B/DAD24718, checkpoint LSN B/DAD24770, on timeline ID 20
2026-08-03 17:03:08.766 +08 [3623] DEBUG:  checkpoint record is at B/DAD24770
2026-08-03 17:03:08.766 +08 [3623] LOG:  entering standby mode
2026-08-03 17:03:08.766 +08 [3623] DEBUG:  redo record is at B/DAD24718; shutdown false
..
..
2026-08-03 17:03:08.766 +08 [3623] DEBUG:  starting up replication slots
2026-08-03 17:03:08.767 +08 [3623] DEBUG:  xmin required by slots: data 0, catalog 0
2026-08-03 17:03:08.767 +08 [3623] DEBUG:  starting up replication origin progress state
2026-08-03 17:03:08.767 +08 [3623] DEBUG:  didn't need to unlink permanent stats file "pg_stat/pgstat.stat" - didn't exist
2026-08-03 17:03:08.772 +08 [3623] DEBUG:  resetting unlogged relations: cleanup 1 init 0
2026-08-03 17:03:08.772 +08 [3623] DEBUG:  initializing for hot standby
2026-08-03 17:03:08.772 +08 [3623] LOG:  redo starts at B/DAD24718
2026-08-03 17:03:08.773 +08 [3623] DEBUG:  recovery snapshots are now enabled
..
..
2026-08-03 17:04:52.283 +08 [3621] LOG:  recovery restart point at D/3A0618D0
2026-08-03 17:04:52.283 +08 [3621] DETAIL:  Last completed transaction was at log time 2026-08-03 16:03:01.13535+08.
2026-08-03 17:04:52.283 +08 [3621] LOG:  restartpoint starting: wal
2026-08-03 17:04:52.283 +08 [3621] DEBUG:  performing replication slot checkpoint
2026-08-03 17:04:52.688 +08 [3623] DEBUG:  end of backup reached
2026-08-03 17:04:52.693 +08 [3623] LOG:  completed backup recovery with redo LSN B/DAD24718 and end LSN D/43D1ADA0
2026-08-03 17:04:52.693 +08 [3623] LOG:  consistent recovery state reached at D/43D1ADA0
2026-08-03 17:04:52.693 +08 [3619] DEBUG:  postmaster received pmsignal signal
2026-08-03 17:04:52.693 +08 [3619] LOG:  database system is ready to accept read-only connections
2026-08-03 17:04:52.694 +08 [3623] LOG:  invalid record length at D/43D1ADA0: expected at least 24, got 0  <--- End of WAL, 24 bytes (minimum WAL record header) but found 0 bytes.
2026-08-03 17:04:52.694 +08 [3623] DEBUG:  switched WAL source from archive to stream after failure
2026-08-03 17:04:52.696 +08 [3619] DEBUG:  postmaster received pmsignal signal
2026-08-03 17:04:52.815 +08 [3625] LOG:  started streaming WAL from primary at D/43000000 on timeline 21
2026-08-03 17:04:52.815 +08 [3625] DEBUG:  sending write D/43D1ADA0 flush D/43D1ADA0 apply D/43D1ADA0
2026-08-03 17:04:52.815 +08 [3625] DEBUG:  sending hot standby feedback xmin 0 epoch 0 catalog_xmin 0 catalog_xmin_epoch 0
2026-08-03 17:04:52.821 +08 [3625] DEBUG:  sendtime 2026-08-03 17:04:52.816301+08 receipttime 2026-08-03 17:04:52.821075+08 replication apply delay (N/A) transfer latency 5 ms

14. Verify Streaming Replication and Data sanity

on pgdb01:

[postgres@pgdb01 ~]$ psql -c "select count(*) from demo_wal_test;"
  count
----------
 20000000
(1 row)

[postgres@pgdb01 ~]$

on pgdb02:

[postgres@pgdb02 ~]$ psql -c "SELECT application_name, client_addr, state, sync_state, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replica_lag FROM pg_stat_replication;"
 application_name | client_addr  |   state   | sync_state | replica_lag
------------------+--------------+-----------+------------+-------------
 pgdb03           | 192.168.3.20 | streaming | async      | 0 bytes
 pgdb01           | 192.168.2.21 | streaming | async      | 0 bytes
(2 rows)

[postgres@pgdb02 ~]$ psql -x -c "SELECT * FROM pg_stat_replication;"
-[ RECORD 1 ]----+------------------------------
pid              | 3721
usesysid         | 16384
usename          | repuser
application_name | pgdb03
client_addr      | 192.168.3.20
client_hostname  |
client_port      | 36568
backend_start    | 2026-08-03 15:50:43.560514+08
backend_xmin     |
state            | streaming
sent_lsn         | D/43D1ADA0
write_lsn        | D/43D1ADA0
flush_lsn        | D/43D1ADA0
replay_lsn       | D/43D1ADA0
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2026-08-03 17:32:09.52501+08
-[ RECORD 2 ]----+------------------------------
pid              | 4235
usesysid         | 16384
usename          | repuser
application_name | pgdb01
client_addr      | 192.168.2.21
client_hostname  |
client_port      | 43214
backend_start    | 2026-08-03 17:04:52.791918+08
backend_xmin     |
state            | streaming
sent_lsn         | D/43D1ADA0
write_lsn        | D/43D1ADA0
flush_lsn        | D/43D1ADA0
replay_lsn       | D/43D1ADA0
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2026-08-03 17:32:13.587316+08

[postgres@pgdb02 ~]$

15. Verify Replication Slot Status on pgdb02

[postgres@pgdb02 ~]$ psql -c "SELECT slot_name, active, wal_status, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal FROM pg_replication_slots;"
  slot_name  | active | wal_status | retained_wal
-------------+--------+------------+--------------
 pgdb03_slot | t      | reserved   | 0 bytes
 pgdb01_slot | t      | reserved   | 0 bytes
(2 rows)

[postgres@pgdb02 ~]$
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you
Rajasekhar Amudala
Email: br8dba@gmail.com
LinkedIn: linkedin.com/in/rajasekhar-amudala