Data Synchronization Between DC and DR Using pg_rewind – Part 1

By /

Introduction:

We recently encountered a scenario with one of our customers during a PostgreSQL DR drill. As part of the drill, the standby server was promoted to the new primary, and several transactions, including inserts, updates, and deletes, were performed on it. The DR environment remained active as the primary for approximately 48 hours, during which around 300 GB of data changes were generated. After completing the DR activity, the customer wanted to bring the old primary back into the replication setup while ensuring that all the changes made on the new primary were retained.

The key challenge was how to resynchronize the old primary without performing a complete pg_basebackup. The overall database size was approximately 3 TB, so rebuilding the entire standby by transferring the complete database would require significant time and network bandwidth. Since only a portion of the database had changed during the DR period, performing a full rebuild would not be an efficient approach for this scenario.

This is where PostgreSQL’s pg_rewind becomes useful. pg_rewind can identify the point where the old and new primary servers having the same copy of the cluster diverged, and rewind the old primary to the last common point, allowing it to be resynchronized with the new primary without rebuilding the entire database from scratch. The actual time and network requirements depend on the amount of divergence and the available network bandwidth. In this blog, we will walk through this scenario and demonstrate how pg_rewind can be used to bring the old primary back into the replication setup efficiently.

In this blog, we will demonstrate the pg_rewind process using a controlled UAT environment with a small test dataset. The scenario is designed to simulate a DR promotion and subsequent resynchronization of the former primary. The test environment and data shown in this blog are for demonstration purposes only and do not represent any Production or customer-specific configuration.

pg_rewind Prerequisites:

Before performing the pg_rewind operation, ensure that both the DC and DR servers have wal_log_hints enabled.

postgres=# show wal_log_hints ;
 wal_log_hints
---------------
 on
(1 row)

wal_log_hints must be enabled before the divergence occurs for pg_rewind to work, unless data checksums were enabled when the PostgreSQL cluster was initialized.

Before initiating the pg_rewind, ensure that the pg_hba.conf file on both instances allows the other instance to connect :

cat /var/lib/pgsql/15/data/pg_hba.conf

host all all /32 scram-sha-256
host all all /32 scram-sha-256

Check the .pgpass entry in DR & DC

vi .pgpass

Expected result:

localhost:5432:postgres:postgres:

If the .pgpass file does not exist, in the postgres user’s home directory. Create a file with that name and add the text as displayed above.

Export the path

export PGPASS=/var/lib/pgsql/.pgpass


Stop postgresql.services on DC.

/usr/pgsql-15/bin/pg_ctl -D /var/lib/pgsql/15/data stop
waiting for server to shut down..... done
server stopped

Expected result: server should not be running.

Check the DR server status it should be running

/usr/pgsql-15/bin/pg_ctl -D /var/lib/pgsql/15/data status
pg_ctl: server is running (PID: 30828)

 Check the replication lag in DR

SELECT pg_size_pretty(pg_last_wal_receive_lsn() - pg_last_wal_replay_lsn()) AS replay_lag_bytes;
 replay_lag_bytes
------------------
 0 bytes
(1 row)

Check if the standby is in recovery mode:

select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

Promote the standby as new primary :


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

Verify:

postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

Create a replication slot in DR:

postgres=# SELECT pg_create_physical_replication_slot('rewind_slot', true);
 pg_create_physical_replication_slot
-------------------------------------
 (rewind_slot,0/7284FD0)
(1 row)

Must be able to see the slot.server

Run any transactions on the new primary

postgres=# CREATE TABLE rewind_test (id serial primary key, note text, ts timestamptz default now());
INSERT INTO rewind_test (note) VALUES ('row1'), ('row2'), ('row3');
UPDATE rewind_test SET note = 'row1-updated' WHERE id = 1;
DELETE FROM rewind_test WHERE id = 3;
CREATE TABLE
INSERT 0 3
UPDATE 1
DELETE 1
postgres=# create table test (id int, name varchar(200));
CREATE TABLE
postgres=# INSERT INTO test (id, name)
SELECT id, 'Name_' || id
FROM generate_series(1, 100) AS id;
INSERT 0 100
postgres=# CREATE TABLE bottle (
    id INT,
    name VARCHAR(200)
);
INSERT INTO bottle (id, name)
SELECT id, 'Bottle_' || id
FROM generate_series(1, 100) AS id;
CREATE TABLE
INSERT 0 100
postgres=# CREATE TABLE earphone (
    id INT,
    name VARCHAR(200)
);
INSERT INTO earphone (id, name)
SELECT id, 'Earphone_' || id
FROM generate_series(1, 100) AS id;
CREATE TABLE
INSERT 0 100

Note the new timeline ID for reference:

postgres=# SELECT timeline_id FROM pg_control_checkpoint();
 timeline_id
-------------
           2
(1 row)

Start the old primary :

/usr/pgsql-15/bin/pg_ctl -D /var/lib/pgsql/15/data start


Drop the replication slot in DC 

postgres=# select pg_drop_replication_slot('test_ust_standby1');
 pg_drop_replication_slot
--------------------------

(1 row)

Stop the old primary :

/usr/pgsql-15/bin/pg_ctl -D /var/lib/pgsql/15/data stop

Check the pg_rewind with a dry run in DC:

[postgres@node1 ~]$ /usr/pgsql-15/bin/pg_rewind \
  --target-pgdata=/var/lib/pgsql/15/data \
  --source-server="host=192.168.21.144 port=5433 user=postgres dbname=postgres" \
  -R -P --dry-run
pg_rewind: connected to server
pg_rewind: servers diverged at WAL location 0/320000A0 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/32000028 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 180 MB (total source directory size is 801 MB)
184399/184399 kB (100%) copied
pg_rewind: creating backup label and updating control file
pg_rewind: syncing target data directory
pg_rewind: Done!

Run the pg_rewind in DC if the above step is successful:

/usr/pgsql-15/bin/pg_rewind   --target-pgdata=/var/lib/pgsql/15/data   --source-server="host=192.168.21.144 port=5433 user=postgres dbname=post
gres"   -R -P
pg_rewind: connected to server
pg_rewind: servers diverged at WAL location 0/320000A0 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/32000028 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 180 MB (total source directory size is 801 MB)
184399/184399 kB (100%) copied
pg_rewind: creating backup label and updating control file
pg_rewind: syncing target data directory
pg_rewind: Done!

At this point, the old primary’s data directory has been rewound and prepared to follow the new primary.

Verify standby.signal and Replication Configuration

Verify:

ls -l /var/lib/pgsql/15/data/standby.signal
-rw-------. 1 postgres postgres 0 Sep 2 15:39 standby.signal

Start the Rewound Server on the DC

systemctl start postgresql-15
systemctl status postgresql-15
● postgresql-15.service - PostgreSQL 15 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; preset: disabled)
Active: active (running) since Wed 2026-09-02 15:53:51 IST; 4s ago
Docs: https://www.postgresql.org/docs/15/static/
Process: 532639 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 532644 (postmaster)
Tasks: 5 (limit: 25902)
Memory: 16.6M (peak: 17.9M)
CPU: 68ms
CGroup: /system.slice/postgresql-15.service
├─532644 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
├─532645 "postgres: logger "
├─532646 "postgres: checkpointer "
├─532647 "postgres: background writer "
└─532648 "postgres: startup recovering 000000010000000000000007"

Sep 02 15:53:50 node1 systemd[1]: Starting PostgreSQL 15 database server...
Sep 02 15:53:50 node1 postmaster[532644]: 2026-09-02 15:53:50.818 IST [532644] LOG: redirecting log output to logging coll>
Sep 02 15:53:50 node1 postmaster[532644]: 2026-09-02 15:53:50.818 IST [532644] HINT: Future log output will appear in dire>
Sep 02 15:53:51 node1 systemd[1]: Started PostgreSQL 15 database server.

During startup, PostgreSQL will recover using the new timeline and begin following DR.

Verify DC Is Now a Standby

postgres=# SELECT pg_is_in_recovery();
pg_is_in_recovery

t
(1 row)

This confirms that DC is now running as a standby.

Verify the New Timeline

Verify the timeline on the standby matches the new primary’s timeline 

postgres=# SELECT timeline_id FROM pg_control_checkpoint();
timeline_id
       2
(1 row)

Verify Replication Lag in DC (standby)

postgres=# SELECT
pg_is_in_recovery(),
pg_last_wal_receive_lsn(),
pg_last_wal_replay_lsn(),
pg_size_pretty(pg_wal_lsn_diff(
pg_last_wal_receive_lsn(),
pg_last_wal_replay_lsn()
)) AS lag;
pg_is_in_recovery | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn | lag
-------------------+-------------------------+------------------------+---------
t | 0/72FE960 | 0/72FE960 | 0 bytes
(1 row)

A zero or acceptable lag indicates that DC has caught up with DR.

Validate the Data

Finally, verify that the data created on DR is available on DC.

postgres=# SELECT * FROM test LIMIT 5;
id | name
----+--------
1 | Name_1
2 | Name_2
3 | Name_3
4 | Name_4
5 | Name_5
(5 rows)

postgres=# SELECT * FROM bottle LIMIT 5;
id | name
----+----------
1 | Bottle_1
2 | Bottle_2
3 | Bottle_3
4 | Bottle_4
5 | Bottle_5
(5 rows)

postgres=# SELECT * FROM earphone LIMIT 5;
id | name
----+------------
1 | Earphone_1
2 | Earphone_2
3 | Earphone_3
4 | Earphone_4
5 | Earphone_5
(5 rows)

Conclusion:

In Part 1, we demonstrated how pg_rewind can be used after a PostgreSQL DR promotion to resynchronize the old primary without rebuilding the entire database. We covered the DR promotion, timeline divergence, pg_rewind prerequisites, dry run, rewind operation, and finally bringing the old primary back as a standby.

In Part 2, we will go deeper into the internals of pg_rewind and understand what PostgreSQL does behind the scenes, from identifying the last common point and processing WAL to rewinding the target and bringing the old primary back into streaming replication with the current primary.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top