Connecting from a client app

Suggest edits

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.

BigAnimal 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 BigAnimal, 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

Note

When connecting to an EDB Postgres Advanced Server database, use edb-psql. edb-psql is a symbolic link to a binary called psql, a modified version of the PostgreSQL community psql with added support for EDB Postgres Advanced Server features.

  1. Sign in to the BigAnimal 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 BigAnimal 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)

      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

      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

      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

      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 BigAnimal cluster from pgAdmin, you need to enter your cluster values into pgAdmin. Keep BigAnimal and pgAdmin open to copy and paste the values.

Navigate to the location of the values:

  1. Sign in to the BigAnimal 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 BigAnimal and paste them into pgAdmin.

    BigAnimal fieldpgAdmin fieldExample value
    HostHost name/addressp-n85scw2ies.fcrziuxgkqazmhkl.s.edbcloud.io
    PortPort5432
    DbnameMaintenance databaseedb_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 BigAnimal.

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

  8. Select Save. pgAdmin attempts to connect to the BigAnimal 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 BigAnimal 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.

Note

On read-only workloads, BigAnimal might cancel long-running queries and display an error message like the following:

ERROR: canceling statement due to conflict with recovery

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

← Prev

Connecting from AWS

Next →

Configuring EDB PgBouncer for BigAnimal