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 OCI Managed Database with PostgreSQL Service using Terraform

Introduction

PostgreSQL, a widely acclaimed open-source object relational database, holds a prominent position due to its robust architecture and unwavering data integrity, making it a favored choice in enterprise landscapes. Its availability on Oracle Cloud Infrastructure (OCI) introduces a fully managed and high-performing database service with its intelligent sizing, cost-effective tuning, and durability. PostgreSQL supports enterprises, small and medium-sized businesses (SMBs), and a wide range of development environments, demonstrating its adaptability and robustness.

In this tutorial, we will learn how we can deploy three nodes of an OCI managed PostgreSQL database system in an OCI cloud tenancy and connect privately through a compute instance using Terraform script.

Objectives

Deploy an OCI managed database with PostgreSQL and a compute instance to connect the database service privately using Terraform scripts. We will be deploying following architecture in OCI using Terraform code.

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

Prerequisites

An Oracle Cloud Infrastructure tenancy.
A user account with the privileges to create users, add users to user groups, and create security policies.
The user should have OCI IAM permissions. For more information, see OCI Database with PostgreSQL Policies.

Task 1: Create Terraform script files in a folder

Create the providers.tf script.

Let’s begin with creating a providers.tf file. Create a local folder and copy the below code in the file created. The values for the tenancy_id, user_id, region, and so on will be replaced in the variables.tf file, which we will create in a later step. To get the OCI configurations values, generate API keys in OCI by accessing user settings, create an API key pair, securely download the private key, then use it along with the user and tenancy OCIDs in Terraform code for authentication to deploy OCI resources. For more information, see Generate an API Signing Key.
```
terraform {
    required_providers {
    oci =  {
        source = "oracle/oci"
        version = "5.22.0"
    }
  }
}
# Provider configuration for Tenancy
provider "oci" {
tenancy_ocid          = var.tenancy_id
user_ocid             = var.user_id
fingerprint           = var.api_fingerprint
private_key_path      = var.api_private_key_path
region                = var.region
}
```

Create the vcn.tf script.

We need a VCN network with private and public subnets to deploy and access the OCI PostgreSQL database system, remembering that PostgreSQL database will be deployed only on a private subnet. Create a file named vcn.tf using the following code. The code will make sure that only the VCN CIDR can access port 5432 for PostgreSQL, and it will allow access to the compute instance through SSH on port 22 within the security list. There is no need to worry about replacing the values here, you can do that in the variables.tf file.

resource oci_core_vcn psql_vcn_tf {
    #Required
    compartment_id = var.compartment_id

    #Optional
    cidr_block = var.vcn_cidr_block
    display_name = var.vcn_display_name
}

resource oci_core_subnet public_subnet {
    #Required
    cidr_block = var.public_subnet_cidr_block
    compartment_id = var.compartment_id
    vcn_id = oci_core_vcn.psql_vcn_tf.id

    #Optional
    display_name = var.public_subnet_display_name
    route_table_id = oci_core_route_table.tf_public_route_table.id
}

resource oci_core_subnet private_subnet {
    #Required
    cidr_block = var.private_subnet_cidr_block
    compartment_id = var.compartment_id
    vcn_id = oci_core_vcn.psql_vcn_tf.id

    display_name = var.private_subnet_display_name
    route_table_id = oci_core_route_table.tf_private_route_table.id
    prohibit_internet_ingress = true
    security_list_ids = [oci_core_security_list.tf_private_security_list.id]
}

resource oci_core_internet_gateway vcntf_igw {
  vcn_id       = oci_core_vcn.psql_vcn_tf.id
  compartment_id = var.compartment_id
  display_name = var.internet_gateway_name
}

resource oci_core_nat_gateway tf_nat_gateway {
  vcn_id       = oci_core_vcn.psql_vcn_tf.id
  compartment_id = var.compartment_id
  display_name = example-ngw
  # Add route tables to direct traffic through this NAT gateway
}

data oci_core_services test_services {
}

variable create_service_gateway {
  description = whether to create a service gateway. If set to true, creates a service gateway.
  default     = true
  type        = bool
}

data oci_core_services all_oci_services {
  filter {
    name   = name
    values = [All .* Services In Oracle Services Network]
    regex  = true
  }
  count = var.create_service_gateway == true ? 1 : 0
}

resource oci_core_service_gateway service_gateway {
  compartment_id = var.compartment_id
  display_name   = var.service_gateway_displayname
  services {
    service_id = lookup(data.oci_core_services.all_oci_services[0].services[0], id)
  }
  vcn_id = oci_core_vcn.psql_vcn_tf.id
  count = var.create_service_gateway == true ? 1 : 0
}

