PostgreSQL Cascade Standby Cluster
- Jan Klinke
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 WAL Streaming and WAL Archiving, but the replication must be of a single replication type (it’s not possible to mix Streaming and archiving)
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 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).
This page describes setting up cascade for WAL Streaming mode configuration. Setting up cascade for WAL Archiving is the same.
1. Create Streaming Cascade Configuration
To create cascade configuration we need to have already an existing Streaming configuration:
The standby database MUST be HOT STANDBY
Our streaming configuration is from pgdemo1 => pgdemo2 and we would like to add cascading postgreSQL Standby on host pgcascade1. Before starting, verify that the cascade server fulfills the prerequisites as described here: Creating PostgreSQL Configuration
Afterwards, create new postgreSQL configuration. In the dashboard click on NEW CONFIGURATION (1) and select Start with PostgreSQL (2):
This is the same as when creating non-cascade config. The create configuration form looks also entirely the same as when creating non-cascade configuration:
For our example, we select as primary host “pgdemo2” which is standby host for postgreSQL configuration “pgslon” (1) and we select as our standby pgcascade1 host (2). Name the configuration, enter the license key (3) and click on “Create Configuration”.
For full description of the Create configuration form, see: Creating PostgreSQL Configuration
After the configuration is created, the configuration will be visible in the dashboard:
Note the “HOT STANDBY” status for primary server for our cascade configuration and pgdemo1 is the target for “pgslon” configuration and “source” for our “pgcascade” configuration.
2. Create Streaming Cascade Standby PostgreSQL cluster
To Create our cascade standby PostgreSQL cluster, click on “Set Up Disaster Recovery” in ACTIONS pane of our “pgcascade” configuration:
Creating the cascade replica form has nearly the same options as non-cascade form:
You will be able to choose different locations for your datafiles and config files (1,2).
Make sure to select HOT mode for your standby (3)
Notification message that this will be a cascade replica is visible (4)
set correct username and password for your cascade replica (5,6) and test (7). Username and Password will be inherently same as in your source Streaming replication
Enable or disable observer for this configuration (8)
Create Cascade Standby Streaming Replica (9)
You can refer to For further details to Creating PostgreSQL Standby Cluster | 2. Create WAL Streaming DR Cluster
Once the process is started, you can view the task status and details in the dashboard:
Once the task is finished, the dashboard will display both configurations like so:
3. Switchover in Cascade deployments
Graceful Switchover (GS) is possible only for the source configuration, not for the cascade configuration. In our example, it means that only configuration “pgslon” is eligible for switchover:
For information how to execute GS, please see: PostgreSQL Graceful Switchover . Once the switchover is executed, the configurations will have following status:
Single postgreSQL cluster on pgdemo2 is now source for two standby postgreSQL clusters on pgcascade1 and pgdemo1. It is now possible to execute GS process for configuration pgcascade. After Graceful Switchover is completed for pgcascade, the situation will look like so:
Configuration pgslon is now the cascade configuration.
4. Failover in Cascade deployments
In cascade deployments, it is always possible to activate both: the source or the cascade standby database. But only activation of source standby database is without consequences for the other configruation. For example if we have following situation:
A. Activating Source Standby Database
If we activate standby database on pgdemo2 for our source configuration pgslon, there will not be any consequences for configuration pgcascade (other than it will become non-cascade configuration). After failover of configuration pgslon the situation will look like so:
The WAL Streaming for configuration pgcascade will keep working, because new timeline will be picked up by cascade standby database on pgcascade1 host.
B. Activating Cascade Standby Database
Activating standby database for configuration pgcascade on host pgcascade1 in this situation:
Will result in an activation error:
It is impossible for Dbvisit to promote the PostgreSQL cluster on pgdemo2, because it is locked by configuration pgslon. If we want to activate cascade standby database, we need to remove configuration pgslon first, so as our cascade is our only configuration:
And afterwards the activation will process successfully:
