Heroku Postgres

Last Updated: 18 April 2014

database postgres

Heroku Postgres is the SQL database service run by Heroku that is provisioned and managed as an add-on. Heroku Postgres is accessible from any language with a PostgreSQL driver including all languages and frameworks supported by Heroku: Java, Ruby, Python, Scala, Play, Node.js and Clojure.

In addition to a variety of management commands available via the Heroku CLI, Heroku Postgres features a web dashboard, the ability to create dataclips and several additional services on top of a fully managed database service.

Provisioning the add-on

Many buildpacks (what compiles your application into a runnable entity on Heroku) automatically provision a Heroku Postgres instance for you. Your language’s buildpack documentation will specify if any add-ons are automatically provisioned. Additionally, you can use heroku addons to see if your application already has a database provisioned and what plan it is.

$ heroku addons | grep POSTGRES
heroku-postgresql:dev  HEROKU_POSTGRESQL_RED

If your application doesn’t yet have a database provisioned, or you wish to upgrade your existing database or create a master/slave setup, you can create a new database using the CLI.

Create new db

Heroku Postgres can be attached to a Heroku application via the CLI:

$ heroku addons:add heroku-postgresql:dev
Adding heroku-postgresql:dev to sushi... done, v69 (free)
Attached as HEROKU_POSTGRESQL_RED
Database has been created and is available

Depending on the plan, some databases can take up to 5 minutes to become available. Use pg:wait to track their status:

$ heroku pg:wait
Waiting for database HEROKU_POSTGRESQL_RED... done

Once Heroku Postgres has been added a HEROKU_POSTGRESQL_COLOR_URL setting will be available in the app configuration and will contain the URL used to access the newly provisioned Heroku Postgres service. This can be confirmed using the heroku config command.

$ heroku config | grep HEROKU_POSTGRESQL
HEROKU_POSTGRESQL_RED_URL: postgres://user3123:passkja83kd8@ec2-117-21-174-214.compute-1.amazonaws.com:6212/db982398

Establish primary DB

Heroku recommends using the DATABASE_URL config var to store the location of your primary database. In single-database setups your new database will have already been assigned a HEROKU_POSTGRESQL_COLOR_URL config with the accompanying DATABASE_URL. You may verify this via heroku config and verifying the value of both HEROKU_POSTGRESQL_COLOR_URL and DATABASE_URL which should match.

On apps with multiple databases, you can set the primary database like so:

$ heroku pg:promote HEROKU_POSTGRESQL_RED_URL
Promoting HEROKU_POSTGRESQL_RED_URL to DATABASE_URL... done

At this point an empty PostgreSQL database is provisioned. To populate it with data from an existing data source see the import instructions or follow the language-specific instructions in this article to connect from your application.

Version support

The version of Postgres you wish to run can be specified with the --version flag within the Heroku Toolbelt.

The PostgreSQL project releases new major versions yearly. Each major version once released will be supported shortly after by Heroku Postgres. Heroku Postgres will support at least 3 major versions at a given time. Currently supported versions are:

• 9.3 (default)
• 9.2
• 9.1
• 9.0

By supporting at least 3 major versions, users will be required to upgrade once every three years. However, you can upgrade at any point earlier to gain the benefits of the latest version.

Local setup

Heroku recommends running the same database locally during development as in production. There are several pre-packaged installers for installing PostgreSQL in your local environment.

Set up Postgres on Mac

Install Postgres.app and follow documentation. Note that Postgres.app requires Mac OS 10.7 or above. Once installed verify that it worked correctly. The mac version of psql should point to the Postgres.app directory:

$ which psql
/Applications/Postgres.app/Contents/Versions/9.3/bin/psql

and this command should work correctly:

$  psql -h localhost
psql (9.3.2)
Type "help" for help.
schneems=# \q

Also verify that the app is set to Automatically start at login.

PostgreSQL ships with a constellation of useful binaries, like pg_dump or pg_restore, that you will likely want to use. Go ahead and add the /bin directory that ships with Postgres.app to your PATH (preferably in .profile, .bashrc, .zshrc, or the like to make sure this gets set for every Terminal session):

PATH="/Applications/Postgres93.app/Contents/MacOS/bin:$PATH:$PATH"

Set up Postgres on Windows

Install postgres on windows by using the Windows installer.

Set up Postgres on Linux

Install Postgres via your package manager. The actual package manager command you use will depend on your distribution. The following will work on Ubuntu, Debian, and other Debian-derived distros:

$ sudo apt-get install postgresql

If you do not have a package manager on your distro or the Postgres package is not available, install Postgres on Linux using one of the Generic installers.

