Connecting from a client app
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.
Sign in to the BigAnimal portal.
Go to the Clusters page.
Select the name of your cluster.
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
psqlto your clipboard.psqlprompts for the edb_admin user password you selected when you created the cluster.Paste the command in your terminal.
Connect to your cluster using common database drivers
Sign in to the BigAnimal portal.
Go to the Clusters page.
Select the name of your cluster.
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:
Sign in to the BigAnimal portal.
Go to the Clusters page.
Select the cluster you want to connect to.
Select the Connect tab.
Enter the values in pgAdmin:
Open pgAdmin.
In the Quick Links panel, select Add New Server.
In the Create-Server dialog box, in the General tab, enter a server name in the Name field.
Select the Connection tab.
Copy the corresponding values from BigAnimal and paste them into pgAdmin.
BigAnimal field pgAdmin field Example value Host Host name/address p-n85scw2ies.fcrziuxgkqazmhkl.s.edbcloud.io Port Port 5432 Dbname Maintenance database edb_admin 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.
In the SSL tab, set the SSL mode field to Require.
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:
Sign in to the BigAnimal portal.
Go to Clusters.
Select the cluster you want to connect to.
Select the Connect tab.
Copy the Read-only Service URI.
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.