Creating PostgreSQL Database in OCI

 

To connect to your OCI PostgreSQL Database System (ORA11PG) using psql, you need a few details first.

Step 1: Get PostgreSQL Connection Details

In OCI Console:

Databases → PostgreSQL → ORA11PG

Check:

• Private IP Address (Example: 192.168.10.50)
• Port (Default: 5432)
• Database Name (Example: postgres)
• Username (Example: postgres or admin user created during provisioning)

---

Step 2: Verify Network Connectivity

From your Compute VM:

ping <postgres_private_ip>

Example:

ping 192.168.10.50

Check port connectivity:

nc -zv 192.168.10.50 5432

or

telnet 192.168.10.50 5432

Expected:

Connection to 192.168.10.50 5432 port [tcp/postgresql] succeeded!

If it fails:

✅ Check NSG/Security List

Allow:

Source	Protocol	Port
Compute Subnet CIDR	TCP	5432

---

Step 3: Install PostgreSQL Client (psql)

Oracle Linux / RHEL

sudo dnf install postgresql -y

Verify:

psql --version

Example:

psql (PostgreSQL) 17.6

---

Step 4: Connect Using psql

Syntax:

psql -h <host_ip> -p 5432 -U <username> -d <database>

Example:

psql -h 192.168.10.50 -p 5432 -U postgres -d postgres

Enter password.

---

Step 5: Verify Connection

After login:

SELECT version();

SELECT current_database();

SELECT current_user;

List databases:

\l

List schemas:

\dn

List tables:

\dt

Exit:

\q

---

OCI Practical Example

Suppose:

Component	Value
PostgreSQL IP	192.168.20.80
Port	5432
User	postgres
Database	postgres

Connection command:

psql -h 192.168.20.80 -p 5432 -U postgres -d postgres

---

Interview Question

Q: What is required to connect from a Compute Instance to OCI PostgreSQL Database System?

Answer:

1. Network connectivity between Compute and PostgreSQL DB System.
2. Security List or NSG allowing TCP 5432.
3. PostgreSQL client (psql) installed.
4. Valid database username and password.
5. Correct private IP/FQDN of the PostgreSQL server.

====================================================

To connect to your ORA11PG PostgreSQL instance on Oracle Cloud Infrastructure (OCI), you must use a client within the same Virtual Cloud Network (VCN) or set up a secure proxy, because OCI PostgreSQL database systems are deployed in private subnets and are not directly accessible from the public internet. [1]

---

Step 1: Gather Connection Details from OCI

1. Open your OCI Console, navigate to Databases > PostgreSQL > DB Systems. []
2. Click on your active instance name: ORA11PG. []
3. Look for the Connection details or Endpoint section and copy:
   • Endpoint (FQDN or Private IP) (e.g., 10.0.1.173 or ://oraclecloud.com)
   • Port (Default is 5432)
   • Database Name (Default is usually postgres)
   • Username (The administrator username you chose during creation) [, 2, 3, 4]

---

Step 2: Choose Your Connection Strategy

Option A: Connect from an OCI Compute VM (Simplest)

If you have a Linux or Windows Compute Instance running inside the same VCN and subnet (or a subnet that can route to the database), you can connect directly: [1]

1. Log into your OCI Compute VM using SSH. [1]
2. Install the psql client if it isn't already installed:
   • For Ubuntu/Debian: sudo apt install postgresql-client
   • For RHEL/Rocky Linux: sudo dnf install postgresql []
3. Run the standard connection string:
   bash

   psql -h <Your_Private_IP_or_FQDN> -p 5432 -U <Your_Username> -d <Your_Database_Name>
   

   Use code with caution.
   (Replace <Your_Private_IP_or_FQDN> with the endpoint gathered in Step 1). [1]

Option B: Connect from your Local Machine (Using OCI Bastion)

To connect directly from your personal laptop command line without a dedicated VM, use the OCI Bastion Service to create a Port Forwarding session: [1]

1. Navigate to Identity & Security > Bastions in OCI and create a Bastion if you don't have one. [1]
2. Create a Port Forwarding Session:
   • Target Resource: DB System (ORA11PG)
   • Target Private IP / FQDN: Enter your OCI PostgreSQL private endpoint.
   • Port: 5432 [1, 2, 3, 4, 5]
3. Copy the SSH command provided by OCI Bastion and run it on your local machine terminal:
   bash

   ssh -i <path_to_private_key> -N -L 5432:<database_private_ip>:5432 <bastion_user>@<bastion_endpoint>
   

   Use code with caution.
   [, 2]
4. Keep that terminal window open, open a new terminal on your local machine, and run:
   bash

   psql -h 127.0.0.1 -p 5432 -U <Your_Username> -d <Your_Database_Name>
   

   Use code with caution.
   [, 2]

---

Step 3: Network Troubleshooting Checklist

If your psql connection times out, verify your cloud firewall rules: [1]

• Ingress Security List: Go to the Security List of your PostgreSQL database's private subnet. Make sure there is an Ingress Rule allowing TCP traffic on port 5432 from your Compute VM's IP or the Bastion's subnet