PostgreSQL Graceful Switchover
- Jan Klinke
The graceful switchover is a zero data loss process for role reversal between the primary and standby clusters. At the end of this process, the standby and primary clusters change roles, and the standby cluster becomes the new primary, and the primary cluster becomes the new standby. This process involves a little downtime, but is seamless and does not require any manual intervention.
1. Concept
Graceful Switchover (GS) concept is the same for Streaming or WAL Archiving replication and includes following steps:
A. Disable Observer
Observer is disabled for the whoel configuration to prevent any notification for primary or standby cluster being down
B. Stop primary cluster
Primary cluster is stopped with pg_ctl stop
C. Final sync
Sync original primary and original standby before GS
D. Change role on Primary
Setup postgresql configuration to convert database role from primary to standby (together with standby.signal file).
E. Apply WAL on standby
Apply WAL records on standby cluster
F. Stop standby cluster
Standby cluster is stopped with pg_ctl stop
G. Change role on Standby
Setup postgresql configuration to convert database role from standby to primary (also remove standby.signal file).
H. Start primary and standby cluster
Use pg_ctl start to start primary and standby clusters
I. Verify sync
dbvagentmanagers will verify that both databases are in sync
J. Enable observer
Enable back the observer
K. Execute user script
If custom user scripts were selected, they will be executed at this moment.
2. Starting Graceful Switchover
Graceful Switchover (GS) is executed in the same way for WAL Streaming and WAL Archiving configurations. Before starting GS, it is important that PostgreSQL configuration shows status “In Sync” - this is the same for WAL Streaming and WAL Archiving:
To initiate GS, select Graceful Switchover from right ACTIONS pane:
The following form will perform prechecks and let you know if the configuration is ready for GS (1):
You will be also able to select any user scripts you would like to execute after switchover (2). For more details on how to setup User scripts, see: User Scripts .
Click on Start (3) to initiate the GS.
Once the GS start, you will be able to see the task and view its detailed information:
Once the task completes, the switchover is finished and there are no further post-steps involved. Status in dashboard will be seen with swapped server roles:
PostrgeSQL instances are restarted with pg_ctl during switchover, so any status bound to systemctl services is lost. You will need to restart the postgreSQL instaces by systemctl if you wish to correct the status for systemctl service.