resource oci_core_security_list tf_public_security_list {
  vcn_id         = oci_core_vcn.psql_vcn_tf.id
  compartment_id = var.compartment_id
  display_name   = var.public_subnet_security_list_display_name

  ingress_security_rules {
    protocol    = 6  # TCP protocol for SSH
    source      = 0.0.0.0/0  # Allow inbound traffic from all sources
    tcp_options {
            #Optional
            max = 22
            min = 22
        }
    description = Allow SSH from all sources
  }
}

resource oci_core_security_list tf_private_security_list {
  vcn_id         = oci_core_vcn.psql_vcn_tf.id
  compartment_id = var.compartment_id
  display_name   = var.private_subnet_security_list_disply_name

  ingress_security_rules {
    protocol    = 6  # TCP protocol to connect Postgress service from compute instance in public subnet
    source      = oci_core_vcn.psql_vcn_tf.cidr_block  # Allow inbound traffic from CIDR Block of VCN sources
    tcp_options {
            #Optional
            max = 5432
            min = 5432
        }
    description = Allow psql service connections from all ranges cidr vcn
  }
}

resource oci_core_route_table tf_public_route_table {
  vcn_id     = oci_core_vcn.psql_vcn_tf.id
  compartment_id = var.compartment_id
  display_name = var.public_subnet_route_table_display_name
  route_rules {
    // Define route rules for public subnet
    network_entity_id = oci_core_internet_gateway.vcntf_igw.id
    destination = 0.0.0.0/0
    destination_type = CIDR_BLOCK
  }
}

resource oci_core_route_table tf_private_route_table {
  vcn_id     = oci_core_vcn.psql_vcn_tf.id
  compartment_id = var.compartment_id
  display_name = var.private_subnet_route_table_display_name
  route_rules {
    // Define route rules for private subnet
    network_entity_id = oci_core_nat_gateway.tf_nat_gateway.id
    destination = 0.0.0.0/0
    destination_type = CIDR_BLOCK
  }
  route_rules {
    network_entity_id = oci_core_service_gateway.service_gateway.0.id
    destination = all-iad-services-in-oracle-services-network
    destination_type  = SERVICE_CIDR_BLOCK
  }
  }

resource oci_core_route_table_attachment public_route_table_attachment {
  #Required
  subnet_id = oci_core_subnet.public_subnet.id
  route_table_id =oci_core_route_table.tf_public_route_table.id
}

resource oci_core_route_table_attachment private_route_table_attachment {
  #Required
  subnet_id = oci_core_subnet.private_subnet.id
  route_table_id =oci_core_route_table.tf_private_route_table.id
  depends_on = [oci_core_service_gateway.service_gateway]
}

Create the instance.tf script.

To access the PostgreSQL database, we need a compute instance in the public subnet. We will connect to the instance and then access PostgreSQL because it is accessible only through the private subnet. To do this, create a file called instance.tf using the following code. You can easily update the compute variable values in the variables.tf file later on.

# Resources
data "oci_identity_availability_domains" "ads" {
  compartment_id = var.compartment_id
}

resource "oci_core_instance" "tf_compute" {
  # Required
  availability_domain = data.oci_identity_availability_domains.ads.availability_domains[0].name
  compartment_id      = var.compartment_id
  shape               = var.compute_shape
  source_details {
    source_id         = var.source_operating_system_image_id
    source_type       = "image"
  }
  display_name        = var.compute_instance_display_name
  shape_config {
    ocpus         = var.compute_cpus
    memory_in_gbs = var.compute_memory_in_gbs
  }
  create_vnic_details {
    subnet_id = oci_core_subnet.public_subnet.id
    assign_public_ip  = true
  }
  metadata = {
    ssh_authorized_keys = file(var.compute_ssh_authorized_keys)
  }
  preserve_boot_volume = false
  provisioner "remote-exec" {
    inline = [
      "sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm",
      "sudo dnf -qy module disable postgresql",
      "sudo dnf install -y postgresql16-server",
      "sudo /usr/pgsql-16/bin/postgresql-16-setup initdb",
      "sudo systemctl enable postgresql-16",
      "sudo systemctl start postgresql-16"
    ]
    connection {
      type        = "ssh"
      host        = self.public_ip
      user        = "opc"
      private_key = file(var.api_private_key_for_ssh)
    }
  }

}

