Deploy a PostgreSQL Database System on OCI with Terraform — Step-by-Step Guide

Provisioning cloud infrastructure through the console works — until things get big. It’s slow, hard to manage, and doesn’t scale well. That’s where OpenTofu (Infrastructure as Code) comes in — it’s faster, traceable, and fully repeatable.

In this guide, I’ll walk you through how to use OpenTofu to set up a PostgreSQL Database System on Oracle Cloud Infrastructure (OCI).

🔧 Prerequisites

Before diving in, make sure you have the following set up on your machine:

1. Install OCI CLI

The OCI CLI (Command Line Interface) is a powerful tool that allows you to interact with Oracle Cloud from your terminal.

bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"

Once installed, verify it:

oci --version

2. Set Up OCI CLI Configuration

Run the following command to configure your OCI CLI:

oci setup bootstrap

This will walk you through:

• Selecting your region
• Creating an API key pair
• Uploading the public key to your OCI account
• Generating the configuration file (~/.oci/config)

3. Verify OCI CLI Configuration

To verify that your CLI is configured correctly, run:

oci iam region list

If it returns a list of regions, you’re good to go!

4. Install OpenTofu

Follow the official OpenTofu installation guide for your operating system.

Once installed, verify it:

tofu --version

📁 Organizing Your OpenTofu Files

Let’s start by organizing our project. We’ll split the configuration into multiple files so it’s clean and easy to manage.

Here’s the folder structure we’ll use:

postgresql-oci/
├── main.tf
├── network.tf
├── secrets.tf
├── vars.tf
├── outputs.tf
└── terraform.tfvars (optional)

Each file will have its own responsibility — from setting up the database to managing secrets and networking.

Step-by-Step OpenTofu Configuration

1. main.tf

This main.tf sets up a managed PostgreSQL DB system on OCI with configurable instance count, storage IOPS, version, and secure credential management via OCI Vault.

terraform {
  required_providers {
    oci = {
      source  = "oracle/oci"
      version = ">= 5.0.0"
    }
  }
  required_version = ">= 1.0.0"
}

provider "oci" {
  region              = var.region
  config_file_profile = "DEFAULT"
}
resource "oci_psql_db_system" "postgres_db_system" {
  display_name   = var.postgres_db_system_name
  compartment_id = var.provider_id
  db_version     = var.psql_version
  shape          = var.postgres_shape_name
  instance_count = var.instance_count
  credentials {
    password_details {
      password_type = "PLAIN_TEXT"
      password      = base64decode(data.oci_secrets_secretbundle.admin_password_bundle.secret_bundle_content[0].content)
    }
    username = var.administrator_login
  }
  network_details {
    subnet_id = oci_core_subnet.postgres_subnet.id
  }
  storage_details {
    is_regionally_durable = false
    system_type           = var.system_type
    availability_domain   = var.availability_domain
    iops                  = var.iops
  }
}

What this does:

• Provider Configuration: Uses the OCI provider (version >= 5.0.0) with the DEFAULT profile and sets the region.
• PostgreSQL DB System: Creates a managed PostgreSQL database using oci_psql_db_system with the specified shape, version, and instance count.
• Credentials: Sets the admin username and pulls the admin password securely from OCI Vault (stored as a base64-encoded secret).
• Network: Places the DB system inside a private subnet using network_details.
• Storage: Configures system_type, availability_domain, and iops for storage performance tuning.

2. network.tf

This file sets up the network backbone for your PostgreSQL DB system — a Virtual Cloud Network (VCN) and a private subnet to securely host the database.

resource "oci_core_virtual_network" "postgres_vcn" {
  compartment_id = var.provider_id
  cidr_block     = "10.0.0.0/16"
  display_name   = "postgres-vcn"
  dns_label      = "postgresvcn"
}

resource "oci_core_subnet" "postgres_subnet" {
  compartment_id             = var.provider_id
  vcn_id                     = oci_core_virtual_network.postgres_vcn.id
  cidr_block                 = "10.0.1.0/24"
  display_name               = "postgres-subnet"
  prohibit_public_ip_on_vnic = true
  dns_label                  = "postgressub"
}

What this does:

• VCN: Creates a Virtual Cloud Network with a 10.0.0.0/16 CIDR block — this is your isolated network in OCI.
• Subnet: Creates a private subnet (10.0.1.0/24) inside the VCN. The prohibit_public_ip_on_vnic = true ensures the database is not exposed to the public internet.

3. secrets.tf

This file handles secret management using OCI Vault. We generate a strong random password, store it securely, and later retrieve it for our PostgreSQL DB system.

resource "oci_kms_vault" "postgres_vault" {
  compartment_id = var.provider_id
  display_name   = "${var.postgres_db_system_name}-vault"
  vault_type     = "DEFAULT"
}

resource "oci_kms_key" "postgres_key" {
  compartment_id      = var.provider_id
  display_name        = "${var.postgres_db_system_name}-key"
  management_endpoint = oci_kms_vault.postgres_vault.management_endpoint
  key_shape {
    algorithm = "AES"
    length    = 32
  }
}
resource "random_password" "db_admin_password" {
  length           = 16
  special          = true
  override_special = "!@$_+-=?"
  upper            = true
  lower            = true
  numeric          = true
}
resource "oci_vault_secret" "admin_password_secret" {
  compartment_id = var.provider_id
  secret_name    = "${var.postgres_db_system_name}-postgres-db-secret"
  vault_id       = oci_kms_vault.postgres_vault.id
  key_id         = oci_kms_key.postgres_key.id
  secret_content {
    content_type = "BASE64"
    content      = base64encode(random_password.db_admin_password.result)
  }
}
data "oci_secrets_secretbundle" "admin_password_bundle" {
  secret_id = oci_vault_secret.admin_password_secret.id
}

