Create PostgreSQL DR Cluster
- Vijayganesh Sivaprakasam
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.
The process of creating a configuration is same for all the three method of creating a DR 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.
A full backup is taken on the primary and sent to the standby server.
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.
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.
The Primary Cluster will be restarted to enable the archive mode and also provide the location for archive and restore command along with archive timeout parameter. This is updated on postgresql.conf file.
The primary cluster will be restarted even if the archive mode is already enabled to update other parameters.
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)
WAL Streaming replication allows real-time transfer of primary cluster database changes to standby. The databases are kept in sync. There are three processes wal receiver, wal sender and startup. The wal receiver process sends the LSN(Log sequence Number) up until the standby (slave) has replayed and send it to the primary. The wal sender process sends the WAL data until the latest LSN starting from the LSN sent by the wal receiver and the same process writes the data to the WAL Segments. The startup process on slave that replays the data written to WAL segment.
A few parameters need to be updated on the primary cluster to ensure that the streaming happens.
In Postgresql.conf file we need to ensure that the listen_address parameter is set to provide a list of IP addresses that it listens to. This is usually the standby server address. In the below example we have kept the listen_address to “*” so it listens to all the addresses.
We also have to change the pg_hba.conf file to provide connections to the standby server and also add replication connections from localhost to the remote host.
host all all 10.0.2.23/24 (Address of remote server) trust
host replication all 10.0.2.22/30 (Address of local server needed after Graceful Switchover) trust
host replication all 10.0.2.23/30 (Address of remote server) trustLine 1 of the above code block is just for remote connection
lines 3 &4 are mainly for replication connections that do for all privileged users (usually postgres or some specific user). Line 3 is a local server address and is mainly used to ensure that the switchback happens without any issues.
( 1 ) Postgres configuration name
( 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 ) WAL Streaming method used for creating the standby cluster
( 4 ) The default user(postgres) used for creating the standby cluster. The password would be the host password for postgres users.
( 5 ) The observer component is enabled by default. This would automatically monitor the configuration when the DR cluster is created.
( 6 ) Click start to create the DR cluster.
( 1 ) The configuration name and the status of the streaming replication (In Sync)
( 2 ) The primary cluster status with port number and server name.
( 3 ) The method of replication used for creating the standby (Streaming)
( 4 ) The standby cluster status with port number and server name.