# Outputs
output "compute_id" {
  value = oci_core_instance.tf_compute.id
}

output "compute_state" {
  value = oci_core_instance.tf_compute.state
}

output "compute_public_ip" {
  value = oci_core_instance.tf_compute.public_ip
}

Create the postgresql.tf script.

Let’s set up the OCI PostgreSQL database system by adding the Terraform code into a file named postgresql.tf. This code details the system’s configuration, including shape, cores, and nodes. For an optimized setup, consider a three-node database system: one primary node and two replicas across different availability zones. Start by creating the postgresql.tf file and inserting the provided code. You can easily add the PostgreSQL database’s variable values in the variables.tf file to meet your specific requirements.

resource "oci_psql_db_system" "test_db_system" {
    #Required
    compartment_id = var.compartment_id
    db_version = var.db_system_db_version
    display_name = var.db_system_display_name
    network_details {
        #Required
        subnet_id = oci_core_subnet.private_subnet.id
    }
    shape = var.db_system_shape
    storage_details {
        #Required
        is_regionally_durable = var.db_system_storage_details_is_regionally_durable
        system_type = var.db_system_storage_details_system_type
        #Optional
        # availability_domain = var.db_system_storage_details_availability_domain
        # iops = var.db_system_storage_details_iops
    }
    credentials {
        #Required
        password_details {
            #Required
            password_type = var.db_system_credentials_password_details_password_type
            #Optional
            password = var.db_system_credentials_password_details_password
        }
        username = var.db_system_credentials_username
    }
    instance_count = var.db_system_instance_count
    instance_memory_size_in_gbs = var.db_system_instance_memory_size_in_gbs
    instance_ocpu_count = var.db_system_instance_ocpu_count

}

Create the variables.tf script.

In this section, the user needs to adjust and update the values for the resources to be created within their OCI tenancy. Create a variables.tf file and add the following code. Remember, this is the final file to be created in the same folder. Carefully review and modify each variable according to your requirements. Some values will be pre-filled in the ‘value’ section of each code, while others, specific to your needs, will require your input. Once all values have been added or modified in the file, proceed to the execution phase.

# Provider identity parameters - Replace these values from API Key Values from OCI User

variable "api_fingerprint" {
  description = "Fingerprint of OCI API private key for Requestor Tenancy"
  type        = string
  default     = ""
}

variable "api_private_key_path" {
  description = "Path to OCI API private key used for Requestor Tenancy"
  type        = string
  default     = ""
}

variable "tenancy_id" {
  description = "Tenancy ID where to create resources for Requestor Tenancy"
  type        = string
  default     = ""
}

variable "user_id" {
  description = "User ID that Terraform will use to create resources for Requestor Tenancy"
  type        = string
  default     = ""
}

variable "region" {
  description = "OCI region where resources will be created for Requestor Tenancy"
  type        = string
  default     = "us-ashburn-1"  # example value
  # check this document, if you want to use different region - https://docs.oracle.com/en-us/iaas/Content/General/Concepts/regions.htm#About
}


# compartment OCID - Replace these values

variable "compartment_id" {
  description = "Compartment ID where to create resources for Requestor Tenancy"
  type        = string
  default     = ""
}

variable "db_system_db_version" {
  description = "Version"
  type = number
  default = 14
}

variable "db_system_display_name" {
  description = "postgress db service name"
  type = string
  default = "psqlfromterraform" # example value
}


variable "db_system_shape" {
    description = "shape"
    type = string
    default = "PostgreSQL.VM.Standard.E4.Flex.4.64GB"  # example value
    #change the shape value as per your requirements
}

variable "db_system_instance_count" {
  description = "instance count"
  type = number
  default = 3  # example value
}

variable "db_system_instance_memory_size_in_gbs" {
  description = "RAM"
  type = number
  default = 64  # example value
}

variable "db_system_instance_ocpu_count" {
  description = "OCPU count"
  type = number
  default = 4  # example value
}

variable "db_system_storage_details_is_regionally_durable" {
  description = "regional"
  type = bool
  default = true
}
variable "db_system_credentials_password_details_password_type" {
    description = "type"
    type = string
    default = "PLAIN_TEXT"

}

variable "db_system_credentials_password_details_password" {
  description = "password"
  type = string
  default = ""
}

variable "db_system_credentials_username" {
  description = "username"
  type = string
  default = "admin" # example value
}

variable "db_system_storage_details_system_type" {
  description = "type"
  type = string
  default = "OCI_OPTIMIZED_STORAGE"
}



