Connect to your cluster from a client app

You've created a cluster. Now what? You probably want to connect to it!

You can connect to Cloud Service clusters as you would any other Postgres database.

You can find all of the parameters you need to connect on the portal by selecting the name of your cluster on the Clusters page and then selecting the Connect tab.

Following are a few examples of connecting from common clients.

You can connect to your cluster using the client of your choice including:

• psql — Terminal-based client for Postgres
• Other common database drivers.
• pgAdmin — Desktop or web UI client to inspect, monitor, manage, and query your cluster's databases.

Recommended settings for SSL mode

Different clients can have different default TLS/SSL modes (sslmode). For example, psql defaults to prefer, which means the client attempts to establish a TLS connection but falls back to non-TLS if the server doesn't support it. In the psql example provided by EDB in the Quick Connect field, sslmode is explicitly set to require, which means the client attempts a TLS connection and fails if the connection to the server can't be encrypted.

For public connections and in most environments, EDB recommends setting sslmode to verify-full. This setting ensures that you connect to the server you specified and that the connection is encrypted.

Cloud Service generates certificates with LetsEncrypt, a widely trusted certificate authority. Your client machine might already have a bundled CA certificate for LetsEncrypt, for example, at /etc/ssl/certs/ca-certificates.crt or /etc/pki/ca-trust/extracted/pem/tls-ca-bundle.pem. If it doesn't, your client machine needs a CA certificate for Let's Encrypt. Once the CA certificate is in place on your client machine, configure the sslrootcert parameter to its location, and set the sslmode parameter to verify-full to verify the certificate to fully validate the connection.

To view the encryption protocol being used for communication, connect to the cluster using psql and use the conninfo meta-command. In the case of Cloud Service, TLS (v1.2+) is supported:

edb_admin=> \conninfo
You are connected to database "edb_admin" as user "edb_admin" on host "xxxxxxxxx.xxxxx.biganimal.io" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

Connect to your cluster using psql or edb-psql

1. Sign in to the Console portal.

2. Go to the Clusters page.

3. Select the name of your cluster.

4. On the Overview tab, select the copy icon to the right of the Quick Connect field to copy the command for connecting to your cluster using psql to your clipboard. psql prompts for the edb_admin user password you selected when you created the cluster.

5. Paste the command in your terminal.

Connect to your cluster using common database drivers

1. Sign in to the Console portal.

2. Go to the Clusters page.

3. Select the name of your cluster.

4. Select the Connect tab. You can review and copy all the relevant information you need from this screen except for the edb_admin user password.

   Connection string examples for common database drivers using the recommended setting for SSL mode (consult the client driver documentation for more information):

   • libpq (psql or edb-psql)

     → WrapCopy

     psql -W "postgres://edb_admin@xxxxxxxxx.xxxxx.biganimal.io:5432/edb_admin?sslmode=verify-full&sslrootcert=/usr/share/ca-certificates/ca-cert_name.pem"

   • JDBC for Java

     → WrapCopy

     jdbc:postgresql://xxxxxxxxx.xxxxx.biganimal.io:5432/edb_admin?&sslrootcert=/usr/share/ca-certificates/ca-cert_name.pem&sslmode=verify-full?user=edb_admin&password=$PWD

   • Npgsql for DotNet Core

     → WrapCopy

     Host=xxxxxxxxx.xxxxx.biganimal.io;Port=5432;Username=edb_admin;Password=$PWD;Database=edb_admin;SslRootCert=/usr/share/ca-certificates/ca-cert_name.pem;SslMode=verify-full

   • ODBC for Windows

     → WrapCopy

     Driver={PostgreSQL};Server=xxxxxxxxx.xxxxx.biganimal.io;Port=5432;Database=myDataBase;Uid=edb_admin;Pwd=$PWD;sslrootcert=C:\\ssl\\ca-certificate.pem;sslmode=verify-full;

Connect to your cluster using pgAdmin

To connect to your Cloud Service cluster from pgAdmin, you need to enter your cluster values into pgAdmin. Keep Cloud Service and pgAdmin open to copy and paste the values.

Navigate to the location of the values:

1. Sign in to the Console portal.

2. Go to the Clusters page.

3. Select the cluster you want to connect to.

4. Select the Connect tab.

Enter the values in pgAdmin:

1. Open pgAdmin.

2. In the Quick Links panel, select Add New Server.

3. In the Create-Server dialog box, in the General tab, enter a server name in the Name field.

4. Select the Connection tab.

5. Copy the corresponding values from Cloud Service and paste them into pgAdmin.

   Cloud Service field	pgAdmin field	Example value
   Host	Host name/address	p-n85scw2ies.fcrziuxgkqazmhkl.s.edbcloud.io
   Port	Port	5432
   Dbname	Maintenance database	edb_admin

6. In the Username and Password fields, enter the cluster's administrator credentials. These are the same credentials you set when configuring the cluster. If you didn't set the username, then copy and paste the default administrator username from the User field in Cloud Service.

7. In the SSL tab, set the SSL mode field to Require.

8. Select Save. pgAdmin attempts to connect to the Cloud Service cluster.

Connect a read-only workload

Clusters that are read-only enabled display separate connection strings for Read-only and Read/write access.

To connect to your cluster with the read-only workload:

1. Sign in to the Console portal.

2. Go to Clusters.

3. Select the cluster you want to connect to.

4. Select the Connect tab.

5. Copy the Read-only Service URI.

6. Use the Read-only Service URI to connect to the cluster according to your database driver.

We recommend that your applications run SET default_transaction_read_only = true for all read-only activity. For a libpq driver, add options='-c default_transaction_read_only=true' to the database connection string. Other drivers use similiar connection parameters. Also, applications can run BEGIN TRANSACTION READ ONLY to mark individual transactions as read-only.

ERROR: canceling statement due to conflict with recovery

For more information on querying standby replicas, see the PostgreSQL Hot Standby documentation.