1. Deploy a PostgreSQL database
  2. Deploy a PostgreSQL Database

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.

Note:

Oracle Cloud Infrastructure (OCI) offers as well a fully managed PostgreSQL service in OCI. You can find more information here: Experience the best of PostgreSQL with OCI Database with PostgreSQL.

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 with file-based log shipping.

The following diagram illustrates this reference architecture.

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

postgre-oci-oracle.zip

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.

  • Site-to-Site VPN

    Site-to-Site VPN provides IPSec VPN connectivity between your on-premises network and VCNs in Oracle Cloud Infrastructure. The IPSec protocol suite encrypts IP traffic before the packets are transferred from the source to the destination and decrypts the traffic when it arrives.

  • FastConnect

    Oracle Cloud Infrastructure FastConnect provides an easy way to create a dedicated, private connection between your data center and Oracle Cloud Infrastructure. FastConnect provides higher-bandwidth options and a more reliable networking experience when compared with internet-based connections.

  • Dynamic routing gateway (DRG)

    The DRG is a virtual router that provides a path for private network traffic between VCNs in the same region, between a VCN and a network outside the region, such as a VCN in another Oracle Cloud Infrastructure region, an on-premises network, or a network in another cloud provider.

    This architecture shows an optional Dynamic Routing Gateway (DRG) for a private subnet.

  • Internet gateway

    The internet gateway allows traffic between the public subnets in a VCN and the public internet.

  • Network address translation (NAT) gateway

    A NAT gateway enables private resources in a VCN to access hosts on the internet, without exposing those resources to incoming internet connections.

  • Bastion service

    Oracle Cloud Infrastructure Bastion provides restricted and time-limited secure access to resources that don't have public endpoints and that require strict resource access controls, such as bare metal and virtual machines, Oracle MySQL Database Service, Autonomous Transaction Processing (ATP), Oracle Container Engine for Kubernetes (OKE), and any other resource that allows Secure Shell Protocol (SSH) access. With Oracle Cloud Infrastructure Bastion service, you can enable access to private hosts without deploying and maintaining a jump host. In addition, you gain improved security posture with identity-based permissions and a centralized, audited, and time-bound SSH session. Oracle Cloud Infrastructure Bastion removes the need for a public IP for bastion access, eliminating the hassle and potential attack surface when providing remote access.

  • Route table

    Virtual route tables contain rules to route traffic from subnets to destinations outside a VCN, typically through gateways.

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

  • Block volume

    With block storage volumes, you can create, attach, connect, and move storage volumes, and change volume performance to meet your storage, performance, and application requirements. After you attach and connect a volume to an instance, you can use the volume like a regular hard drive. You can also disconnect a volume and attach it to another instance without losing data.

  • PostgreSQL

    PostgreSQL is an open-source relational database management system (RDBMS) that is highly extensible and highly scalable. It supports SQL (relational) and JSON (non-relational) querying.

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 9 OS image with a VM.Standard.E4.Flex 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.

Explore More

For a fully-managed instance using Oracle Cloud Infrastructure (OCI) Database with PostgreSQL, see Experience the best of PostgreSQL with OCI Database with PostgreSQL.

Change Log

This log lists significant changes:

Acknowledgments

  • Author: Lukasz Feldman
  • Contributor: Wei Han