# OCI VCN parameters - psql instance deployed on this

variable "vcn_cidr_block" {
  description = "vcn cidr"
  type = string
  default = "172.16.0.0/16" # example value
}

variable "vcn_display_name" {
  description = "vcn name"
  type = string
  default = "vcn-from-tf-psql" # example value
}

variable "public_subnet_cidr_block" {
    description = "subnet cidr range"
    type = string
    default = "172.16.1.0/24" # example value
}

variable "private_subnet_cidr_block" {
    description = "subnet cidr range"
    type = string
    default = "172.16.2.0/24" # example value
}

variable "public_subnet_display_name" {
  description = "public subnet name"
  type = string
  default = "public-subnet" # example value

}

variable "private_subnet_display_name" {
  description = "public subnet name"
  type = string
  default = "private-subnet" # example value

}

variable "internet_gateway_name" {
  description = "internet gateway name"
  type = string
  default = "internetgateway" # example value
}

variable "service_gateway_displayname" {
  description = "Service Gateway Display Name"
  type = string
  default = "servicegateway" # example value
}

variable "public_subnet_security_list_display_name" {
  description = "Public Subnet Security List Display Name"
  type = string
  default = "public_subnet_security_list" # example value
}

variable "private_subnet_security_list_display_name" {
  description = "Public Subnet Security List Display Name"
  type = string
  default = "public_subnet_security_list" # example value
}

variable "public_subnet_route_table_display_name" {
  description = "Public Subnet Route table Display Name"
  type = string
  default = "public_subnet_route_table" # example value
}

variable "private_subnet_route_table_display_name" {
  description = "Public Subnet Route table Display Name"
  type = string
  default = "private_subnet_route_table" # example value
}


# OCI Compute Instance parameters - We will use this instance to connect postgreSQL db instance

variable "compute_shape" {
  type    = string
  default = "VM.Standard.E4.Flex" # example value
}

variable "compute_cpus" {
  type    = string
  default = "1" # example value
}

variable "compute_memory_in_gbs" {
  type    = string
  default = "1" # example value
}

variable "compute_ssh_authorized_keys" {
  type = string
  default = ""
}

variable "api_private_key_for_ssh" {
  type = string
  default = ""
}

variable "source_operating_system_image_id" {
  description = "Oracle Linux 8 image ocid"
  type = string
  default = "ocid1.image.oc1.iad.aaaaaaaaszr5wpipg6qskiol3fhbitm56qdmumpbcpv6irzxuofi2nfmlhma" # example value
  # if you change the region , then change the default value from the region you have selected from this document -https://docs.oracle.com/en-us/iaas/images/image/998f1273-d4fd-4e16-8673-dd2517ddd724/
}

variable "compute_instance_display_name" {
  description = "display name of the compute name"
  type = string
  default = ""
}

After creating the required files and adjust the variables.tf file with the necessary values, your folder structure will reflect the set up displayed in the following screenshot.

tf-folder

Task 2: Execute Terraform Scripts

Using your terminal or command prompt, run the following commands to initialize Terraform and create resources within your OCI tenancy. These commands will initialize Terraform and deploy the specified resources in your OCI tenancy.

terraform init

terraform plan

terraform apply

postgreSQLdb_oci_console

Task 3: Connect to OCI PostgreSQL Database

Upon completion, navigate to the OCI Console to locate OCI PostgreSQL and a compute instance preloaded with the psql client. Access the compute instance via SSH using its public IP, then execute the provided command to establish a connection with the OCI PostgreSQL database system (both SSH port 22 and database port 5432 are created automatically through the vcn.tf script; no action is needed for port management). Update the private endpoint IP, obtainable from the OCI PostgreSQL database console, along with the username and password you have specified in the variable.tf file. Once you execute the command, it will prompt for the password to connect.

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

After execution of the above following commands, users should be able to see the connection established to the managed PostgreSQL database.

psqldb_access_from_instance

The user successfully deployed three nodes of OCI managed PostgreSQL database and connected them privately using a compute instance.

Acknowledgments

Author - Akarsha I K (Cloud Architect)

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 OCI Managed Database with PostgreSQL Service using Terraform

F91015-01

January 2024

Deploy OCI Managed Database with PostgreSQL Service using Terraform

Introduction

Objectives

Prerequisites

Task 1: Create Terraform script files in a folder

Task 2: Execute Terraform Scripts

Task 3: Connect to OCI PostgreSQL Database

Related Links

Acknowledgments

More Learning Resources