Connect using the CLI for Public Cloud Databases for PostgreSQL

Objective

Public Cloud Databases allow you to focus on building and deploying cloud applications while OVHcloud takes care of the database infrastructure and maintenance in operational conditions.

This guide explains how to connect to a PostgreSQL database instance with a Command Line Interface (CLI).

Requirements

• A Public Cloud project in your OVHcloud account
• A PostgreSQL database running on your OVHcloud Public Cloud Databases (this guide can help you to meet this requirement)
• Configure your PostgreSQL instance to accept incoming connections

Concept

A PostgreSQL instance can be managed through multiple ways. One of the easiest, yet powerful ways, is to use a Command Line Interface (CLI), also known as a Console or Terminal.

To interact correctly with the PostgreSQL instance, we need to first install something called a PostgreSQL client, in order to connect and control the PostgreSQL service remotely. It's a Client-Server interaction.

Your service exposes two distinct entry points: the service port, on which each client connection opens a dedicated process on the server side, and, if you created one, the port of a connection pool, which shares client connections over a smaller number of server connections. This guide describes the direct connection to the service. The syntax is the same for a pool: only the port and the database name change.

OVHcloud Control Panel Access

• Direct link: All my Public Cloud projects
• Navigation path: Public Cloud > Select your project > Databases

Instructions

Installing the PostgreSQL client

You first need to install a software to interact with your PostgreSQL instance remotely. This official software can be installed on various client machines such as your own computer, a VPS or a virtual machine. The only rule is to be able to reach the public network (Internet) and have sufficient rights to install it.

In order to do so and depending on your configuration, you may need to follow official PostgreSQL documentation to install PostgreSQL client (referred to as psql).

Follow the steps here after selecting Windows, MacOS or Linux as operating system: https://www.postgresql.org/download/

As explained, the postgresql-client is often included by default.

Example with Linux/Debian:

We will now follow official PostgreSQL documentation to perform our first connection.

In your CLI, type psql --version. The result should look like this:

$ psql --version
psql (PostgreSQL) 18.0 (Ubuntu 18.0-1)

It means that psql is correctly installed and working properly. If you do not see something like this result, please go back to the previous step and reinstall PostgreSQL client.

Connect to your PostgreSQL instance

We will follow official PostgreSQL documentation: https://www.postgresql.org/docs/devel/app-psql.html.

To perform a connection, simply type psql followed by the Service URI copied before:

$ psql "postgres://<username>:<password>@<hostname>:<port>/defaultdb?sslmode=require"

Don't forget you need to modify the username, password, hostname and port. In our example, it will look like this:

$ psql "postgres://avnadmin:Mysup3rs3cur3p4ssw0rd@postgresql-ab123456-cd7891011.database.cloud.ovh.net:20184/defaultdb?sslmode=require"

Once connected correctly, you should see something similar to:

psql (18.0 (Ubuntu 18.0-1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

defaultdb=>

Verify the server identity

The Connection information section of the Dashboard tab provides your service's CA certificate, in the Certificate field. With this certificate, your client can check that it is talking to your service and not to an intermediary:

$ psql "postgres://<username>:<password>@<hostname>:<port>/defaultdb?sslmode=verify-full&sslrootcert=/path/to/ca.pem"

See the Secure the TLS connection guide for the full procedure, certificate renewal management and configuration examples per language.

PostgreSQL client usage

Once connected, you can manage your PostgreSQL instance with built-in PostgreSQL client methods.

Please follow the official PostgreSQL documentation.

To verify:

• \h CREATE DATABASE will display help on a specific command (here for CREATE DATABASE);
• \l+ will list all the databases;
• select * from pg_user; will display information about existing users.

In our example, it will look like this:

defaultdb=> \h CREATE DATABASE
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ STRATEGY [=] strategy ]
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ BUILTIN_LOCALE [=] builtin_locale ]
           [ ICU_LOCALE [=] icu_locale ]
           [ ICU_RULES [=] icu_rules ]
           [ LOCALE_PROVIDER [=] locale_provider ]
           [ COLLATION_VERSION = collation_version ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ]
           [ OID [=] oid ]

URL: https://www.postgresql.org/docs/18/sql-createdatabase.html

defaultdb=> \l+
                                                                List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |            Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+------------------------------------
 _aiven    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8269 kB | pg_default |
 defaultdb | avnadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 12 MB   | pg_default |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8253 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8229 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
(4 rows)

defaultdb=> select * from pg_user;
  usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig
-----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres  |       10 | t           | t        | t       | t            | ******** |          |
 _aiven    |    16399 | f           | t        | t       | f            | ******** |          |
 avnadmin  |    16400 | t           | f        | t       | t            | ******** |          |
(3 rows)

Go further

PostgreSQL capabilities

Getting started with Public Cloud Databases

Secure the TLS connection

Create and use connection pools

Manage users, roles and privileges

Visit the Github examples repository to find how to connect to your database with several languages.

For training or technical assistance implementing our solutions, contact your sales representative or visit our Professional Services page to request a quote and have your project analysed by our experts.

We want your feedback!

We would love to help answer questions and appreciate any feedback you may have.

Are you on Discord? Connect to our channel at https://discord.gg/ovhcloud and interact directly with the team that builds our databases service!