Skip to content

Postgres CLIλ︎

Heroku toolbelt has many commands for viewing information and querying the Heroku Postgres database. Here is a breakdown of the most commonly used commands.

Postgres Command Line Client required

Heroku Toolbelt pg commands require a working postgres command line client to be installed and available on your operating system path.

Ubuntu documentation: PostgreSQL has details on installing postgresql clients.

Postgres Informationλ︎

To see all PostgreSQL databases provisioned by your application and the identifying characteristics of each (db size, status, number of tables, PG version, creation date etc…) use the heroku pg:info command.

$ heroku pg:info
Plan:        Hobby-dev
Status:      available
Connections: 0
PG Version:  9.3.3
Created:     2014-03-20 23:33 UTC
Data Size:   6.5 MB
Tables:      1
Rows:        4/10000 (In compliance)
Fork/Follow: Unsupported
Rollback:    Unsupported

To continuously monitor the status of your database, pass pg:info through the unix watch command:

watch heroku pg:info

Running Queries on Postgresλ︎

psql is the native PostgreSQL interactive terminal and is used to execute queries and issue commands to the connected database. To establish a psql session with your remote database use heroku pg:psql. You must have PostgreSQL installed on your system to use heroku pg:psql.

$ heroku pg:psql
psql (9.2.6, server 9.3.3)
WARNING: psql version 9.2, server version 9.3.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

heroku-app-name::BROWN=> \dt
               List of relations
 Schema |     Name     | Type  |     Owner
 public | pl0_programs | table | moiwgreelvvujc
(1 row)

heroku-app-name::BROWN=> SELECT * FROM pl0_programs;
  name  |           source
 3m2m1  |                     3-2-1\r+
 ap1tb  | a+1*b\r                    +
 test   |                     a+1*b\r+
        |           \r               +
 lolwut |                     3-2-1\r+
(4 rows)

If you have more than one database, specify the database to connect to as the first argument to the command (the database located at DATABASE_URL is used by default).

Connecting to HEROKU_POSTGRESQL_GRAY... done

Reset your databaseλ︎

To drop and recreate your database use heroku pg:reset

$ heroku pg:reset DATABASE

 !    WARNING: Destructive Action
 !    This command will affect the app: heroku-app-name
 !    To proceed, type "pegjspl0" or re-run this command with --confirm heroku-app-name

> heroku-app-name

Then restart the server

$ heroku ps:restart
Restarting dynos... done

There are many more Heroku toolbelt commands you can use for postgres. [TODO: Link to postgres command]