Heroku Postgres
Last updated July 26, 2024
Table of Contents
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 Heroku CLI commands to manage your database, Heroku Postgres provides a web dashboard, dataclips to share queries, and several other helpful features.
Understanding Heroku Postgres Plans
Heroku Postgres offers a variety of plans, spread across different tiers of service: Essential, Standard, Premium, Private, and Shield. 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
For more information about how to provision a Heroku Postgres database, see Provisioning Heroku Postgres.
Local Setup
Heroku recommends running Postgres locally to ensure parity between environments. For more information about how to set up Heroku Postgres to run in your local environment, see Local Setup for Heroku Postgres.
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. You can also specify an alias for your add-on using the –as flag.:
$ heroku addons:attach my-originating-app::DATABASE --app example-app --as ATTACHED_DB
Attaching postgresql-addon-name to example-app... done
Setting ATTACHED_DB vars and restarting example-app... done, v11
By default, the attached database’s URL is assigned to a config var with the name format HEROKU_POSTGRESQL_[COLOR]_URL
. In the example, since an alias was set, the config var’s name is ATTACHED_DB
.
A shared database isn’t necessarily the primary database for any given app that it’s shared with. To promote a shared database, use the same command that you use for any other database.
To stop sharing your Heroku Postgres instance with another app, use the heroku addons:detach
command:
$ heroku addons:detach ATTACHED_DB --app example-app
Detaching ATTACHED_DB to postgresql-addon-name from example-app... done
Unsetting ATTACHED_DB config vars and restarting example-app... done, v11
Version Support
The PostgreSQL project releases new major versions on a yearly basis. Heroku Postgres supports each major version shortly after its release. For more information about version support on Heroku Postgres, see Heroku Postgres Version Support.
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.
To connect to a common runtime database from an external resource, see Connecting to Common Runtime Heroku Postgres Databases from an External Resource.
To connect to a Private or Shield database from an external resource, see Connecting to a Private or Shield Heroku Postgres Database from an External Resource.
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, some services ancillary to Heroku Postgres and the systems managing the database fleet aren’t 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.
- Logs for the Common Runtime are routed in the same region the app is running within. For example, logs for an app in the
us
region gets routed through theus
infrastructure. - Logs for apps in Private Spaces stay within the same region as the space itself. For example, logs for an app in a Tokyo space routes through the infrastructure in Tokyo. You can deploy apps with strict compliance requirements in Shield Private Spaces, which uses Private Space Logging to route logs instead of Logplex.
- 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.
See the Salesforce Infrastructure & Sub-processors document for the list of sub-processors Heroku uses and the list of countries where Heroku data is stored and processed.
Blocking Logs
At add-on creation time, you can pass a flag 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. To turn it off after it has been turned on, a migration to a new database is required.
Blocking the queries in the logs reduces Heroku’s ability to help debug applications and tune application performance.
The addons:create
example follows the syntax for Heroku CLI v9.0.0 or later. If you’re on v8.11.5 or earlier, use the command:
$ heroku addons:create heroku-postgresql:standard-0 -a example-app --block-logs
$ heroku addons:create heroku-postgresql:standard-0 -a example-app -- --block-logs
Removing the Add-on
To remove your Heroku Postgres database, you must destroy the add-on:
$ heroku addons:destroy heroku-postgresql
If you have two databases of the same type you must remove the add-on using its config var name. For example, to remove the HEROKU_POSTGRESQL_GRAY_URL
database, run the command:
$ 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.
If you’ve deprovisioned your database and want it restored, we may be able to restore it within the stated continuous protection rollback period. Beyond this window, it’s unlikely we can restore your database. Open a support ticket for assistance.
We recommend that you create backups of your data before deprovisioning. You can use PG Backups to take a snapshot of your data and download your backup to store in an external storage service. You can also export your data before deprovisioning.
Support
Submit all Heroku Postgres support and runtime issues via one of the Heroku Support channels.