Page Comparison

...

2. Create DR Cluster using WAL Streaming Mode

2.1 Explanation

Below is the diagrammatic and explanation of steps that happen on the background when we run the creation of DR cluster using the streaming mode.

...

...

...

...

...

...

...

...

...

1. Trigger CSD in streaming mode

2. Create a Replication Slot on Primary

   • 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. Tar PostgreSQL Config Files on Primary Server:

   • postgres.conf

   • pg_ident.conf

   • pg_hba.conf

4. Transfer Config Files to Standby Server

   • Transfer the tarball containing PostgreSQL config files from the primary cluster to the standby cluster

5. Data Directory Setup Using pg_basebackup

   • Initiate a streaming backup from the standby cluster to the primary cluster . This process retrieves all files from the primary cluster’s data directory and includes the previously established replication slot on step 2

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

   • Extract files from tarball received in step 4 and place them in the target configuration directory on the standby server

   • Configure the PostgreSQL instance on the standby cluster 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 cluster

   • After setting up the data directory and config directory, restart the standby cluster.

9. Applying Configuration Changes

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

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.

Image Removed

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

...

...

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.

Image Added

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
loghot_filenamestandby = 'postgresql-%Y-%m-%d_%H%M%S.logon'

# Added by Dbvisit StandbyMP
log_rotation_agedestination = '1dstderr'

# Added by Dbvisit StandbyMP
logginglog_collectordirectory = 'onlog'

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

# Added by Dbvisit StandbyMP
primarylog_slotrotation_nameage = '1d'

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

postgresql.auto.conf

primary_conninfoDbvisit StandbyMP
logging_collector = '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.

...

Trigger CSD in archive mode.

...

Create Base Backup Files

• Generate the essential base backup files using the following command (By default Postgres is installed under /usr/pgsql-<version> directory)

  Code Block
  $PG_HOME/bin/pg_basebackup -D tempBackupDir -F tar -X stream -R --progress --verbose

• The aforementioned command will generate the subsequent files to be transferred to the Standby to facilitate the cluster restoration process.

  • base.tar: PostgreSQL base files, including settings and etc

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

  • tblspc_xxxxx.tar: customized table space files

  • backup_manifest: backup metadata, is used for pg_verifybackup to verify the backup

...

Tar the following PostgreSQL configuration files on the primary server

• Postgresql.conf

• pg_ident.conf

• pg_hba.conf

...

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

...

Data directory setup

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

...

Config directory setup

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

• Configure PostgreSQL in archive mode as per the Dbvisit DR cluster creation parameter

...

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

...

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

Apply Configuration Changes from CSD

...

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

...

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#-------------------------------------- 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

--------------------------------------

# Add settings for extensions here

# 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
       

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.

...