IBM Cloud Docs
Connecting with psql

Connecting with psql

Use psql for direct interaction and monitoring of the data structures that are created within the database. psql is also useful for testing and monitoring queries and performance, installing and modifying scripts, and other management activities.

The admin user comes with the PostgreSQL default role pg_monitor, that allows access to PostgreSQL monitoring views and functions. By default, the admin user does not have permissions on objects that are created by other users.

You must set the admin password before you use it to connect to the database. For more information, see the Setting the Admin Password page.

Installing psql

To use psql, the PostgreSQL client tools need to be installed on the local system. They can be installed with the full PostgreSQL package that is provided from postgresql.org, as a package from your operating system's package manager.

For more information about psql, see the PostgreSQL documentation.

Most instructions for installing the PostgreSQL tools assume you want the database installed too. It's a reasonable assumption if you're dealing with users who don't have access to PostgreSQL in the cloud or on a remote server.

Here are steps for installing psql as a stand-alone tool on macOS, Linux, and Windows.

Installing psql on macOS with Homebrew

We recommend Homebrew as a package manager for macOS. With Homebrew, you are able to install numerous applications, usually with the programs available in /usr/local/bin. Homebrew's package for the PostgreSQL client tools is the libpq package. Brew makes it easy to install:

brew install libpq

There's a small catch though: libpq won't install itself in the /usr/local/bin directory. To make that happen, you need to run:

brew link --force libpq

Which will symlink (a file that points to another file or folder) all the tools, not just libpq, into the /usr/local/bin directory.

Installing postgresql-client on Ubuntu

Linux systems, unlike macOS, have a package manager built in. For Ubuntu (and Debian-based distributions) thats's the apt command. The PostgreSQL client is distributed in the appositely named postgresql-client. To install it, run a command like:

sudo apt-get install postgresql-client

This will install the PostgreSQL client.

Installing postgresql-client on Red Hat Enterprise Linux

For Red Hat Enterprise Linux (or RHEL as it's usually written), there's a little more setup than with Ubuntu. For RHEL, the package manager is Yum.

First, you need to point Yum to the PostgreSQL repository, like this:

sudo yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-redhat10-10-2.noarch.rpm

Yum goes to that URL and configures itself to use that package repository. With that done, you can add packages by name:

sudo yum install postgresql15

This command installs just the client packages. If you are wondering where to find that repository URL, head to Linux Downloads (Red Hat Family) where you'll find a form that will let you select the PostgreSQL version, platform and architecture and it'll give you the appropriate instructions for that Red Hat variant - that includes CentOS, Scientific Linux, and Oracle Enterprise Linux. It also includes Fedora. Fedora's default repositories already have a PostgreSQL client available from them. So For Fedora 27 and 28 and later, install the PostgreSQL client from the terminal with:

sudo dnf install postgresql.x86_64

Installing psql on Windows

For Windows, use the PostgreSQL installer from Enterprise DB. It's a full installation package for PostgreSQL on Windows but you can set it to only install the command line tools like psql. Select your PostgreSQL and Windows versions. Once the executable file is downloaded, run it. Select only the Command Line Tools, if you don't need the server installed.

After it installs, you set up your Windows environment variables so that you can use the psql client in the command prompt. Go to the Control Panel > System and Security > System and select* Advanced system settings*. From there you see a box called System Properties. Select Environment Variables. A window appears with the two sets of environment variables. In the top set, marked "User variables for...", select the PATH entry and then click the Edit button. An edit window will appear. Click New and add the path to the psql client. Your path will depend on where PostgreSQL installed, but typically that would be:

C:\Program Files\PostgreSQL\<POSTGRES_VERSION>\bin

After that, click OK a couple of times to go back to the desktop. Start a new Command Prompt and you should be able to run psql.

psql Connection Strings

Connection strings are displayed in the Endpoints panel of your deployment's Overview, and can also be retrieved from the Cloud Databases CLI plug-in, and the API.

The information that you need to make a connection with psql is in the "cli" section of your connection strings. The table contains a breakdown for reference.

Table 1. psql/cli connection information
Field Name Index Description
Bin The recommended binary to create a connection; in this case it is psql.
Composed A formatted command to establish a connection to your deployment. The command combines the Bin executable, Environment variable settings, and uses Arguments as command-line parameters.
Environment A list of key/values you set as environment variables.
Arguments 0... The information that is passed as arguments to the command shown in the Bin field.
Certificate Base64 A self-signed certificate that is used to confirm that an application is connecting to the appropriate server. It is base64 encoded.
Certificate Name The allocated name for the self-signed certificate.
Type The type of package that uses this connection information; in this case cli.
  • 0... indicates that there might be one or more of these entries in an array.

Creating a command-line client connection

Before creating a command-line client connection, ensure that you have set the Admin password for your deployment.

The ibmcloud cdb deployment-connections command handles everything that is involved in creating a command-line client connection. For example, to connect to a deployment named "example-postgres", use the following command:

ibmcloud cdb deployment-connections <INSTANCE_NAME_OR_CRN> --start

or

ibmcloud cdb cxn <INSTANCE_NAME_OR_CRN> -s

The command prompts for the admin password and then runs the psql command-line client to connect to the database.

If you have not installed the Cloud Databases CLI plug-in, connect to your PostgreSQL databases using psql by giving it the "composed" connection string. It provides environment variables PGPASSWORD and PGSSLROOTCERT. Set PGPASSWORD to the admin's password and PGSSLROOTCERT to the path or file name for the self-signed certificate.

PGPASSWORD=$PASSWORD PGSSLROOTCERT=0b22f14b-7ba2-11e8-b8e9-568642342d40 psql 'host=4a8148fa-3806-4f9c-b3fc-6467f11b13bd.8f7bfd7f3faa4218aec56e069eb46187.databases.appdomain.cloud port=32325 dbname=ibmclouddb user=admin sslmode=verify-full'

Using the self-signed certificate

  1. Copy the certificate information from the Endpoints panel or the Base64 field of the connection information.
  2. If needed, decode the Base64 string into text.
  3. Save the certificate to a file. (You can use the Name that is provided or your own file name).
  4. Provide the path to the certificate to the ROOTCERT environment variable.

You can display the decoded certificate for your deployment with the CLI plug-in with the command:

ibmcloud cdb deployment-cacert <INSTANCE_NAME_OR_CRN>

The command decodes the base64 into text. Copy and save the command's output to a file and provide the file's path to the ROOTCERT environment variable.

Another option is to add &sslrootcert=/path/to/cert to your connection string, for example:

postgres://$USERNAME:$PASSWORD@6eb96148-90bc-49a0-a5a4-dc2b53334653.btdl8mld0r95fevivv30.databases.appdomain.cloud:32109/ibmclouddb?sslmode=verify-full&sslrootcert=/path/to/cert