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.
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.
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.
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 example-postgres --start
or
ibmcloud cdb cxn example-postgres -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
- Copy the certificate information from the Endpoints panel or the Base64 field of the connection information.
- If needed, decode the Base64 string into text.
- Save the certificate to a file. (You can use the Name that is provided or your own file name).
- 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 "your-service-name"
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