Note:

Implement Cross Region Disaster Recovery for PostgreSQL Databases on VM Using Physical Standby Replication

Introduction

The pg_basebackup is a command-line utility provided by PostgreSQL for performing full backup of a running PostgreSQL database cluster. It is designed to create a binary copy of the database cluster, which can be used for replication or recovery purposes.

pg_basebackup is a command-line utility provided by PostgreSQL for performing full backup of a running PostgreSQL database cluster. It is designed to create a binary copy of the database cluster, which can be used for replication or recovery purposes.

One of the key advantages of the pg_basebackup utility is that it works without requiring access to the file system where the database resides. Instead, it connects to the PostgreSQL server using the streaming replication protocol, making it safe and convenient to use even while the server is running.

Typically used in conjunction with write-ahead logging (WAL) archiving, the pg_basebackup utility plays a crucial role in setting up streaming replication and ensuring high availability and disaster recovery for PostgreSQL environments running on Virtual Machines (VM).

Key features:

This tutorial outlines the tasks to set up cross-region streaming replication between two PostgreSQL databases hosted on compute VMs.

Objectives

Prerequisites

Create VCN peering across the region. To connect to the compute VMs across regions, VCN peering must be set up. For more information, see:

Ensure that the VCN peering is correctly configured to establish seamless connectivity between the databases in different regions.

Task 1: Set up the Primary PostgreSQL Database

  1. Create the user named replicator in primary PostgreSQL database.

    CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD '*********';

  2. Validate the roles/users.

    postgres=# \du
                                    List of roles
   Role name    |                         Attributes                         | Member of
----------------+------------------------------------------------------------+-----------
admin          | Superuser                                                  | {}
guest          |                                                            | {}
mytestdb       |                                                            | {}
postgres       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
replicator     | Replication

  3. Update the postgresql.conf file with the following details.

    listen_addresses = '*'

  4. Update the pg_hba.conf file. Use the standby database IP address.

    host    replication     replicator      Standby_DB_IP/32       md5

  5. Restart the PostgreSQL service.

    sudo systemctl restart PostgreSQL

Task 2: Set up the Standby PostgreSQL Database

  1. Stop the PostgreSQL service.

    sudo systemctl stop PostgreSQL

  2. Remove the existing binaries.

    cp -R /var/lib/postgresql/13/main /var/lib/postgresql/13/main_old
rm -rf /var/lib/postgresql/13/main

  3. Execute the pg_basebackup command to set up the streaming replication.

    pg_basebackup -h PrimaryDB_IP -D /var/lib/postgresql/13/main -U replicator -P -v -R -X stream -C -S slaveslot1

  4. Start the PostgreSQL service.

    sudo systemctl start PostgreSQL

Task 3: Verify the Streaming Replication on the Primary PostgreSQL Database

Run the following command on primary PostgreSQL database to gather the sync status between the primary and DR databases.

SELECT * FROM pg_replication_slots;
SELECT * FROM pg_stat_replication;

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.