The psql client will typically be installed in /usr/bin:

$ which psql
/usr/bin/psql

and the command should work correctly:

$ psql
psql (9.3.1)
Type "help" for help.
maciek# \q

Using the CLI

Heroku Postgres is integrated directly into the Heroku CLI and offers several commands that automate many common tasks associated with managing a database-backed application.

pg:info

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
=== HEROKU_POSTGRESQL_RED
Plan         Ika
Status       available
Data Size    82.8 GB
Tables       13
PG Version   9.1.3
Created      2012-02-15 09:58 PDT
=== HEROKU_POSTGRESQL_GRAY
Plan         Ronin
Status       following
Data Size    82.8 GB
…

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

$ watch heroku pg:info

pg:psql

To establish a psql session with your remote database use heroku pg:psql.

$ heroku pg:psql
Connecting to HEROKU_POSTGRESQL_RED... done
psql (9.1.3, server 9.1.3)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

rd2lk8ev3jt5j50=> SELECT * FROM users;

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).

$ heroku pg:psql HEROKU_POSTGRESQL_GRAY
Connecting to HEROKU_POSTGRESQL_GRAY... done
...

pg:push and pg:pull

pg:pull

pg:pull can be used to pull remote data from a Heroku Postgres database to a database on your local machine. The command looks like this:

$ heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi

This command will create a new local database named “mylocaldb” and then pull data from database at DATABASE_URL from the app “sushi”. In order to prevent accidental data overwrites and loss, the local database must not exist. You will be prompted to drop an already existing local database before proceeding.

If providing a Postgres user or password for your local DB is necessary, use the appropriate environment variables like so:

$ PGUSER=postgres PGPASSWORD=password heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi

Note: like all pg:* commands you can use the shorthand identifiers here, so to pull data from HEROKU_POSTGRESQL_RED on the app “sushi” you could do heroku pg:pull sushi::RED mylocaldb.

pg:push

Like pull but in reverse, pg:push will push data from a local database into a remote Heroku Postgres database. The command looks like this:

$ heroku pg:push mylocaldb HEROKU_POSTGRESQL_MAGENTA --app sushi

This command will take the local database “mylocaldb” and push it to the database at DATABASE_URL on the app “sushi”. In order to prevent accidental data overwrites and loss, the remote database must be empty. You will be prompted to pg:reset an already a remote database that is not empty.

Usage of the PGUSER and PGPASSWORD for your local database is also supported for pg:push, just like for the pg:pull commands.

Troubleshooting

These commands rely on the pg_dump and pg_restore binaries that are included in a Postgres installation. It is somewhat common, however, for the wrong binaries to be loaded in $PATH. Errors such as

!    createdb: could not connect to database postgres: could not connect to server: No such file or directory
!      Is the server running locally and accepting
!      connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?
!
!    Unable to create new local database. Ensure your local Postgres is working and try again.

and

pg_dump: server version: 9.3.1; pg_dump version: 9.1.5
pg_dump: aborting because of server version mismatch
pg_dump: *** aborted because of error
pg_restore: [archiver] input file is too short (read 0, expected 5)

are both often a result of this incorrect $PATH problem. This problem is especially common with Postgres.app users, as the post-install step of adding /Applications/Postgres.app/Contents/MacOS/bin to $PATH is easy to forget.

pg:ps, pg:kill, pg:killall

These commands give you view and control over currently running queries.

The pg:ps command queries the pg_stat_statements table in postgres to give a concise view into currently running queries.

$ heroku pg:ps
 procpid |         source            |   running_for   | waiting |         query
---------+---------------------------+-----------------+---------+-----------------------
   31776 | psql                      | 00:19:08.017088 | f       | <IDLE> in transaction
   31912 | psql                      | 00:18:56.12178  | t       | select * from hello;
   32670 | Heroku Postgres Data Clip | 00:00:25.625609 | f       | BEGIN READ ONLY; select 'hi'
(3 rows)

The procpid column can then be used to cancel or terminate those queries with pg:kill. Without any arguments pg_cancel_backend is called on the query which will attempt to cancel the query. In some situations that can fail, in which case the --force option can be used to issue pg_terminate_backend which drops the entire connection for that query.

$ heroku pg:kill 31912
 pg_cancel_backend
-------------------
 t
(1 row)

$ heroku pg:kill --force 32670
 pg_terminate_backend
----------------------
 t
(1 row)

pg:killall is similar to pg:kill except it will cancel or terminate every query on your database.

pg:promote

In setups where more than one database is provisioned (common use-cases include a master/slave high-availability setup or as part of the database upgrade process) it is often necessary to promote an auxiliary database to the primary role. This is accomplished with the heroku pg:promote command.

