Note:

• This tutorial requires access to Oracle Cloud. To sign up for a free account, see Get started with Oracle Cloud Infrastructure Free Tier.
• It uses example values for Oracle Cloud Infrastructure credentials, tenancy, and compartments. When completing your lab, substitute these values with ones specific to your cloud environment.

Deploy a PostgreSQL Cluster on Oracle Cloud Infrastructure Compute Instances

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.

This tutorial shows a typical three-node deployment of a PostgreSQL cluster on Oracle Cloud Infrastructure (OCI) Compute instances. In this tutorial, the servers are configured in master and standby configuration and use streaming replication.

You can find the automation instructions here: terraform-oci-arch-postgresql.

Note: It will automatically install postgis34 and pgvector.

Architecture Diagram

Objectives

• Deploy a three-node PostgreSQL cluster.

• Modify the code to better fit your requirements.

Prerequisites

• Permission to manage the following resources in your OCI tenancy: Virtual Cloud Networks (VCNs), Internet Gateways, Route Tables, Security lists, Subnets, and Instances.

• Create the following resources:

  • 1 VCN,
  • 1 Subnet,
  • 1 Internet Gateway,
  • 1 Route Rules, and
  • 3 Compute Instances (1 primary master PostgreSQL instance and 2 standby instances of PostgreSQL).

Task 1: Deploy the Terraform Code using OCI Resource Manager

The deployment can be done in two ways, using terraform Command Line Interface (CLI), or OCI Resource Manager. In this tutorial, we will be using OCI Resource Manager.

For terraform CLI, see the repository documentation here: terraform-oci-arch-postgresql.

1. Click the following image to start the OCI Resource Manager process that will deploy the resources.

   

2. If you are not already signed in, enter the tenancy and user credentials, review and accept the terms and conditions, select region, compartment where you want the stack to reside and click Next that will take you to the next step to Configure variables.

3. In Configure variables, enter the following information to configure the variables.

   • Compartment: Select the compartment in which you want to deploy the resources.
   • Availability Domain for master PostgreSQL: Select the availability domain for the master compute instance.
   • PostgreSQL Version: You can select versions 9.6, 10, 11, 12 or 13.
   • PostgreSQL Password: Set a Password to access PostgreSQL.
   • Deploy first PostgreSQL HotStandby: Select if you want to deploy the first standby.
   • Deploy second PostgreSQL HotStandby: Select if you want to deploy the second standby.
   • Create in Private Subnet: Select if you want the instances to be created in a private subnet.

   Note:

   • Deploying the first and second PostgreSQL HotStandby allows you to deploy one, two, or three nodes.
   • If you select to create in a private subnet, an OCI Bastion service will be created to allow access.

4. Click Show advanced options to enter additional configurable variables information.

   • Network Optional Configuration: This allows you to specify the VCN and subnet CIDRs and whether or not to create a Dynamic Routing Gateway (DRG).
   • Compute, Storage & Backup Optional Configuration: This allows you to add a public SSH key to access the PostgreSQL instances and configure backups.
   • PostgreSQL Master Optional Configuration: Specify PostgreSQL master instance fault domain, shape, CPU and memory for flex shapes.
   • PostgreSQL HotStandby1 Optional Configuration: Specify PostgreSQL first standby instance availability domain, fault domain, shape, CPU and memory for flex shapes.
   • PostgreSQL HotStandby2 Optional Configuration: Specify PostgreSQL second standby instance availability domain, fault domain, shape, CPU and memory for flex shapes.

5. Click Next to review the information.

   Note:

   • Deselect Run apply if you want to run a plan first.
   • If you select Run apply, it will trigger the terraform apply command and it will automatically start provisioning the resources.

6. Click Create.

Task 2: Destroy the Deployment

To destroy your resources, simply go to the Stack Details page and click Destroy.

Understand how PostgreSQL is Installed and Configured

This information is useful if you need to modify the way PostgreSQL works.

It will help you understand:

• Where exactly the installation and configuration is.

• The order in which the scripts are running.

• A high-level overview of what each script does.

Note: This will not cover how to customize the automation. Depending on the changes, it might require modifications in multiple terraform files.

• Important files we are using in this tutorial are:

  • remote.tf: Terraform file where the scripts are deployed on each PostgreSQL instance.
  • scripts/postgresql_install_binaries.sh: Installs PostgreSQL server, some extensions and configures firewall.
  • scripts/postgresql_master_initdb.sh: Initializes the database.
  • scripts/postgresql_master_setup.sh: Configures the replica for PostgreSQL standby1.
  • scripts/postgresql_master_setup2.sh: Configures the replica for PostgreSQL standby2.
  • scripts/postgresql_standby_setup.sh: Additional configuration for replicas like firewall, max connections, editing replica.conf.

• The scripts run in the following order:

  • scripts/postgresql_install_binaries.sh: Runs on PostgreSQL master instance.
  • scripts/postgresql_master_initdb.sh Runs on PostgreSQL master instance.
  • scripts/postgresql_install_binaries.sh Runs on PostgreSQL standby1 instance.
  • scripts/postgresql_install_binaries.sh Runs on PostgreSQL standby2 instance.
  • scripts/postgresql_master_setup.sh Runs on PostgreSQL master instance.
  • scripts/postgresql_master_setup2.sh Runs on PostgreSQL master instance.
  • scripts/postgresql_standby_setup.sh Runs on PostgreSQL standby1 instance.
  • scripts/postgresql_standby_setup.sh Runs on PostgreSQL standby2 instance.

Acknowledgments

• Author - Gabriel Feodorov (Senior Cloud Engineer)

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.

---

Title and Copyright Information

Deploy a PostgreSQL Cluster on Oracle Cloud Infrastructure Compute Instances

G25614-01

January 2025

Copyright ©2025, Oracle and/or its affiliates.