PostgreSQL Tools

pgAdmin

pgAdmin3 is a popular GUI client for PostgreSQL. It is available from https://www.pgadmin.org/ with cersions for Windows and Mac OS X. Download, install and run it then select "Add Server" on the menu bar to create a new connection. This will show the following dialog:

The details needed to complete this dialog can be found in the Connection Info panel in the overview of your deployment:

• Name: can be anything describing your Postgres deployment. For simplicity, make this the same name as used in Compose.
• Host: this will be from the host part of your connection string.
• Port: this will be from the port part of your connection string.
• Username: this will be the username for either admin or a user you have created.
• Password: this will be the password for either admin (found in the Credentials section) or a user you have created.

After completing the fields, select the "SSL" tab:

Change SSL to "require". Click "OK" to save the connection settings and connect to the database.

pgHero

pgHero is a GUI front end for pg_stat_statements and used to monitor performance on your databases. It is available as a Rails package, Linux package, or as a Docker image. Instructions here are for connecting a PostgreSQL database to pgHero running in a Docker container.

1. Download and install Docker for your platform.
2. Run Docker with the command load the pgHero image while passing your Compose database's connection string to the container.

docker run -ti -e DATABASE_URL=postgres://user:[email protected]:port/dbname -p 8080:8080 ankane/pghero

The flags are:
-ti to keep the docker session attached so it can be halted easily with Ctrl-C.
-e to set an environment variable (DATABASE_URL) inside the container.
-p 8080:8080 to map the containers port 8080 to the hosts port 8080
ankane/pghero the name of the image we want to use to populate this Docker container.

Example:

docker run -ti -e DATABASE_URL=postgres://admin:[email protected]:17980/compose -p 8080:8080 ankane/pghero

3. Open a web browser to http://localhost:8080/ to see pgHero.

pspg

pspg is a command-line tool for psql which supports paging for tabular formatted data. It replaces the less command when displaying query results. Installation is

You can set the configuration in your psql profile (e.g. ~/.psqlrc) by adding the lines:

\setenv PAGER pspg
\pset border 2
\pset linestyle unicode

Or you can type the above into the psql command-line once you have connected to your deployment to set the configuration for just the current session. Other options can change the colors, themes, or functionality. A complete list is available on the pspg github README.

Still Need Help?

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