Create DR Cluster for PostgreSQL

1. Introduction

There are three methods for creating a DR cluster, each with two options. A PostgreSQL configuration must be created before attempting to create a DR Cluster.

• WAL (Write Ahead Logs) Streaming

  • Hot Standby

  • Warm Standby

• WAL (Write Ahead Logs) file Archiving

  • Hot Standby

  • Warm Standby

• WAL (Write Ahead Logs) 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”

2. Create DR Cluster using WAL Streaming Mode

2.1 Explanation

2.2 Streaming mode using Dbvisit

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 the slave replays the data written to the WAL segment.

The primary cluster must update a few parameters to ensure 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)          md5 (scram-sha-256 in version after 10 if needed)

host    replication     all                 10.0.2.22/30   (Address of local server needed after Graceful Switchover)        md5 (scram-sha-256 in version after 10 if needed)
host    replication     all                 10.0.2.23/30  (Address of remote server)         md5 (scram-sha-256 in version after 10 if needed)

Line 1 of the above code block is just for remote connection. Lines 3 &4 are mainly for replication connections that are 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.

The top screen displays the usual configuration details

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 PG5435-13

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. The tablespace location of the primary is in a different location from the default data directory. The above code snippet also shows the permission of these folders.

1. The method used for creating the DR cluster (WAL Streaming)

2. The replication user that is used for streaming connection to the primary cluster. By default, the Postgres user is used. It is recommended to use a different user for establishing this connection. Sample user creation SQL used. SUPERUSER role needed here because only SUPERUSER can retrieve the sync status between primary database and standby database

create user repuser replication login encrypted password 'xxxxxx';
alter ROLE repuser SUPERUSER;

3. The password of the replication user that is used for streaming connection to the primary cluster.

4. A replication test that can be done to ensure the correct username and password with necessary privileges and the whether the pg_hba.conf updates are correct.

5. The result of the replication test.

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

7. Click start to start creating the DR cluster using WAL streaming method.

The report of successful completion of the DR Cluster creation using the WAL Streaming method. The time it took to complete the DR Cluster creation ( 1 ). The overview of the steps performed during this process ( 2 ).

1. The configuration name and the status of the WAL Streaming which is STREAMING(In Sync).

2. The observer is monitoring the configuration.

3. The primary hostname and the status of the production cluster. The port on which the primary cluster is running.

4. The method used for creating the DR cluster. We have used WAL Streaming method (STREAMING).

5. The standby hostname and the status of the standby cluster (HOT STANDBY). The port in which the standby cluster is running.

The configuration parameter changes done by Dbvisit as part of the DR cluster creation.

PRIMARY:

No changes here

STANDBY:

postgresql.conf

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

# Add settings for extensions here

# Added by Dbvisit StandbyMP
archive_mode = 'off'

# Added by Dbvisit StandbyMP
hot_standby = 'on'

# Added by Dbvisit StandbyMP
log_destination = 'stderr'

# Added by Dbvisit StandbyMP
log_directory = 'log'

# Added by Dbvisit StandbyMP
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

# Added by Dbvisit StandbyMP
log_rotation_age = '1d'

# Added by Dbvisit StandbyMP
logging_collector = 'on'

# Added by Dbvisit StandbyMP
primary_conninfo = ''

# Added by Dbvisit StandbyMP
primary_slot_name = ''

# Added by Dbvisit StandbyMP
data_directory = '/u01/PG5435-13'

postgresql.auto.conf

primary_conninfo = 'user=postgres password=pg channel_binding=prefer host=postgresql1 port=5434 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
primary_slot_name = 'dbvisit_3'

3. Create DR Cluster using WAL Archive Mode

3.1 Explanation

Below is a detailed explanation of how the DR cluster is created in the background.

3.2 Steps for creating DR cluster using Archive Mode

To start the DR cluster creation using archive mode

The creation of the DR cluster can be started either by clicking on Set up now? ( 1 ) or from the ACTIONS menu by clicking on “Set Up Disaster Recovery” ( 2 )

The top screen displays the usual configuration details

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 PG5432-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. The tablespace location of the primary is in a different location from the default data directory. The above code snippet also shows the permission of these folders.

7. The tablespace location of the standby. This folder can also be different from the primary but it is recommended to keep the directory structures same as primary.

After this choose the WAL file archiving method with a Hot or Warm standby option.

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. The archive_timeout specifies the maximum time interval between the completion of a WAL file and its archiving, and also indicates the maximum lag standby (replica) database can potentially fall behind the primary database in terms of the time of changes applied.

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 ).

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.

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
data_directory = '/u01/PG5432-12'

STANDBY:

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

# Add settings for extensions here

# 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.

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:

No changes here

STANDBY:

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

# Add settings for extensions here

# Added by Dbvisit StandbyMP
archive_mode = 'off'

# Added by Dbvisit StandbyMP
hot_standby = 'on'

# Added by Dbvisit StandbyMP
log_destination = 'stderr'

# Added by Dbvisit StandbyMP
log_directory = 'log'

# Added by Dbvisit StandbyMP
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

# Added by Dbvisit StandbyMP
log_rotation_age = '1d'

# Added by Dbvisit StandbyMP
logging_collector = 'on'