Page Comparison

...

1. For this example, a Hot standby is created that can be accessible for reporting purposes as the databases in the cluster are in read only mode.

2. The WAL file archiving method is used to create the standby cluster.

3. The location on a primary where the base backup is taken and the location to which it's transferred in the standby. This location provided is a default location but it's recommended to provide a different location to accommodate backups that are larger in size.

4. The custom backup location of primary and standby in which the WAL archive files are copied in the primary and transferred to standby.

5. The archive timeout in seconds. This corresponds to the archive_timeout parameter in postgresql.conf. After 60 sec, a checkpoint is forced on primary triggering a WAL archive file creation to sync with the standby. This would ensure that the standby is always 60 seconds behind the primary.

6. By, default the observer is enabled that will start monitoring the configuration as soon the DR cluster is created.

7. The primary cluster will be restarted irrespective of whether the archive_mode is off or on in the postgresql.conf file.

8. Click start to create the DR cluster.

...

After successful completion. The summary displayed will show the time it took to create the DR cluster ( 1 ) and also the step by steps for the DR cluster ( 2 ).

...

CSD for Log shipping Mode

...

Trigger CSD in archive mode

...

Create Replication Slot on Primary

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

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

...

Create Base Backup Files

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

   Code Block
   /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.

...

Tar the following PostgreSQL configuration files on the primary server

1. postgres.conf

2. pg_ident.conf

3. pg_hba.conf

...

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

...

Data directory setup

1. On the standby server, 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.

...

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

...

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

...

After configuring the data and config directories, restart the standby PostgreSQL cluster

Apply Configuration Changes from CSD

...

The dashboard will now show the following

1. The configuration is in sync. The standby is in sync with the primary.

2. The observer is monitoring the configuration.

3. The method used in creating the DR cluster (WAL file archiving).

4. The standby status is now “HOT STANDBY”. The standby cluster database is in read-only status.

Image Added

Below are the parameters that are modified on both Primary and Standby clusters.

PRIMARY:

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Add settings for extensions here

# Added by Dbvisit StandbyMP
archive_command = '"/usr/dbvisit/standbymp/bin/dbvpgarchive" "-configurationId" "6" "-targetAddr" "centlin72.oraclekiwi.co.nz" "-targetPort" "7890" "-sourceFile" "/u01/PG5432-12/%p" "-targetFile" "/u01/backup/PG5432-12/6/%f" "-localAddr" "" "-localPort" "7890" "-pgBin" "/usr/pgsql-12/bin" "-pgData" "/u01/PG5432-12" "-backupFile" "/u01/backup/PG5432-12/6/%f"'

# Added by Dbvisit StandbyMP
archive_mode = 'on'

# Added by Dbvisit StandbyMP
archive_timeout = 60

# Added by Dbvisit StandbyMP
hot_standby = 'off'

# Added by Dbvisit StandbyMP
archive_cleanup_command = '"/usr/pgsql-12/bin/pg_archivecleanup" "/u01/backup/PG5432-12/1" "%r"'

# Added by Dbvisit StandbyMP
data_directory = '/u01/PG5432-12'

# Added by Dbvisit StandbyMP
port = 5432

# Added by Dbvisit StandbyMP
restore_command = '"/usr/dbvisit/standbymp/bin/dbvpgrestore" "-configurationId" "1" "-sourceFile" "/u01/backup/PG5432-12/1/%f" "-targetFile" "%p" "-localAddr" "" "-localPort" "7890" "-pgBin" "/usr/pgsql-12/bin" "-pgData" "/u01/PG5432-12"'

STANDBY:

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Add settings for extensions here

# Added by Dbvisit StandbyMP
archive_command = '"/usr/dbvisit/standbymp/bin/dbvpgarchive" "-configurationId" "6" "-targetAddr" "centlin72.oraclekiwi.co.nz" "-targetPort" "7890" "-sourceFile" "/u01/PG5432-12/%p" "-targetFile" "/u01/backup/PG5432-12/6/%f" "-localAddr" "" "-localPort" "7890" "-pgBin" "/usr/pgsql-12/bin" "-pgData" "/u01/PG5432-12" "-backupFile" "/u01/backup/PG5432-12/6/%f"'

# Added by Dbvisit StandbyMP
archive_mode = 'off'

# Added by Dbvisit StandbyMP
archive_timeout = 60

# Added by Dbvisit StandbyMP
hot_standby = 'on'

# Added by Dbvisit StandbyMP
archive_cleanup_command = '"/usr/pgsql-12/bin/pg_archivecleanup" "/u01/backup/PG5432-12/6" "%r"'

# Added by Dbvisit StandbyMP
data_directory = '/u01/PG5432-12'

# Added by Dbvisit StandbyMP
port = 5432

