Note:

Set up PostgreSQL primary and two standby databases using OCI Console and validate data replication

Introduction

PostgreSQL is an open source object-relational database management system. It is highly extensible, highly scalable, and has many features. PostgreSQL supports data replication across multiple data centers.

Servers that can modify data are called master or primary servers. Servers that track changes in the primary are called standby servers. A standby server that cannot be connected to until it is promoted to a primary server is called a warm standby server, and one that can accept connections and serves read-only queries is called a hot standby server.

Resource Manager is an Oracle Cloud Infrastructure (OCI) service that allows you to automate the process of provisioning your OCI resources. Using Terraform, Resource Manager helps you install, configure, and manage resources through the “infrastructure-as-a-code” model.

Oracle Cloud Infrastructure is designed for workloads that require consistent high-performance, including stateful connections to databases, raw processing through CPUs or GPUs, millions of storage IOPS, and GB/s of throughput. It starts with a zero-trust architecture. In designing Oracle Cloud, we wanted a cloud that performed better for every application, but also had better price-performance.

Objective

Configure 1 primary and 2 hot standby servers using Terraform Code in OCI Resource Manager. It will be deployed on 3 compute instances in 3 different Availability Domains.

After the deployment, we will check the replication of tables from primary to standby server and we will connect to the hot standby databases to query the data.

  1. Using the terraform stack, we will be deploying the Network, PostgreSQL primary and 2 standby servers.

  2. We will create the sample table in Primary and insert few records in the table.

  3. Then we will verify, if these sample table and rows have been replicated to both standby servers.

Task 1: Create the Stack

  1. Open the following link in a browser: Deploy to Oracle Cloud.

  2. If you aren’t already signed in, enter the tenancy and user credentials.

  3. Select the region where you want to deploy the stack.

  4. On the Stack information page, select the I have reviewed and accept the Oracle Terms of Use check box. The Stack Information section will display the details.

  5. Click Next.

  6. On the Configure Variables page, configure the following settings:

    Note: In this tutorial, we are deploying PostgreSQL version 12 in a public subnet.

    Variables:

    1. Choose your required Availability Domain and PostgreSQL version.

    2. Clear the Create in Private Subnet check box.

    3. Select the Show advanced options check box and specify details for network, compute, storage, standby, and other settings.

      Variables:

      Variables3:

    4. Specify the required OS, backup policy and other details.

      Variables:

    5. Specify the required values for OCPU and Memory.

      1. Choose your Primary Node Shape and replicat user.

        Variables:

      2. Provide details for your Hotstandby1 (Choosing AD-2).

        Variables:

      3. Provide details for Hotstandby2 (Choosing AD-3).

        Variables:

    6. Click Next.

  7. Verify your details on the Review page and ensure that the Run apply check box is selected to provision the resources immediately.

    Stack Review

  8. Click Create.

    Job Logs

  9. Monitor your stack job’s log (Go to Developer Services, Resource Manager, Stacks) as shown in the following screenshots.

    Stack Navigation

    Stack Page

    Stack Job Status

    Stack Job Logs

Note: For the test setup used in this tutorial, the stack was applied and successfully configured the resources in 11 minutes for the selected shapes. This can vary based on the configuration settings you choose.

Stack Job Succeeded

The following screenshot displays the completed Primary and Standby server configuration.

PG Compute Running

Task 2: Verify the connectivity and replication

  1. ssh to your Primary node as opc user and run the following command:

    sudo su - postgres

    pgprimary login

  2. Create table order_details in the Primary node .

    CREATE TABLE order_details (order_detail_id integer CONSTRAINT order_details_pk PRIMARY KEY, order_id integer NOT NULL, order_date date, qty integer, ACCEPTED_QUANTITY integer);

  3. Describe the table created.

    \dt order_details

  4. Query the data and it should return 0 rows:

    select * from order_details;

    pgprimary table

  5. Log in to the standby1 server and verify that the order_details table has been replicated.

    pgstandby table

  6. Log in to the standby2 server and verify that the order_details table has been replicated.

    pgstandby2 table

  7. Insert 2 rows in the Primary Database table order_details.

    BEGIN; INSERT INTO order_details (order_detail_id,order_id,order_date,qty,ACCEPTED_QUANTITY) values (101,1,'2022-07-14',10,5); INSERT INTO order_details (order_detail_id,order_id,order_date,qty,ACCEPTED_QUANTITY) values (102,2,'2022-07-14',100,100); COMMIT;

    pgprimary insert

  8. Verify the rows in the standby1 server, it should have been replicated.

    pgstandy1 verify

  9. Verify the rows in standby2 server, it should have been replicated here also.

    pgstandy2 verify:

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.