Heroku Postgres
Last updated March 23, 2022
Table of Contents
- Understanding Heroku Postgres Plans
- Provisioning Heroku Postgres
- Local Setup
- Designating a Primary Database
- Sharing Heroku Postgres between Applications
- Version Support
- Legacy Infrastructure
- Performance Analytics
- Using the CLI
- Connecting to Heroku Postgres
- Migrating between Plans
- Data Residency
- Removing the Add-on
- Support
Heroku Postgres is a managed SQL database service provided directly by Heroku. You can access a Heroku Postgres database from any language with a PostgreSQL driver, including all languages officially supported by Heroku.
In addition to a variety of management commands available via the Heroku CLI, Heroku Postgres provides a web dashboard, the ability to share queries with dataclips, and several other helpful features.
Understanding Heroku Postgres Plans
Heroku Postgres offers a variety of plans, spread across different tiers of service: hobby, standard, premium, and enterprise. For more information on what each plan provides, see Choosing the Right Heroku Postgres Plan.
Pricing information for Heroku Postgres plans is available on the Heroku Postgres add-on page.
If your app’s requirements eventually outgrow the resources provided by the initial plan you select, you can easily upgrade your database.
Provisioning Heroku Postgres
Before you provision Heroku Postgres, confirm that it isn’t already provisioned for your app. Heroku automatically provisions Postgres for apps that include certain libraries, such as the pg
Ruby gem.
Use the heroku addons
command to determine whether your app already has Heroku Postgres provisioned:
$ heroku addons
Add-on Plan Price State
────────────────────────────────────────────────────────── ───────── ───── ───────
heroku-postgresql (postgresql-concave-52656) hobby-dev free created
If heroku-postgresql
doesn’t appear in your app’s list of add-ons, you can provision it with the following CLI command:
$ heroku addons:create heroku-postgresql:<PLAN_NAME>
For example, to provision a hobby-dev plan database:
$ heroku addons:create heroku-postgresql:hobby-dev
Creating heroku-postgresql:hobby-dev on ⬢ sushi... free
Database has been created and is available
! This database is empty. If upgrading, you can transfer
! data from another database with pg:copy
Created postgresql-concave-52656 as DATABASE_URL
You can specify the version of Postgres you want to provision by including the --version
flag in your provisioning command:
$ heroku addons:create heroku-postgresql:<PLAN_NAME> --version=12
Learn more about PostgreSQL version support.
Depending on the plan you choose, your database can take up to 5 minutes to become available. You can track its status with the heroku pg:wait
command, which blocks until your database is ready to use.
As part of the provisioning process, a DATABASE_URL
config var is added to your app’s configuration. DATABASE_URL
contains the URL your app uses to access the database. If your app already has a Heroku Postgres database and you’ve provisioned another one, this config var’s name instead has the format HEROKU_POSTGRESQL_<COLOR>_URL
(for example, HEROKU_POSTGRESQL_YELLOW_URL
).
You can confirm the names and values of your app’s config vars with the heroku config
command.
The value of your app’s DATABASE_URL
config var can change at any time.
Do not rely on this value either inside or outside your Heroku app.
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.
Local Setup
Heroku recommends running Postgres locally to ensure parity between environments. There are several pre-packaged installers for installing PostgreSQL in your local environment.
After Postgres is installed and you can connect, you must export the DATABASE_URL environment variable for your app to connect to it when running locally:
-- for Mac and Linux
$ export DATABASE_URL=postgres://$(whoami)
-- for Windows
$ set DATABASE_URL=postgres://$(whoami)
Postgres will connect to the local database matching your user account name (which is set up as part of installation).
Set up Postgres on Mac
Postgres.app requires Mac OS 10.7 or above.
- Install Postgres.app and follow setup instructions.
- Install the postgres CLI tools.
- Open up a new terminal window to ensure your changes have been saved.
- Verify that it worked correctly. The OS X version of
psql
must point to the path containing thePostgres.app
directory.
If you’re using version 10, the output will look similar to:
$ which psql
/Applications/Postgres.app/Contents/Versions/latest/bin/psql
This command should work correctly:
$ createdb
$ psql -h localhost
psql (10.14)
Type "help" for help.
=# \q
Also verify that the app is set to automatically start at login.
PostgreSQL ships with several useful binaries including pg_dump
and pg_restore
. To make these available in every terminal session, add the /bin
directory that ships with Postgres.app to your PATH (preferably in .profile
, .bashrc
, .zshrc
, or similar):
PATH="/Applications/Postgres.app/Contents/Versions/latest/bin:$PATH"
Set up Postgres on Windows
Install Postgres on Windows by using the Windows installer.
Remember to update your PATH environment variable to add the bin
directory of your Postgres installation. The directory is similar to: C:\Program Files\PostgreSQL\<VERSION>\bin
. Commands like heroku pg:psql
depend on the PATH and do not work if the PATH is incorrect.
Set up Postgres on Linux
Install Postgres via your package manager. The actual package manager command you use depends on your distribution. The following works on Ubuntu, Debian, and other Debian-derived distributions:
$ sudo apt-get install postgresql
If you don’t have a package manager on your distribution or the Postgres package isn’t available, install Postgres on Linux using one of the Generic installers.
The psql
client is typically installed in /usr/bin
:
$ which psql
/usr/bin/psql
The following command should work correctly:
$ psql
psql (9.3.5)
Type "help" for help.
maciek# \q
Designating a Primary Database
The DATABASE_URL
config var designates the URL of an app’s primary Heroku Postgres database. For apps with a single database, its URL is automatically assigned to this config var.
For apps with multiple Postgres databases, set the primary database with heroku pg:promote
. Common use cases include leader/follower high-availability setups or as part of the database upgrade process.
Sharing Heroku Postgres between Applications
You can share a single Heroku Postgres database between multiple apps with the heroku addons:attach
command:
$ heroku addons:attach my-originating-app::DATABASE --app sushi
Attaching postgresql-addon-name to sushi... done
Setting HEROKU_POSTGRESQL_BRONZE vars and restarting sushi... done, v11
The attached database’s URL is assigned to a config var with the name format HEROKU_POSTGRESQL_[COLOR]_URL
. In the example above, the config var’s name is HEROKU_POSTGRESQL_BRONZE_URL
.
A shared database isn’t necessarily the primary database for any given app that it’s shared with. You promote a shared database with the same command that you use for any other database.
You can stop sharing your Heroku Postgres instance with another app with the heroku addons:detach
command:
$ heroku addons:detach HEROKU_POSTGRESQL_BRONZE --app sushi
Detaching HEROKU_POSTGRESQL_BRONZE to postgresql-addon-name from sushi... done
Unsetting HEROKU_POSTGRESQL_BRONZE config vars and restarting sushi... done, v11
Version Support
The PostgreSQL project releases new major versions on a yearly basis. Each major version is supported by Heroku Postgres shortly after its release.
Heroku Postgres supports at least 3 major versions at a given time. Heroku currently offers Postgres version 13 as the default. Currently supported versions include:
Tier | Version | Status | EOL Date |
---|---|---|---|
hobby | 9.6 | Deprecated | November 11, 2021 |
hobby | 10 | Deprecated | November 10, 2022 |
hobby | 11 | Available | November 9, 2023 |
hobby | 12 | Available | November 14, 2024 |
hobby | 13 | Available | November 13, 2025 |
hobby | 14 | Available (Default) | November 12, 2026 |
production | 9.6 | Deprecated | November 11, 2021 |
production | 10 | Deprecating | November 10, 2022 |
production | 11 | Available | November 9, 2023 |
production | 12 | Available | November 14, 2024 |
production | 13 | Available (Default) | November 13, 2025 |
production | 14 | Available | November 12, 2026 |
Users are required to upgrade roughly one time every three years. However, you can upgrade your database PostgreSQL version at any point to gain the benefits of the latest version.
The deprecation process for PostgreSQL versions in Heroku Postgres starts 1 year before its EOL date. Deprecation processes are announced via the Changelog.
To create a database on an older, supported PostgreSQL version use the –version flag with the addons:create
command.
Migration of Deprecated Databases
The PostgreSQL project stops supporting a major version five years after its initial release. Heroku Postgres deprecates these versions to ensure no databases run on an unsupported major version of PostgreSQL.
Heroku highly recommends that you perform the version upgrade before support ends so that you can test compatibility, plan for unforeseen issues, and migrate your database on your own schedule.
Hobby-tier databases
One (1) year before a version’s end of life (EOL) Heroku prevents provisioning new Hobby databases on the deprecated version.
At that time, Heroku starts migrating Hobby databases running on the deprecated versions to the last default version available.
Production databases
- One (1) year before a version’s end of life (EOL), Heroku notifies customers via email about the deprecation process for their affected databases.
- Six (6) months before EOL, Heroku prevents provisioning new production databases on the deprecated version. Creating forks and followers of existing databases is allowed.
- One (1) month before EOL, Heroku schedules forced upgrade maintenances for databases that are still running on a deprecated version.
Legacy Infrastructure
Heroku also occasionally deprecates old versions of its infrastructure for the following reasons:
- The operating system running beneath the database stop receiving security updates.
- Support for the operating system is no longer practical due to its age (required packages and patches are no longer available or difficult to support).
- The server instances are are impractical to support because they are significantly different from Heroku’s current infrastructure.
To see if your database is running on legacy infrastructure, use pg:info
:
$ heroku pg:info
=== DATABASE_URL, HEROKU_POSTGRESQL_IVORY_URL
Plan: Standard 0
Status: Available
Data Size: 8.09 MB
Tables: 0
PG Version: 12.5
Connections: 7/120
Connection Pooling: Available
Credentials: 1
Fork/Follow: Available
Rollback: earliest from 2021-01-24 18:59 UTC
Created: 2020-12-01 02:27
Region: us
Data Encryption: In Use
Continuous Protection: On
Forked From: HEROKU_POSTGRESQL_SILVER
Maintenance: not required
Maintenance window: Wednesdays 21:30 to Thursdays 01:30 UTC
Add-on: postgresql-cubed-48277
Performance Analytics
For more information about performance analytics, see Heroku Postgres Performance Analytics.
Using the CLI
For more information about managing Heroku Postgres using the CLI, see Managing Heroku Postgres Using the CLI.
Connecting to Heroku Postgres
For more information about connecting to Heroku Postgres, see Connecting to Heroku Postgres.
Migrating between Plans
See this detailed guide on updating and migrating between database plans.
Data Residency
When a database gets provisioned, the data associated with that database is stored within the region in which it’s created. However, services ancillary to Heroku Postgres and the systems managing the database fleet might not be located within the same region as the provisioned databases:
- Postgres Continuous Protection for disaster recovery stores the base backup and write-ahead logs in the same region that the database is located.
- Application logs are routed to Logplex, which is hosted in the US. In addition to logs from your application, this includes System logs and Heroku Postgres logs from any database attached to your application.
- Logging of Heroku Postgres queries and errors can be blocked by using the
--block-logs
flag when creating the database withheroku addons:create heroku-postgres:...
. - PG Backup snapshots are stored in the US. To capture logical backups in another region, see Heroku Postgres Logical Backups.
- Dataclips are stored in the US.
Blocking Logs
At add-on creation time, a flag can be passed to prevent logging of queries that get run against the database. If this option is turned on, it can’t be turned off after the database has been provisioned. If you must turn it off after it has been turned on, a migration to a new database will be required.
Blocking the queries in the logs reduces Heroku’s ability to help debug applications and tune application performance.
$ heroku addons:create heroku-postgresql:standard-0 -a sushi --block-logs
Removing the Add-on
You must remove the add-on to destroy your Heroku Postgres database.
$ heroku addons:destroy heroku-postgresql:hobby-dev
If you have two databases of the same type you must to remove the add-on using its config var name. For example, to remove the HEROKU_POSTGRESQL_GRAY_URL
, you would run:
heroku addons:destroy HEROKU_POSTGRESQL_GRAY
If the removed database was the same one used in DATABASE_URL
, that DATABASE_URL
config var is unset on the app.
Databases can’t be reconstituted after being destroyed. Take a snapshot of the data beforehand using PG Backups or by exporting the data
Support
All Heroku Postgres support and runtime issues should be submitted via one of the Heroku Support channels.