PostgreSQL Cascade Configuration

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

 

1. Introduction

This page will show us how to create a cascade configuration using PostgreSQL. This page will have the below topic covered.

  • Setting up PostgreSQL cascade configuration

  • Create a cascade PostgreSQL Cluster

  • Graceful Switchover

Below are some of the rules that must be followed when creating a cascade configuration.

  • The cascade configuration can be created and maintained for all replication modes (WAL Streaming, WAL Archiving, and WAL Shipping).

  • Switching from multiple standby clusters(A--> & A-->C) to cascade configurations is not allowed (A-->B-->C).

  • All cascade configurations must be HOT Standby. including the standby cluster database that is serving as primary for the cascade must be HOT Standby.

  • WAL Archiving mode replication can have cascade configuration (A-->B-->C is allowed) but cannot have multiple standby configurations from a single Primary ( A--> & A-->C is not allowed).

There is only one example shown below with Archiving mode configuration. Setting up cascade for other replication modes are similar.

2. Create Cascade Configuration

An existing setup of WAL Streaming configuration below is InSync status with the primary.

The primary hostname for this configuration is centlin71.oraclekiwi.co.nz, and the standby configuration is centlin72.oraclekiwi.co.nz. Click on New Configuration and choose Start with PostgreSQL.

  1. The source host must the standby host of the first configuration . In this case it is centlin72.oraclekiwi.co.nz. The status of the cluster of this host will be Hot Standby.

  2. The cluster information of the source host.

  3. The version of the PostgreSQL cluster.

  4. The standby host. This host cannot be same as original primary or the source host of the cascade configuration.

  5. The Port number for the cascade standby cluster. This can be same as standby or it is configurable to be a different port.

  6. The version of cascade standby PostgreSQL Cluster.

  7. The configuration name of the cascade standby cluster.

  8. License key for the cluster. This is completely different from the original pair.

  9. Click “Create Configuration” to create the configuration.

 

  1. Configuration name of the cascade standby cluster.

  2. The standby host of the first configuration (PG-5435-13) is the source host of the cascade configuration (PG-5435-13-CAS)

  3. Create a DR cluster by clicking on Set up now?

3. Create Cascade DR Cluster

The process is similar to creating a DR cluster. Ensure that the pg_hba.conf file has required entries for communication between the cascade configuration's source server and the cascade configuration's standby host.

  1. The configuration name of the cascade standby cluster.

  2. The source hosts data directory.

  3. the cascade standby hosts data directory.

  4. The important point is that we are creating the cascade configuration and the replication mode must be same as the source configuration.

  5. As per requirement, the cascade configuration must be created as Hot Standby.

  6. The method used for creating the cluster is WAL Streaming as the source configuration (PG-5435-13)

  7. The connection is successful in creating

  8. the replication for cascade configuration.

  9. Enable the observer to monitor the configuration.

  10. Click Start to start creating the DR cluster.

 

 

After successfully creating the DR cluster. The status of the original cluster ( 1 ) and the cascade standby cluster ( 2 ). Any transaction happening in the source host (centlin71.oraclekiwi.co.nz) will be first applied to the standby host (centlin72.oraclekiwi.co.nz), and then the changes are applied to the cascade standby cluster (nzel81.oraclekiwi.co.nz). The status of both the standby and the cascade standby are now Synced and it is In Sync status.

 

 

4. Graceful Switchover

The switchover process can only be run for the original configuration (PG-5432-13) and not for the cascaded configuration (PG-5435-13-CAS). Even after the switchover is completed, the sync process will continue with the new configuration setting to the cascade configuration standby server (nzel81.oraclekiwi.co.nz). The configuration instead of being a cascaded configuration (A-->B-->C) has become a configuration with 2 standby clusters with the same source host (A -->B A-->C).

Now, a switchover can be performed for the cascade configuration(PG-5435-13-CAS), making the standby host (nzel81.oraclekiwi.co.nz) the primary host by doing this. The original configuration (PG-5435-13) becomes the cascade configuration.