What this does:

• Vault: Creates an OCI KMS Vault to securely store secrets.
• Key: Creates an AES-256 encryption key inside the vault.
• Random Password: Generates a 16-character password with uppercase, lowercase, numeric, and special characters (!@$_+-=?).
• Vault Secret: Stores the generated password in OCI Vault as a base64-encoded secret.
• Secret Bundle: Retrieves the stored password using oci_secrets_secretbundle for use in the PostgreSQL DB system configuration.

4. vars.tf

This file declares all the input variables used across the OpenTofu configuration. These let you customize the deployment without modifying the main code.

variable "region" {
  description = "Region of the tenancy"
  type        = string
}

variable "provider_id" {
  description = "OCI Compartment ID"
  type        = string
}
variable "postgres_shape_name" {
  description = "Shape of the PostgreSQL instance"
  type        = string
}
variable "availability_domain" {
  description = "Availability domain to install the PostgreSQL instance"
  type        = string
}
variable "administrator_login" {
  description = "The admin username for PostgreSQL database"
  type        = string
  default     = "postgresadmin"
}
variable "postgres_db_system_name" {
  description = "Name of the PostgreSQL DB system"
  type        = string
}
variable "psql_version" {
  description = "Version of the postgres database"
  type        = number
  default     = 15
}
variable "iops" {
  description = "The storage IOPS for the Postgres Database system"
  type        = number
  default     = 75000
  validation {
    condition     = var.iops >= 75000
    error_message = "IOPS value must be greater than or equal to 75000."
  }
  validation {
    condition     = var.iops <= 750000
    error_message = "IOPS value must be less than or equal to 750000."
  }
}
variable "system_type" {
  description = "System type of the Postgres database"
  type        = string
  default     = "OCI_OPTIMIZED_STORAGE"
}
variable "instance_count" {
  description = "Count of the instance"
  type        = number
  default     = 1
}

Here’s a quick summary of all variables:

5. outputs.tf

This file defines the outputs that will be displayed after tofu apply completes. It exposes critical database details like the endpoint, admin credentials, and metadata.

output "endpoints_postgres" {
  value = oci_psql_db_system.postgres_db_system.network_details
}

output "db_admin_user" {
  value = oci_psql_db_system.postgres_db_system.admin_username
}
output "db_password" {
  value     = data.oci_secrets_secretbundle.admin_password_bundle.secret_bundle_content[0].content
  sensitive = true
}
output "db_port" {
  value = "5432"
}
output "db_url" {
  value = oci_psql_db_system.postgres_db_system.network_details[0].primary_db_endpoint_private_ip
}
output "server_name" {
  value = oci_psql_db_system.postgres_db_system.display_name
}
output "server_id" {
  value = oci_psql_db_system.postgres_db_system.id
}
output "db_version" {
  value = oci_psql_db_system.postgres_db_system.db_version
}

6. terraform.tfvars

Create a terraform.tfvars file to supply the actual values for the variables. You can also create separate files like stage.tfvars or prod.tfvars and use the -var-file flag to switch between environments.

region                  = "ap-mumbai-1"
provider_id             = "ocid1.tenancy.oc1..xxxxx"   # Replace with your Compartment OCID
availability_domain     = "AZat:AP-MUMBAI-1-AD-1"
postgres_shape_name     = "PostgreSQL.VM.Standard.E4.Flex.2.32GB"
postgres_db_system_name = "test-postgres-db"
psql_version            = 15
iops                    = 75000
instance_count          = 1

Note: You can find your Compartment OCID from the OCI Console under Identity > Compartments. The Availability Domain can be found under Governance > Tenancy Details.

⚙️ Installing PostgreSQL Database System with OpenTofu

Now that all the OpenTofu files are ready, let’s deploy!

Initialize OpenTofu

This downloads the required providers (OCI, random) and prepares the working directory.

tofu init

Review the Deployment Plan

Before applying, always review the plan to see what OpenTofu will create:

tofu plan -var-file="terraform.tfvars"

This shows you a preview of all the resources that will be created. Review the output carefully before proceeding.

Apply the Configuration

When you’re satisfied with the plan, apply the configuration:

tofu apply -var-file="terraform.tfvars"

OpenTofu will prompt you to confirm. Type **yes** to proceed. This will create:

• A Virtual Cloud Network (VCN) with a private subnet
• An OCI KMS Vault with an encryption key
• A securely generated admin password stored in the vault
• A managed PostgreSQL DB system with your specified configuration

📋 Checking PostgreSQL Database Outputs

Once the deployment is complete, you can check the outputs to get the connection details for your PostgreSQL database.

Run the following command:

tofu output

This will display all the connection details for your PostgreSQL DB system:

• Database Endpoint — The private IP/hostname for connecting to the database
• Admin Username — The login credential for the admin user
• Admin Password — Securely generated and stored in OCI Vault
• Port — 5432 (default PostgreSQL port)
• Other Metadata — Server name, ID, and PostgreSQL version

Conclusion

That’s how you can easily create a production-ready PostgreSQL database system on OCI using OpenTofu.

You can find the full source code in the opentofu-modules GitHub repository. If you find it useful, give it a star!

For a hands-free deployment experience, check out zop.dev — it takes care of infrastructure management so you can focus on building your application.

Thanks for reading! If you found this guide helpful, give it a clap and share it with your team.