Page Comparison

...

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

A The primary cluster must update 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.

...

Line 1 of the above code block is just for remote connectionlines . 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. 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.

create user repuser replication login encrypted password 'xxxxxx';

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:

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

# Add settings for extensions here

# Added by Dbvisit StandbyMP
# archive_mode = 'off'

# Added by Dbvisit StandbyMP
hot_standby = 'off'

# Added by Dbvisit StandbyMP
primary_conninfo = 'user=postgres password=xxxxxx channel_binding=prefer host=centlin72.oraclekiwi.co.nz port=5435 sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'

# Added by Dbvisit StandbyMP
primary_slot_name = 'dbvisit_3'

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

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
primary_conninfo = ''

# Added by Dbvisit StandbyMP
primary_slot_name = ''

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

3. Create DR Cluster using WAL Archive Mode

...

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 RemovedImage Added

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

...