PostgreSQL Cluster: Graceful Switchover

Requirement

1. Database Status

   1. Primary cluster should be “ONLINE“ and standby cluster should be “HOT STANDBY“/”WARM STANDBY” 

1. Trigger Graceful Switchover

2. Disable Control Center Behaviors

   • Disable Observer: Turn off the observer to ensure a smooth switchover without conflicting activities

   • Disable Auto Update: Suspend any automatic update processes during the switchover procedure

3. Prepare for Sync Between Primary and Standby 1

   • If the backup mode is set to "Archive," generate a new WAL file as a reference point for synchronization checks

     SELECT * from pg_switch_wal()

   • Otherwise skip

4. Stop Original Primary Cluster

5. Prepare for Sync Between Primary and Standby 2

   • If the backup mode is "Archive," skip this step as it's already done in step 3

   • If the backup mode is "Log Shipping," transmit all unapplied WAL files to the standby server via dbvnet

     start_lsn:
     can be retrieved from backupInfo
     
     end_lsn:
     pg_controldata -D dataDir
     
     Any wal file with LSN: LSN > start_lsn && LSN <= end_lsn 

   • If the backup mode is "Streaming," there's no need to sync as the WAL sync is accomplished before the primary cluster shutdown

6. Update Original Primary PostgreSQL Configuration

   • Adjust the original primary PostgreSQL configuration based on the backup mode. Convert its role to become the new standby database.

7. Sync Between Primary and Standby for Switchover 1

   • If the backup mode is "Archive," wait for the WAL file generated in step 3 to be applied in the original standby database.

   • Otherwise skip

8. Sync Between Primary and Standby for Switchover 2

   • If the backup mode is "Log Shipping," apply the WAL file transferred in step 5 to the original standby database.

   • Otherwise skip

9. Stop Original Standby Cluster

10. Update Original Standby PostgreSQL Configuration

    • Modify the original standby PostgreSQL configuration according to the backup mode, converting its role to the new primary database

11. Switch Role and Persist New Configuration Data

12. Start New Primary Cluster

13. Refresh Replication Slot on Primary Cluster (if Streaming Backup)

SELECT * FROM pg_drop_replication_slot($1) WHERE EXISTS (SELECT * FROM pg_replication_slots WHERE slot_name = SLOT_NAME)
SELECT * FROM pg_create_physical_replication_slot(SLOT_NAME, false, false)

14. Start New Standby Cluster

15. Prepare for Sync validation Between Primary and Standby

    • If the backup mode is "Archive," create a new WAL file as a reference for synchronization checks.

    • If the backup mode is "Log Shipping," generate a new WAL file and transfer it to the new standby cluster.

    • If the backup mode is "Streaming," verify that the current LSN on the new primary is applied in the new standby database.

16. Sync Between Primary and Standby for Validation 1

    • If the backup mode is "Archive," wait for the WAL file generated in step 15 to be applied in the new standby database.

    • Otherwise skip

17. Sync Between Primary and Standby for Validation 2

    • If the backup mode is "Log Shipping," apply the WAL file transferred in step 15 to the new standby database.

    • Otherwise skip

18. Enable Control Center Behaviors

    • Enable Observer

    • Enable Auto Update: Restore the automatic update processes

19. Execute User Script on New Primary and Standby Servers

    • Run any necessary user scripts on the new primary server and new standby server to complete the switchover process.