Create DR Cluster for PostgreSQL

Requirement

1. Support Postgresql version

   1. v10 - v14

   2. Both servers should be running the same version.

2. Network connectivity

   1. The servers need to be on the same network and have consistent network connectivity.

   2. Ensure that the primary server has the correct pg_hba.conf settings.

CSD for streaming Mode

1. Trigger CSD in streaming mode

2. Create Replication Slot on Primary

   1. Run the following SQL query on the primary server to create a physical replication slot named SLOT_NAME

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

3. Tar PostgreSQL Config Files on Primary:

   1. postgres.conf

   2. pg_ident.conf

   3. pg_hba.conf

4. Transfer Config Files to Standby

   1. Transfer the tarball containing PostgreSQL config files from the primary server to the standby server

5. Data Directory Setup Using pg_basebackup
   Initiate a streaming backup from the standby server to the primary server. This process retrieves all files from the primary server's data directory and includes the previously established replication slot on step 2

   1. /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

6. Config Directory Setup

   1. Extract files from tar ball recieved on step 4 and place them in the target configuration directory on the standby server

   2. Configure the PostgreSQL instance on the standby server for streaming replication based on the CSD parameter and the replication slot created earlier

7. If the operating system is Windows, register a Windows service for the PostgreSQL cluster

8. Restart Standby Server

   1. After setting up data director and config directory, restart standby server

9. Applying Configuration Changes

   1. Applying the latest changes on configuration coming CSD and persisting configuration data on Control Center

CSD for archive Mode

1. Trigger CSD in archive mode

2. Create Base Backup Files

   1. Generate the essential base backup files using the following command:

      /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

3. Tar the following PostgreSQL configuration files on the primary server

   1. postgres.conf

   2. pg_ident.conf

   3. pg_hba.conf

4. Transfer Backup Files from Primary to Standby, which are generated on step 3 and step 2.b

5. Data directory setup

   1. On the Standby, receive and extract all files generated from Step 2.b and Step 3. Place these files in the designated data folder, previously allocated before initiating CSD.

6. Config directory setup

   1. Extract the files from the tarball received in Step 4 and place them into the target configuration directory on the standby server.

   2. Configure PostgreSQL in archive mode as per the CSD parameter

7. If the OS is windows, register windows service for the cluster

8. After configuring the data and config directories, restart the standby PostgreSQL server

9. Apply Configuration Changes from CSD

   1. Applying the latest changes on configuration coming CSD and persisting configuration data on Control Center

CSD for Log shipping Mode

1. Trigger CSD in archive mode

2. Create Replication Slot on Primary

   1. Run the following SQL query on the primary server to create a physical replication slot named SLOT_NAME

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

3. Create Base Backup Files

   1. Generate the essential base backup files using the following command

      /opt/rh/rh-postgresql13/root/usr/bin/pg_basebackup -D tempBackupDir -F tar -X stream -R --progress --verbose -S slot_name 

   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

   3. Note: Different archive mode base backups and log shipping backups also include the replication_slot within the base backup files and transfer it to the standby.

4. Tar the following PostgreSQL configuration files on the primary server

   1. postgres.conf

   2. pg_ident.conf

   3. pg_hba.conf

5. Transfer Backup Files from Primary to Standby, which are generated on step 3 and step 2.b

6. Data directory setup

   1. On the Standby, receive and extract all files generated from Step 2.b and Step 3. Place these files in the designated data folder, previously allocated before initiating CSD.

7. Config directory setup

   1. Extract the files from the tarball received in Step 4 and place them into the target configuration directory on the standby server.

   2. Configure PostgreSQL in archive mode as per the CSD parameter

8. If the OS is windows, register windows service for the cluster

9. After configuring the data and config directories, restart the standby PostgreSQL server

10. Apply Configuration Changes from CSD

    1. Applying the latest changes on configuration coming CSD and persisting configuration data on Control Center