# Added by Dbvisit StandbyMP
restore_command = '"/usr/dbvisit/standbymp/bin/dbvpgrestore" "-configurationId" "6" "-sourceFile" "/u01/backup/PG5432-12/6/%f" "-targetFile" "%p" "-localAddr" "" "-localPort" "7890" "-pgBin" "/usr/pgsql-12/bin" "-pgData" ""'

4. Create DR Cluster using WAL File shipping Mode

4.1 Technical explanation

This method of creating the standby is specific to Dbvisit. The wal files from the primary are copied to standby at regular intervals and applied on the standby.

1. Trigger CSD in archive mode.

2. Create a Replication Slot on the Primary

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

      Code Block
      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

      Code Block
      /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 to be transferred to the Standby to facilitate the cluster restoration process.

      1. base.tar: PostgreSQL base files, including settings.

      2. pg_wal.tar: wal data files for standby to restore

      3. tblspc_xxxxx.tar: customized table space files

      4. backup_manifest: backup metadata, 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. postgresql.conf

   2. pg_ident.conf

   3. pg_hba.conf

5. Transfer Backup Files from Primary to Standby, which are generated in step 3

6. Data directory setup

   1. On the standby server, receive and extract all files generated from Step 2 and Step 3. Place these files in the designated data folder, previously allocated, before initiating DR Cluster creation.

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 the Windows service for the cluster

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

10. Apply Configuration Changes from DR Cluster creation

    1. Applying the latest changes on configuration coming during DR cluster creation and persisting configuration data on the Control Center
       

4.2 Steps for creating DR cluster using Archive Shipping.

The top half of the DR Cluster creation page is similar to WAL Streaming and WAL archive mode.

1. Configuration name of the PostgreSQL for which DR cluster will be created.

2. The primary hostname and the status of the PostgreSQL cluster (ONLINE).

3. The standby hostname and also mentioning that no standby cluster exists.

4. The locations of the standby cluster datafiles. This location is recommended to be the same as the primary, but this can be changed and provided a custom location. For Linux the permission of these folders must be like below.

-bash-4.2$ ls -lrt /u01
drwx------.  5 postgres postgres  4096 Aug 16 16:41 tablespace
drwx------. 20 postgres postgres  4096 Aug 26 12:06 PG5433-12

5. The location of the configuration files (postgresql.conf, pg_hba.conf, and pg_indent.conf). This location can be different from the primary data directory, but the example below has these two in the same directory. Keeping the same as the primary is recommended, but this can also be different in the standby.

6. For this example, a Hot standby is created that can be accessible for reporting purposes as the databases in the cluster are in read only mode.

...

The second half of the DR cluster is specific to the method used for the creation.

1. The method being used to create the DR cluster. In the below example, the WAL file shipping method is used.

2. The location on a primary where the base backup is taken and the location to which it's transferred in the standby. This location is a default location, but it's recommended to provide a different location to accommodate larger backups.

3. The custom backup location of primary and standby in which the WAL archive files are copied in the primary and transferred to standby. It is recommended to provide different locations to ensure the location has enough free space.

4. The automated standby update time interval in seconds. Every 300 sec (default) the WAL files are copied from primary to standby to keep the standby in sync. The minimum time interval cannot be below 59 seconds.

5. By, default the observer is enabled that will start monitoring the configuration as soon the DR cluster is created.

6. Click start to create the DR cluster with the WAL file shipping method.

...

The final screen after successfully completing the DR cluster creation. The configuration for which the cluster is created ( 1 ). The duration of the process ( 2 ) and the steps for the process ( 3 )

...

The dashboard for the configuration is now updated, and the details are updated.

1. The configuration name and the time gap between standby and primary.

2. The automated standby update is enabled, and also the observer is enabled.

3. The primary cluster is online

4. The method using which the standby is synced with primary. In this case its the SHIPPING ( WAL file shipping) method.

5. The standby cluster status is now “HOT STANDBY,” and the standby cluster databases are accessible via read-only mode.

...

The changes done on postgresql.conf file done by Dbvisit.

PRIMARY:

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Add settings for extensions here

# Added by Dbvisit StandbyMP
# archive_mode = 'off'

# Added by Dbvisit StandbyMP
data_directory = '/u01/PG5433-12'

# Added by Dbvisit StandbyMP
hot_standby = 'off'

# Added by Dbvisit StandbyMP
# primary_conninfo = ''

STANDBY:

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Add settings for extensions here

# Added by Dbvisit StandbyMP
# archive_mode = 'off'

# Added by Dbvisit StandbyMP
data_directory = '/u01/PG5433-12'

# Added by Dbvisit StandbyMP
hot_standby = 'on'

# Added by Dbvisit StandbyMP
# primary_conninfo = ''

# Added by Dbvisit StandbyMP
# archive_mode = 'off'