$ heroku pg:promote HEROKU_POSTGRESQL_GRAY_URL
Promoting HEROKU_POSTGRESQL_GRAY_URL to DATABASE_URL... done

pg:promote works by setting the value of the DATABASE_URL config var (which your application uses to connect to the primary database) to the newly promoted database’s URL and restarting your app. The old primary database location is still accessible via its HEROKU_POSTGRESQL_COLOR_URL setting.

pg:credentials

Heroku Postgres provides convenient access to the credentials and location of your database should you want to use a GUI to access your instance.

$ heroku pg:credentials DATABASE
Connection info string:
   "dbname=dee932clc3mg8h host=ec2-123-73-145-214.compute-1.amazonaws.com port=6212 user=user3121 password=98kd8a9 sslmode=require"

It is a good security practice to rotate the credentials for important services on a regular basis. On Heroku Postgres this can be done with heroku pg:credentials --reset.

$ heroku pg:credentials HEROKU_POSTGRESQL_GRAY_URL --reset

When you issue this command, new credentials are created for your database and the related config vars on your Heroku application are updated. However, on Standard, Premium, and Enterprise tier databases the old credentials are not removed immediately. All of the open connections remain open until the currently running tasks complete, then those credentials are updated. This is to make sure that any background jobs or other workers running on your production environment aren’t abruptly terminated, potentially leaving the system in an inconsistent state.

pg:reset

The PostgreSQL user your database is assigned doesn’t have permission to create or drop databases. To drop and recreate your database use pg:reset.

$ heroku pg:reset DATABASE

Connecting in Java

There are a variety of ways to create a connection to a Heroku Postgres database, depending on the Java framework in use, though they all use the DATABASE_URL environment url to determine connection information.

JDBC

Create a JDBC connection to Heroku Postgres by parsing the DATABASE_URL environment variable.

private static Connection getConnection() throws URISyntaxException, SQLException {
    URI dbUri = new URI(System.getenv("DATABASE_URL"));

    String username = dbUri.getUserInfo().split(":")[0];
    String password = dbUri.getUserInfo().split(":")[1];
    String dbUrl = "jdbc:postgresql://" + dbUri.getHost() + ':' + dbUri.getPort() + dbUri.getPath();

    return DriverManager.getConnection(dbUrl, username, password);
}

Spring/XML

This snippet of Spring XML configuration will setup a BasicDataSource from the DATABASE_URL and can then be used with Hibernate, JPA, etc:

<bean class="java.net.URI" id="dbUrl">
    <constructor-arg value="#{systemEnvironment['DATABASE_URL']}"/>
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="url" value="#{ 'jdbc:postgresql://' + @dbUrl.getHost() + ':' + @dbUrl.getPort() + @dbUrl.getPath() }"/>
    <property name="username" value="#{ @dbUrl.getUserInfo().split(':')[0] }"/>
    <property name="password" value="#{ @dbUrl.getUserInfo().split(':')[1] }"/>
</bean>

Spring/Java

Alternatively you can use Java for configuration of the BasicDataSource in Spring:

@Configuration
public class MainConfig {

    @Bean
    public BasicDataSource dataSource() throws URISyntaxException {
        URI dbUri = new URI(System.getenv("DATABASE_URL"));

        String username = dbUri.getUserInfo().split(":")[0];
        String password = dbUri.getUserInfo().split(":")[1];
        String dbUrl = "jdbc:postgresql://" + dbUri.getHost() + ':' + dbUri.getPort() + dbUri.getPath();

        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(dbUrl);
        basicDataSource.setUsername(username);
        basicDataSource.setPassword(password);

        return basicDataSource;
    }
}

The DATABASE_URL for the Heroku Postgres add-on follows this naming convention:

postgres://<username>:<password>@<host>/<dbname>

However the Postgres JDBC driver uses the following convention:

jdbc:postgresql://<host>:<port>/<dbname>?username=<username>&password=<password>

Notice the additional ql at the end of jdbc:postgresql? Due to this difference you will need to hardcode the scheme to jdbc:postgresql in your Java class or your Spring XML configuration.

Remote connections

You can connect to your Heroku Postgres database remotely for maintenance and debugging purposes. However, doing so requires that you use an SSL connection. Your JDBC connection URL will need to include the following:

ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

If you leave off ssl=true you will get a connection error. If you leave off sslfactory=org.postgresql.ssl.NonValidatingFactory you may get an error like:

unable to find valid certification path to requested target

Click here for more information on SSL support with the Postgres JDBC driver.

Connecting in Ruby

