Connecting to PostgreSQL

PostgreSQL deployments come standard with two HAProxy portals providing a connection over HTTPS and are TLS/SSL secured with a self-signed certificate. Compose provides two portals to allow for applications to maintain connectivity should one of the portals become unreachable. Most PostgreSQL drivers do not handle multiple connection strings and graceful failover automatically, so you will have to design your applications to take advantage of multiple connection points.

Language Guides

If you want to connect with a particular language, you can find a simple example on its dedicated page:

• PostgreSQL and Python

Connection Overview and Credentials

All the connection information you need to access your deployment is on the Deployment Overview, under Connection Info.

By default, the deployment comes with an 'admin' username and password. 'admin' will have access to all the databases on your deployment. You may use this to connect, or you may create roles on specific databases for more granular access control. More information and guidance for creating and managing roles is on the Role Management for PostgreSQL page.

The deployment also comes with a default, 'compose' database. Again, you may use that database, or create your own as meets your needs.

Command-Line Interface - psql

psql is the command line tool to connect to Postgres. To use it, the PostgreSQL client tools will need to be installed on the local system. They can be installed by installing the full PostgreSQL package downloaded from postgresql.org, from your operating system's package management platform, or on MacOS X with brew installed, run brew install postgresql.

You can read more about psql in the PostgreSQL documentation - reference - and a simple introduction in Postgres Guide.

The Command line section provides the formatted command that you would use to connect with psql.

Connections are available through either portal on the deployment. The psql line just needs the username substituted in and for you to give the password when prompted. Use the Show credentials button to reveal the 'admin' password for authenticating to your database. You may also connect directly to any other database on your deployment by modifying the dbname parameter or connect with another role (assuming you have created one) by modifying the user parameter.

Self-signed Certificate

The self-signed certificate can be found under the Connection Info on the Overview of your deployment. Click on the Show Certificate button to reveal the certificate; you may be prompted to re-authenticate for security.

To save a copy, open the certificate and copy it's contents. Paste them into an empty file and save with a .pem or .crt extension. Some language drivers, like python, will need a path to this file to be able to connect using SSL.

Still Need Help?

If this article didn't solve things, summon a human and get some help!