PostgreSQL Cluster: Graceful Switchover

Owned by Vijayganesh Sivaprakasam
Last updated: Sept 01, 2024
4 min read

 

1. Introduction

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. Even though this process involves a little downtime, but is seamless and does not require any manual intervention.

Dbvisit agentmanager or controlcenter does not manage automatic startup of PostgreSQL cluster after server reboots. The services must be managed either by system startup scripts or can be done manually from the control center or directly from the server itself.

2. Technical Explanation

  1. Database Status

    1. The primary cluster should be “ONLINE“ and the standby cluster should be “HOT STANDBY“/”WARM STANDBY.” 

3. Dbvisit Switchover

3.1 Switchover with WAL Streaming Mode

The first and foremost step is to ensure that the status of the configuration is In Sync before proceeding with the switchover. It is also important that the application/users are not making any changes to the production cluster.

 

 

  1. The streaming mode configuration is In Sync.

  2. The message indicates that the configuration is ready for switchover.

  3. User scripts that can be executed post the switchover process on new primary and standby.

  4. Click Start to start the switchover process.

  1. The time taken for the switchover process to complete.

  2. The steps for the switchover process.

 

  1. The configuration is In Sync with WAL Streaming method on the reverse direction.

  2. The observer is enabled once the switchover is completed and is now monitored.

  3. The new primary server and Cluster status show ONLINE on port 5435.

  4. The Streaming method is used by the cluster to sync between standby and primary.

  5. The new standby server and cluster status showing HOT STANDBY on port 5435

 

3.2 Switchover with the WAL Archive method.

The first and foremost step is to ensure that the status of the configuration is In Sync before proceeding with the switchover. It is also important that the application/users are not making any changes to the production cluster.

  1. The Configuration name and the status of the configuration showing In Sync.

  2. The method used for the sync process. The archive method is used here.

  3. Click on the Graceful Switchover to start the process.

  1. The archiving mode configuration is In Sync.

  2. The message indicates that the configuration is ready for switchover.

  3. User scripts that can be executed post the switchover process on new primary and standby.

  4. Click Start to start the switchover process.

 

  1. The configuration is In Sync with the WAL Archiving method in the reverse direction.

  2. The observer is enabled once the switchover is completed and is now monitored.

  3. The new primary server and Cluster status show ONLINE on port 5432.

  4. The Streaming method is used by the cluster to sync between standby and primary.

  5. The new standby server and cluster status showing HOT STANDBY on port 5432

 

3.3 Switchover with the WAL Shipping method.

  1. The Configuration name and the status of the configuration show time stamp difference between standby and primary.

  2. The method used for the sync process. The shipping method is used here.

  3. Click on the Graceful Switchover to start the process.

  1. The shipping mode configuration and the time difference between standby and primary

  2. The message indicates that the configuration is ready for switchover.

  3. User scripts that can be executed post the switchover process on new primary and standby.

  4. Click Start to start the switchover process.

 

 

  1. The configuration is In Sync with the WAL Shipping method in the reverse direction.

  2. The observer is enabled once the switchover is completed and is now monitored.

  3. The new primary server and Cluster status show ONLINE on port 5433.

  4. The WAL Shipping method is used by the cluster to sync between standby and primary.

  5. The new standby server and cluster status showing HOT STANDBY on port 5433.