An overview of using Terraform to deploy OCI’s managed PostgreSQL database

PostgreSQL is an open source, object relational database that gets attention for its robust architecture and unwavering data integrity, making it a prime selection within enterprise environments. Customers considering a transition from managed Amazon Web Services (AWS) Relational Database Service (RDS) for PostgreSQL or Azure Database for PostgreSQL to Oracle Cloud Infrastructure (OCI) can find Oracle is now offering a fully managed PostgresSQL service. This cost-efficient, scalable, high-performing database service is suitable for both enterprises and small-to-medium-sized businesses, seeking enhanced flexibility and reliability. 

^{Figure 1: Why OCI Database with PostgreSQL?}

For more on the cloud architecture that enables these business advanages, check out First Principles: Optimizing PostgreSQL for the cloud. OCI Chief Technical Architect, Pradeep Vincent, walks through its efficiency architecture piece by piece.

Exploring the architecture setup

In this post, we explore the OCI-managed PostgreSQL service and its deployment in an OCI tenancy with Terraform. We guide you through accessing this service using a Compute instance provisioned by Terraform. We cover the setup of essential virtual cloud network (VCN) networking, security lists allowing access on key ports, and a three-node, managed PostgreSQL database system. The processing of required PSQL commands are automated through Terraform configuration.

This article also guides you through a step-by-step deployment of an OCI managed database with PostgreSQL service using Terraform with a comprehensive walkthrough for a seamless setup. The complete deployment architecture is shown in Figure 1.

^{Figure 2: The architectural design of a PostgreSQL managed database system provisioned by Terraform configuration}

If the selected region has a single availability domain, creating a highly available database system spreads all PostgreSQL instances across all fault domains in the region, irrespective of whether an availability domain-specific or regional subnet is defined.

Using Terraform configuration in folders to create the resources

To deploy the three-node, OCI-managed PostgreSQL resource using Terraform, we use the following steps:

1. Set up provider information: Create a file called providers.tf where you define the details needed to connect to OCI. This file includes things like OCI Terraform provider code with version, user OCID, tenancy OCID, and region in which resources are provisioned. You get this info from OCI by generating API keys securely.
    
2. Create VPC network: Create a file called vcn.tf to set up the network. Decide which network segments (subnets) are public and which are private. PostgreSQL should go in a private subnet. Add security list rules, one to the private subnet security list enabling PostgreSQL to be accessible through port 5432 and another on the public subnet security list enabling SSH access on port 22. The Compute instance used to connect to PostgreSQL is provisioned in the public subnet and made accessible from the internet. If these rules aren’t set properly, you can’t access the instance and connect PostgreSQL.
    
3. Create a Compute instance: Use a file named instance.tf to create a Compute instance to access the PostgreSQL database. This Compute instance sits in the public subnet of the VCN and connect you to PostgreSQL in the private subnet. You must also install the PSQL CLI tool in the instance to connect to the PostgreSQL database.
    
4. Set up PostgreSQL service: In a file called postgresql.tf, input the code necessary to create the actual OCI-managed PostgreSQL instance. Define traits like how many nodes it has, number of OCPU’s and amount of RAM for each node, the subnet in which to deploy, user credentials, availability domain, and region.
    
5. Define variables: Create a file named variables.tf tocontain the values used in the other files (vcn.tf, instance.tf, postgresql.tf). When you’ve set these values, run Terraform to begin the resource creations in the OCI tenancy.
    

Keep these files organized in your Terraform folder because every file in the Terraform folder relies on the others to make the PostgreSQL setup work as per the architecture. Terraform evaluates the contents of all files together and determine which resources to create, accommodating known resource dependencies. For example, a VCN subnet must exist before you can provision a compute resource within the subnet.

^{Figure 3: PostgreSQL folder with tf files}

Run terraform scripts

Using your terminal or command prompt, run the following commands to initialize Terraform and create resources within your OCI tenancy:

terraform init 
terraform plan 
terraform apply 

Connecting to the OCI PostgreSQL database

Upon completing the setup, log in to the Oracle Cloud Console. Under Databases, select PostgreSQL and locate your OCI PostgreSQL instance, noting the endpoint IP address. Next, under Compute and Instances, locate the public IP address of Compute instance equipped with the PSQL CLI. Access this Compute instance by SSH, utilizing its public IP address. Run the provided command within the instance to establish a connection with the OCI PostgreSQL database system.

psql --version
psql -h endpoint_ip -U admin_username -d postgres

^{Figure 4: PostgreSQL within the Oracle Cloud Console and connecting to the database from an OCI Compute instance}

Conclusion

By using Terraform, we can easily deploy the managed PostgreSQL database system and connect to your enterprise or any type of application. The OCI database with PostgreSQL provides cost effective, high performance, scalability, high availability, and durable solutions in OCI. To create a new PostgreSQL cluster, sign up for a free trial account.

For more on the PostgreSQL service architecture, see First Principles: Optimizing PostgreSQL for the cloud
For more on the service documentation, see OCI Database with PostgreSQL technical documentation