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:

  1. Create an instance for the PostgreSQL CLI.
  2. Install the PostgreSQL CLI on the instance.
  3. Connect to the database from the instance.
Note

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.

Next, install the PostgreSQL client on the instance.

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.

Install a version of the PostgreSQL CLI that's compatible with latest version of PostgreSQL supported by OCI Database with PostgreSQL.
Note

These steps assume resources in the VCN have access to the internet to download the PostgreSQL client.
  1. Connect to the instance using SSH.
  2. Download and install the PostgreSQL CLI. For more information, see https://www.postgresql.org/download.
Next, connect to the database using the 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.

Tip

For details on running PostgreSQL client commands, see the PostgreSQL reference manual.
  1. Connect to the instance using SSH.
  2. Open the navigation menu and click Databases. Under PostgreSQL, click Databases.
  3. (Optional) In the List scope section, under Compartment, select a compartment.
  4. Click the name of the database system.
  5. On the DBSystem details page, on the DB System Information tab, note the Endpoint IP address under Connection details.
  6. From the command line on the instance, run the following commands to sign in to the database:
    1. rm -rf ~/.pgpass

      This command removes the default password file, which has an incorrect password.

    2. psql -h <endpoint_IP> -U <admin_username> -d postgres

      Enter the same username and password you specified when creating the database secret.

    3. 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>"