Connecting with psql

Gen 2

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.

Ensure that the user connecting to the deployment has the role pg_monitor, which allows access to PostgreSQL monitoring views and functions.

Databases for PostgreSQL deployments no longer include a default admin user. Instead, customers create a user with the 'Manager' or 'Writer' role using the IBM Cloud® service credential interface — via UI or CLI. The process provides credentials for connecting to the deployment.

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 on RHEL/CentOS 8:

sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

On RHEL/CentOS 9 use:

sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.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 postgresql18

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.

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 service proprietary 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 service proprietary 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 the username and password for your instance. You will need the the username and password returned to you when you created either a Manager or Writer user using the service credential route.

Run the following command:

export PGUSER=<username>
export PGPASSWORD=<password>

Then, execute the following command:

ibmcloud resource service-instance <instance name or instance id> -o json

Look for the psql connection string in the output. Alternatively, if you have jq installed, run the following command:

ibmcloud resource service-instance <instance name or instance id> -o json | jq '.[0].extensions.dataservices.connection.cli.compose[0]'

The connection string has the following format:

PGUSER=$PGUSER PGPASSWORD=$PASSWORD PGSSLMODE=verify-full PGSSLROOTCERT=system psql 'host=<instance>.<subdomain>.appdomain.cloud port=5432 dbname=postgres

You can only connect from within a VPE that is configured for your PostgreSQL instance.