Connect to a PostgreSQL database in OCI Database with PostgreSQL

甲骨文 recently announced the General Availability of Oracle Cloud Infrastructure (OCI) Database with PostgreSQL version 14.9. OCI Database with PostgreSQL is a fully managed PostgreSQL-compatible service on Oracle Cloud. In this post we will discuss connecting to the PostgreSQL database using PSQL Cli - psql.

To start with create an OCI Database with PostgreSQL database system -

Login to OCI, open the navigation menu and click Databases. Under PostgreSQL, click Databases. Click Create PostgreSQL DB system. Select Create new DBsystem, then click Next.

Enter the DBSystem configuration details -

Under Network configuration, select the Virtual Cloud Network (VCN) in which you want to create the database. Select the private subnet of the selected VCN.

The DB system endpoint uses a private IP address and is not directly accessible from the internet. Hence, it is mandatory to select a Private Subnet.

Next, enter the DBsystem administrator credentials to the database - username and password.

After configuring the database system, click Next.

Review the database system configuration, then click Create.

Create an OCI Compute Instance to act as Client -

Create an OCI Compute Instance using the following link.

While creating the Compute instance, ensure to select the VCN that was selected for Database, as the Primary network for the Compute instance. Select the Public Subnet as Subnet.

Connect to the Compute instance using SSH.

Install PostgreSQL client using following commands -

$ sudo apt-get update
$ apt list | grep postgresql-client-14
$ sudo apt install postgresql-client-14        

Connect to the database -

$ psql -h <endpoint_IP> -U <admin_username> -d Postgres
postgres=> \l
postgres=> select version();        

Or use the following command to connect to the database as a database user -

$ psql "sslmode=require host=<endpoint_IP> dbname=postgres user=<user_in_postgres> password=<user_password>"