Deploy a PostgreSQL Database

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

Architecture

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

The following diagram illustrates this reference architecture.

Description of postgre-oci.png follows
Description of the illustration postgre-oci.png

The architecture has the following components:

  • Region

    An Oracle Cloud Infrastructure region is a localized geographic area that contains one or more data centers, called availability domains. Regions are independent of other regions, and vast distances can separate them (across countries or even continents).

  • Availability domains

    Availability domains are standalone, independent data centers within a region. The physical resources in each availability domain are isolated from the resources in the other availability domains, which provides fault tolerance. Availability domains don’t share infrastructure such as power or cooling, or the internal availability domain network. So, a failure at one availability domain is unlikely to affect the other availability domains in the region.

  • Fault domains

    A fault domain is a grouping of hardware and infrastructure within an availability domain. Each availability domain has three fault domains with independent power and hardware. When you distribute resources across multiple fault domains, your applications can tolerate physical server failure, system maintenance, and power failures inside a fault domain.

  • Virtual cloud network (VCN) and subnets

    A VCN is a customizable, software-defined network that you set up in an Oracle Cloud Infrastructure region. Like traditional data center networks, VCNs give you complete control over your network environment. A VCN can have multiple non-overlapping CIDR blocks that you can change after you create the VCN. You can segment a VCN into subnets, which can be scoped to a region or to an availability domain. Each subnet consists of a contiguous range of addresses that don't overlap with the other subnets in the VCN. You can change the size of a subnet after creation. A subnet can be public or private.

    This architecture uses one public subnet to host the three Compute instances running PostgreSQL. You can choose to use a private subnet if your architecture requires it.

  • Security lists

    For each subnet, you can create security rules that specify the source, destination, and type of traffic that must be allowed in and out of the subnet.

    This architecture adds ingress rules for TCP port 5432. PostgreSQL servers listen on TCP port 5432.

Recommendations

Your requirements might differ from the architecture described here. Use the following recommendations as a starting point.

  • Compute shapes

    This architecture uses an Oracle Linux 7.7 OS image with a VM.Standard2.1 shape. You can choose a different shape if your application’s resource needs are different.

  • VCN

    When you create a VCN, determine the number of CIDR blocks required and the size of each block based on the number of resources that you plan to attach to subnets in the VCN. Use CIDR blocks that are within the standard private IP address space.

    Select an address range that doesn’t overlap with your on-premises network, so that you can set up a connection between the VCN and your on-premises network, if necessary.

    After you create a VCN, you can change, add, and remove its CIDR blocks.

    When you design the subnets, consider your functionality and security requirements. Attach all the compute instances within the same tier or role to the same subnet.

    Use regional subnets.

Considerations

  • Scalability

    This architecture deploys the PostgreSQL master node in one fault domain and the standby nodes in the other two fault domains.

    You can vertically scale the PostgreSQL cluster by changing the VM shape of each Compute node. Using a shape with a higher core count also increases the memory allocated to the Compute instance and network bandwidth.

  • Application availability

    Fault domains provide the best resilience within a single availability domain. This architecture uses another approach to high availability: deploying Compute instances that perform the same tasks across multiple fault domains. This design removes a single point of failure by introducing redundancy.

    After the architecture is deployed, you can connect to the public IP address of the PostgreSQL node by using SSH tools like PuTTY or Git Bash. You can use psql for DDL and DML operations on the PostgreSQL database. You can use the pgAdmin tool to perform administration operations.

  • Cost

    Select a virtual machine (VM) shape based on the cores, memory, and network bandwidth that you need for your database. You can start with a one-core shape, and if you need more performance, memory, or network bandwidth for the database node, you can change the VM shape later.

Deploy

The Terraform code for this reference architecture is available as a sample stack in Oracle Cloud Infrastructure Resource Manager. You can also download the code from GitHub, and customize it to suit your specific requirements.

  • Deploy using the sample stack in Oracle Cloud Infrastructure Resource Manager:
    1. Click Deploy to Oracle Cloud

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

    2. Select the region where you want to deploy the stack.
    3. Follow the on-screen prompts and instructions to create the stack.
    4. After creating the stack, click Terraform Actions, and select Plan.
    5. Wait for the job to be completed, and review the plan.

      To make any changes, return to the Stack Details page, click Edit Stack, and make the required changes. Then, run the Plan action again.

    6. If no further changes are necessary, return to the Stack Details page, click Terraform Actions, and select Apply.
  • Deploy using the Terraform code in GitHub:
    1. Go to GitHub.
    2. Clone or download the repository to your local computer.
    3. Follow the instructions in the README document.

Change Log

This log lists significant changes: