Create PostgreSQL DR Cluster

Introduction

There are three methods for creating a DR cluster, each with two options.

• WAL Streaming

  • Hot Standby

  • Warm Standby

• WAL file Archiving

  • Hot Standby

  • Warm Standby

• WAL file Shipping

  • Hot Standby

  • Warm Standby

For Hot Standby, the standby database is accessible by the application as a read-only cluster, and the databases under the cluster can be queried and used for reporting purposes. This can happen while the standby cluster databases are still getting synced by the primary cluster.

The Warm Standby Cluster databases are not accessible by the application but are still getting synced by the primary cluster.

To create a DR cluster, you can either click on setup now? or “Set Up Disaster Recovery”

WAL Archiving (Hot Standby)

Few points to remember when creating a HOT DR cluster using the WAL Archive method.

1. A full backup is taken on the primary and sent to the standby server.

2. The primary cluster will be restarted before creating the cluster to update the parameters on postgresql.conf file. All parameters added by Dbvisit will be added in the end.

3. Ensure that the backup locations have enough free space available on both primary and standby for backup and restore operations.

Below are the details that need to be entered when creating the DR cluster using WAL Archive method.

( 1 ) The data file locations of the standby cluster. This value is default value as seen on the primary but can be changed to a different location.

( 2 ) The standby is run as a HOT standby, so the databases of the cluster are accessible to the user as read-only databases.

( 3 ) The method used for creating the DR cluster is “WAL file Archiving”.

( 4 ) The base backup location of the primary server and the location to which it is transferred on the standby server.

( 5 ) The custom backup location where the WAL files are copied in primary and then transferred to the standby location from which it is applied.

( 6 ) The archive timeout setting. This value in seconds will ensure that the standby is synced with the primary every 60secs. This value can be changed to a higher value.

( 7 ) The observer is enabled by default to monitor the configuration as soon as the DR cluster is created.

( 8 ) Press start to start the creation process.

Summary of the end of the DR Cluster creation from the primary. The automatic standby update and the observer components are automatically enabled.

( 1 ) The configuration name along with the time gap between standby cluster and primary cluster.

( 2 ) The status of the primary cluster

( 3 ) The method used to create the standby cluster

( 4 ) The status of the standby cluster

Below are the parameters that are modified on the postgresql.conf file

Primary Server:

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

# Add settings for extensions here

# Added by Dbvisit StandbyMP
archive_command = '"/usr/dbvisit/standbymp/bin/dbvpgarchive" "-configurationId" "1" "-targetAddr" "centlin72.oraclekiwi.co.nz" "-targetPort" "7890" "-sourceFile" "/u01/PG5432-12/%p" "-targetFile" "/u01/backup/PG5432-12/1/%f" "-localAddr" "" "-localPort" "7890" "-backupFile" "/u01/backup/PG5432-12/1/%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"'

Standby Server:

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

# Add settings for extensions here

# Added by Dbvisit StandbyMP
archive_command = '"/usr/dbvisit/standbymp/bin/dbvpgarchive" "-configurationId" "3" "-targetAddr" "centlin72.oraclekiwi.co.nz" "-targetPort" "7890" "-sourceFile" "/u01/PG5432-12/%p" "-targetFile" "/u01/backup/PG5432-12/3/%f" "-localAddr" "" "-localPort" "7890" "-backupFile" "/u01/backup/PG5432-12/3/%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/3" "%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" "3" "-sourceFile" "/u01/backup/PG5432-12/3/%f" "-targetFile" "%p" "-localAddr" "" "-localPort" "7890"'

WAL File Shipping (Hot Standby)

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 ) The data file locations on the standby server. This could be a different location than that of the primary server

( 2 ) The option to create a Hot standby. This will enable the customers to use the standby cluster for reporting purposes since the cluster databases will be opened in read-only.

( 3 ) The method used to create the standby (WAL File Shipping)

( 4 ) The base backup location for both primary and standby. This location is used for taking a backup of the primary cluster and then copy to the location provided on the standby cluster.

( 5 ) The custom backup location for copying the wal files on the primary and then copying to the location provided on the standby server.

( 6 ) The Automated Standby Update option, this option is enabled by default, and the value is set to 300. This would ensure that the standby is always 5 minutes behind the primary.

( 7 ) The Observer option is also enabled by default. As soon as the DR cluster is created, the observer will monitor the configuration. The observer can be modified to suit

( 8 ) Click Start to create the DR cluster.

( 1 ) The configuration Name

( 2 ) The Time gap between standby and primary.

( 3 ) The primary cluster status, the port on which the cluster runs, the servername and the status (ONLINE).

( 4 ) The standby cluster name and port on which the cluster runs, along with the servername and the status (HOT STANDBY)

( 5 ) Method used to create the DR Cluster.

WAL Streaming (Hot Standby)