To use PostgreSQL as your database in Ruby applications you will need to include the pg gem in your Gemfile.

gem 'pg'

Run bundle install to download and resolve all dependencies.

Connecting in Rails

When Rails applications are deployed to Heroku a database.yml file is automatically generated for your application that configures ActiveRecord to use a PostgreSQL connection and to connect to the database located at DATABASE_URL.

To use PostgreSQL locally with a Rails app your database.yml should contain the following configuration:

development:
  adapter: postgresql
  host: localhost
  username: user
  database: app-dev

Connecting in Python

To use PostgreSQL as your database in Python applications you will need to use the psycopg2 package.

$ pip install psycopg2
$ pip freeze > requirements.txt

And use this package to connect to DATABASE_URL in your code:

import os
import psycopg2
import urlparse

urlparse.uses_netloc.append("postgres")
url = urlparse.urlparse(os.environ["DATABASE_URL"])

conn = psycopg2.connect(
    database=url.path[1:],
    user=url.username,
    password=url.password,
    host=url.hostname,
    port=url.port
)

Connecting with Django

Install the dj-database-url package using pip.

$ pip install dj-database-url
$ pip freeze > requirements.txt

Then add the following to the bottom of settings.py:

import dj_database_url
DATABASES['default'] =  dj_database_url.config()

This will parse the values of the DATABASE_URL environment variable and convert them to something Django can understand.

Connecting in Node.js

Add the pg NPM module to your dependencies:

"dependencies": {
  ...
  "pg": "0.6.15",
  "express": "latest"
}

And use the module to connect to DATABASE_URL from somewhere in your code:

var pg = require('pg');

pg.connect(process.env.DATABASE_URL, function(err, client) {
  var query = client.query('SELECT * FROM your_table');

  query.on('row', function(row) {
    console.log(JSON.stringify(row));
  });
});

Connection permissions

Heroku Postgres users are granted all non-superuser permissions on their database. These include SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE.

Heroku runs the SQL below to create a user and database for you.

CREATE ROLE user_name;
ALTER ROLE user_name WITH LOGIN PASSWORD 'password' NOSUPERUSER NOCREATEDB NOCREATEROLE;
CREATE DATABASE database_name OWNER user_name;
REVOKE ALL ON DATABASE database_name FROM PUBLIC;
GRANT CONNECT ON DATABASE database_name TO database_user;
GRANT ALL ON DATABASE database_name TO database_user;

Multiple schemas

Heroku Postgres supports multiple schemas and does not place any limits on the number of schemas you can create.

External connections (ingress)

In addition to being available to the Heroku runtime, Heroku Postgres databases can be accessed directly by clients running on your local computer or elsewhere.

You can retrieve the PG connection string in one of two ways. heroku pg:credentials is discussed above:

$ heroku pg:credentials DATABASE
Connection info string:
   "dbname=dee932clc3mg8h host=ec2-123-73-145-214.compute-1.amazonaws.com port=6212 user=user3121 password=98kd8a9 sslmode=require"

Also, the connection string is exposed as a config var for your app:

$ heroku config | grep HEROKU_POSTGRESQL
HEROKU_POSTGRESQL_RED_URL: postgres://user3123:passkja83kd8@ec2-117-21-174-214.compute-1.amazonaws.com:6212/db982398

From other Heroku apps

You can configure other applications running on Heroku to connect to your database, provided that you have the databases credentials. Simply override the DATABASE_URL of the other application to your desired database.

$ heroku config:set DATABASE_URL=`heroku config:get DATABASE_URL -a source-app`

Monitoring & logging

If your application/framework emits logs on database access, you will be able to retrieve them through Heroku’s log-stream:

$ heroku logs -t

To see logs from the database service itself you can also use heroku logs but with the -p postgres flag indicating that you only wish to see the logs from PostgreSQL.

$ heroku logs -p postgres -t

Migrating between plans

The process for migrating between Heroku Postgres plans varies depending on the plan tier of the database to be upgraded.

To migrate from a starter tier database (dev or basic) use the PG Backups add-on.

To migrate from one level of production tier database to another use the follower feature.

Removing the add-on

In order to destroy your Heroku Postgres database you will need to remove the add-on.

$ heroku addons:remove heroku-postgresql:dev

If you have two databases of the same type you will need to remove the add-on using its config var name. For example, to remove the HEROKU_POSTGRESQL_GRAY_URL, you would run:

heroku addons:remove HEROKU_POSTGRESQL_GRAY

If the removed database was the same one used in DATABASE_URL, that DATABASE_URL config var will also be unset on the app.

Support

All Heroku Postgres support and runtime issues should be submitted via one of the Heroku Support channels.