How to Host PostgreSQL on Oracle Cloud

How do I host PostgreSQL on Oracle Cloud Infrastructure quickly?

Shows how to provision, connect to, and query a PostgreSQL database instance hosted on Oracle Cloud Infrastructure (OCI).

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is the fastest way to spin up PostgreSQL in Oracle Cloud?

Use Oracle Cloud’s "PostgreSQL Database" service. It deploys a managed instance in minutes, handles backups, patching, and high-availability.

How do I create a PostgreSQL instance?

1. Log in to OCI.
2. Navigate to Databases ▶ PostgreSQL.
3. Click Create Cluster, choose region, shape, version, and admin password.
4.Click Create; note the public endpoint once provisioning finishes.

How do I connect with psql?

Open port 5432 in the VCN security list, install the PostgreSQL client locally, then run:
psql "host=<public-ip> user=admin dbname=postgres password=<password> sslmode=require"

What initial schema should I use?

Create common ecommerce tables so you can test quickly.The syntax section below shows full DDL.

How do I run test queries?

After loading sample data, run analytical queries such as total sales by customer or low-stock products.

Best practices for production?

• Enable automated backups and point-in-time recovery.
• Place the DB in a private subnet and access through a bastion host.
• Turn on automatic minor-version patching.
• Scale read replicas instead of vertical scaling when possible.

Common pitfalls?

See the mistakes section—most issues stem from security lists and misconfigured SSL.

Why How to Host PostgreSQL on Oracle Cloud is important

How to Host PostgreSQL on Oracle Cloud Example Usage


-- Total revenue per customer in the last 30 days
SELECT c.name,
       SUM(o.total_amount) AS last_30_day_revenue
FROM   Customers c
JOIN   Orders o ON o.customer_id = c.id
WHERE  o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP  BY c.name
ORDER  BY last_30_day_revenue DESC;

How to Host PostgreSQL on Oracle Cloud Syntax


-- Complete DDL for sample ecommerce schema
CREATE TABLE Customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE Products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    stock INT DEFAULT 0
);

CREATE TABLE Orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES Customers(id),
    order_date DATE DEFAULT CURRENT_DATE,
    total_amount NUMERIC(12,2)
);

CREATE TABLE OrderItems (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES Orders(id),
    product_id INT REFERENCES Products(id),
    quantity INT DEFAULT 1
);

-- psql connection
psql "host=<public-ip> port=5432 dbname=postgres user=admin password=**** sslmode=require"

Common Mistakes

Opening the wrong port—Many users only open port 22 (SSH). PostgreSQL needs port 5432. Fix by adding an ingress rule for TCP 5432 in the VCN security list or network security group.
Disabling SSL—Turning off SSL exposes data in transit. Always keep <code>sslmode=require</code> in your connection string or enforce TLS policies on the OCI side.

Frequently Asked Questions (FAQs)

Does Oracle Cloud offer a free tier for PostgreSQL?

Yes. OCI Free Tier includes an always-free PostgreSQL instance (up to 1 OCPU and 20 GB storage). Good for testing and small workloads.

Can I import an existing on-prem PostgreSQL database?

Use pg_dump on the source server and pg_restore or psql to load the dump file into the OCI instance. For large datasets, upload the dump to Object Storage and restore from there.