Connecting to a Database
Connect to a PostgreSQL database in OCI Database with PostgreSQL.
Database system endpoints aren't directly accessible from the internet. Applications that need to connect to the database should have connectivity to the VCN and subnet of the database system. For quick testing, you can have a Compute instance in the same private VCN and subnet of the database system, SSH into the instance, and then connect to the database using a PSQL client. Use the following instructions to get started with setting up such a Compute instance:
If you're connecting to the database using a Bastion session, you can connect directly from a local network to the database. You don't need to configure a Compute instance to connect from. Bastion sessions aren't permanently available, so we recommend using an instance as described.
Creating an Instance for the PostgreSQL CLI
Create and configure an instance so that the PostgreSQL CLI tool can be used to connect to OCI Database with PostgreSQL.
Create an instance to host the PostgreSQL client that can connect to the database node's private endpoint.
When you create the instance:
Download the SSH key pair.Caution
Anyone who has access to the private key can connect to the instance. Store the private key in a secure location.
- Choose the VCN and subnet used when you created the database system.
Installing the PostgreSQL CLI on an Instance
Install the PostgreSQL CLI tool on an instance with access to the OCI Database with PostgreSQL database system.
These steps assume resources in the VCN have access to the internet to download the PostgreSQL client.
Connecting to an OCI Database with PostgreSQL with the PostgreSQL CLI
Use the PostgreSQL CLI tool to OCI Database with PostgreSQL.
Before you connect to the database with the PostgreSQL CLI, you must create an instance to host the tool, and install the tool itself.
For details on running PostgreSQL client commands, see the PostgreSQL reference manual.
- Connect to the instance using SSH.
- Open the navigation menu and click Databases. Under PostgreSQL, click Databases.
- (Optional) In the List scope section, under Compartment, select a compartment.
- Click the name of the database system.
- On the DBSystem details page, on the DB System Information tab, note the Endpoint IP address under Connection details.
From the command line on the instance, run the following commands to sign in to the database:
rm -rf ~/.pgpass
This command removes the default password file, which has an incorrect password.
psql -h <endpoint_IP> -U <admin_username> -d postgres
Enter the same username and password you specified when creating the database secret.
Or, use this command to sign in as a database user:
psql "sslmode=require host=<endpoint_IP> dbname=postgres user=<user_in_postgres> password=<user_password>"