Create DR Cluster for PostgreSQL

Requirement

Support Postgresql version
1. v10 - v14
2. Both servers should be running same version
Network connectivity
1. The servers must be on the same network and have proper network connectivity
2. Primary server should have proper pg_hba.conf setting

CSD for streaming Mode

Trigger CSD in streaming mode

Create Replication Slot on Primary

SELECT * FROM pg_create_physical_replication_slot(SLOT_NAME, false, false) WHERE NOT EXISTS (SELECT * FROM pg_replication_slots WHERE slot_name = SLOT_NAME)

On Primary MP find and Tar the postgresql config files, Including
1. postgres.conf
2. pg_ident.conf
3. pg_hba.conf
On Standby MP receive tar ball of postgresql config files from Primary server

Data directory setup: Using pg_basebackup to initialize streaming backup from Standby to Primary to retrieve all files in data directory from Primary with the Replication Slot created on step 2

/opt/rh/rh-postgresql13/root/usr/bin/pg_basebackup -D /var/lib/pgsql/data/13/main -F plain -X stream -S dbvisit_2 -R --progress --verbose --checkpoint=fast -d postgres://postgres@postgresqlcentos1:5432

Config directory setup:
1. Extract files from tar ball recieved on step 4 and put them into the target config directory
2. Configure the PostgreSQL configuration in streaming mode according to the CSD parameter and streaming slot.
If the OS is windows, register windows service for the cluster
After setting up data director and config directory, restart standby server
Applying the latest changes on configuration coming CSD and persisting configuration data on Control Center

CSD for archive Mode

Trigger CSD in archive mode
Create base backup files
1. ```
/opt/rh/rh-postgresql13/root/usr/bin/pg_basebackup -D tempBackupDir -F tar -X stream -R --progress --verbose 
```
2. The aforementioned command will generate the subsequent files, which are to be transferred to the Standby in order to facilitate the cluster restoration process.
  1. base.tar: postgresql base files, including setting and etc
  2. pg_wal.tar: wal data files for standby to restore
  3. tblspc_xxxxx.tar: customized table space files
  4. backup_manifest: backup meta data, is used for pg_verifybackup to verify the backup
On Primary MP find and Tar the postgresql config files, Including
1. postgres.conf
2. pg_ident.conf
3. pg_hba.conf
On Standby MP receive all files generated from Primary server on step 3 and step 2.b
Data directory setup: Extract files generated from 2.b and placed to the target data folder assigned before triggering CSD
Config directory setup:
1. Extract files from tar ball recieved on step 4 and put them into the target config directory
2. Configure the PostgreSQL configuration in archive mode according to the CSD parameter
If the OS is windows, register windows service for the cluster
After setting up data director and config directory, restart standby server
Applying the latest changes on configuration coming CSD and persisting configuration data on Control Center

CSD for Log shipping Mode

Trigger CSD in archive mode