In part-1 of the blog series, we demonstrated how PostgreSQL pg_rewind can be used to resynchronize an old primary (dc01) server after promoting a Standby to Primary during a Disaster Recovery (DR) drill, without rebuilding the entire database using pg_basebackup. During the DR drill, the original primary, DC01, was stopped and the standby, DR01, was promoted as the new primary. Transactions and test data were then generated on DR01, causing the WAL history of DC01 and DR01 to diverge and placing DR01 on a new timeline.
Once the DR activity was completed, the requirement was to restore DC01 back to its Primary status while retaining the changes made on DR01. After promoting DC01 back as Primary, instead of transferring the complete database again, pg_rewind was used to identify the point where the two servers last shared a common WAL history and rewind DR01 to that point. After the rewind, DR01 was started as a standby and began following the new primary timeline, receiving and replaying WAL from DC01 until it reached the same database state.
This process also highlights several important PostgreSQL concepts, including WAL, LSNs, timeline changes after promotion, wal_log_hints, replication slots, and WAL retention. Understanding these components is essential for successfully using pg_rewind, particularly when the required WAL is still available and the environment meets the necessary prerequisites. In this series, we will explore how PostgreSQL handles WAL divergence after a DR promotion, how pg_rewind identifies and resolves the divergence, and why it can be a more efficient alternative to a complete pg_basebackup when only a portion of the database has changed.
Promoting the Original Primary Back as the Primary
Before promoting the original primary, DC01, back to primary, we verified the current timeline:
mock=# SELECT timeline_id from pg_control_checkpoint();
timeline_id
-------------
2
(1 row)At the end of the DR drill, we stopped the current primary, DR01, and promoted the original primary, DC01, back to primary.
postgres=# select pg_promote();
pg_promote
------------
t
(1 row)After the promotion, we verified that the standby was no longer running in recovery mode
postgres=# SELECT pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)We then verified the timeline on the original primary (DC01):
mock=# SELECT timeline_id from pg_control_checkpoint();
timeline_id
-------------
3
(1 row)At this point, DC01 had been promoted back to primary and was running on the new timeline.
We then used pg_rewind to resynchronize the former primary, DR01, with the newly promoted primary, DC01. The purpose was to bring DR01 back into the replication environment without rebuilding the entire database using pg_basebackup.
[postgres@node2 pg_wal]$ /usr/pgsql-15/bin/pg_rewind -D /var/lib/pgsql/15/data --source-server="host=192.168.21.149 port=5432 user=postgres dbname=postgres password=osdbcortex123@" -R -P
pg_rewind: connected to server
pg_rewind: servers diverged at WAL location 0/D000168 on timeline 3
pg_rewind: rewinding from last common checkpoint at 0/D0000B8 on timeline 3
pg_rewind: reading source file list
pg_rewind: reading target file list
pg_rewind: reading WAL in target
pg_rewind: need to copy 61 MB (total source directory size is 93 MB)
62860/62860 kB (100%) copied
pg_rewind: creating backup label and updating control file
pg_rewind: syncing target data directory
pg_rewind: Done!The output shows two important pieces of information: the WAL location where the servers diverged and the last common checkpoint from which the target server was rewound. In this test, pg_rewind needed to copy only approximately 61 MB, while the total source data directory was approximately 93 MB. This demonstrates why pg_rewind can be significantly more efficient than rebuilding the entire standby when the amount of divergence is relatively small.
After completing the rewind, we verified the timeline again in the DR01:
postgres=# select timeline_id from pg_control_checkpoint();
timeline_id
-------------
3
(1 row)The rewound server can now start in recovery and follow the current primary. It receives WAL from the primary through the WAL sender/WAL receiver process. It replays the WAL through the startup/recovery process until it reaches the same database state as the current primary.
How Does pg_rewind Handle WAL Divergence?
When the standby in DR01 is promoted, PostgreSQL creates a new timeline.
For example:
- Before the DR drill, both DC01 and DR01 may be following Timeline 1.
- DR01 is promoted and becomes the new primary.
- PostgreSQL creates Timeline 2 on DR01.
- New WAL generated after the promotion is written to Timeline 2.
- The old primary in DC01 still belongs to the previous timeline.
- At this point, the two servers have divergent WAL histories.
This is where pg_rewind becomes extremely useful.
pg_rewind identifies the point at which the old primary and the new primary diverged. It then rewinds the old primary to the common point in their WAL history and removes or replaces the data changes that occurred after that divergence point.
The old primary is therefore brought back to a state from which it can follow the current primary’s new timeline.
What Happens to the Last LSN?
Before the promotion, both servers have a common WAL history up to a particular LSN (Log Sequence Number).
For example:
Start
|
| Common WAL History
| DC01:Primary; DR01: Standby
| LSN: 1000
+-------| Timeline 1
|
| DR01 Promoted to Primary
| LSN: 1100
|
+---------------- Timeline 2
|
| DR01 (Primary) <--- DC01 (Standby)
| LSN: 1100
|
+----------------| Timeline 2
|
| DC01 Promoted
| (Back as Primary)
| LSN: 1200
|
+------------------ Timeline 3
|
| DC01 (Primary) ---> DR01 (Standby)
| LSN: 1200
|
+------------------ Timeline 3After DC01 is promoted, new WAL is generated on the new timeline.
The old primary in DR01 may contain WAL records that are not part of the current primary’s history. These records are no longer part of the desired primary timeline.
pg_rewind determines the common ancestor point and rewinds the old primary back to that point.
After the rewind, DR01 no longer continues from its old divergent WAL history. Instead, it can follow the new primary’s timeline.
What Happened During the DR Drill?
During the DR drill, the original primary was stopped, so WAL generation on the original primary also stopped. Replication between the primary and standby was stopped. The standby was then promoted to a new primary, and WAL generation started on the new primary.
Example
- Before the DR drill:
The primary and standby were in sync, and WAL was streaming from the primary to the standby. Both servers were following Timeline 1. - During the DR drill:
The original primary was stopped, WAL generation on the original primary stopped, and the standby was promoted to a new primary. After promotion, PostgreSQL created Timeline 2, and the new primary started generating WAL on Timeline 2. - After the DR Drill
Once the DR testing was completed, the original primary needed to be brought back into the replication environment. Instead of rebuilding DC01 usingpg_basebackup, we usedpg_rewindto identify the common WAL history and synchronize DC01 with the new primary.
How does PostgreSQL choose the correct WAL without missing WAL files?
When the DR drill occurred, the old primary was unavailable. PostgreSQL uses the WAL LSN (Log Sequence Number) and timeline information to identify the correct WAL history.
For example, a WAL segment such as:
000000010000000000000013contains information that identifies its timeline, log, and segment position. PostgreSQL uses the WAL segment name, along with the LSN and timeline history, to determine which WAL belongs to the current recovery history.
The important point is that PostgreSQL does not simply choose a WAL file based only on its filename. It checks the timeline history and WAL position to determine which WAL history the server should follow.
Why Are Timelines Required?
Timelines allow PostgreSQL to distinguish between different WAL histories.
Timeline 1
|
| Primary A generates WAL
|
+---- Standby B
|
|
v
Primary A fails
New Primary B
|
|
| New WAL generated
v
Timeline 2 WAL
Timeline 1 and Timeline 2 may contain WAL referring to the same database, but after the promotion point they represent different histories.
Timeline Mechanics
Timeline IDs are monotonically increasing integers:
- Timeline 1: Initial database history.
- Timeline 2: Created after the first promotion.
- Timeline 3: Created after the next promotion.
- Timeline 4: Created after another promotion.
Therefore, the timeline ID helps PostgreSQL determine which WAL history is valid for the server’s current recovery path.
Timeline History File(pg_wal/.history):
000000010000000000000026
000000010000000000000027.partial
00000002.history
000000020000000000000027
When the server is promoted, PostgreSQL creates a new timeline. The .history file records where the new timeline branched from the previous timeline.
The WAL segment is associated with Timeline 2 after the promotion because new WAL is now generated on Timeline 2.
The purpose is not simply to rename the WAL file to avoid a problem. The timeline ID in the WAL segment name tells PostgreSQL which WAL history the segment belongs to. The .history file tells PostgreSQL where Timeline 2 branched from Timeline 1, allowing recovery to follow the correct WAL history without confusion.
Conclusion :
In this part, we explored how PostgreSQL handles WAL divergence after a DR promotion and how pg_rewind can resynchronize the old primary with the newly promoted primary.By identifying the common WAL history and rewinding only the required changes, pg_rewind avoids the need for a complete pg_basebackup. This entire setup would work seamlessly as long as the replication setup is managed properly; i.e., Replication Slots and Subscriptions are dropped and re-created upon every switch/promotion.
Understanding WAL, LSNs, timelines, and WAL retention also becomes essential for using pg_rewind successfully in DR scenarios.