Skip Navigation
Show nav
Heroku Dev Center
  • Get Started
  • Documentation
  • Changelog
  • Search
  • Get Started
    • Node.js
    • Ruby on Rails
    • Ruby
    • Python
    • Java
    • PHP
    • Go
    • Scala
    • Clojure
  • Documentation
  • Changelog
  • More
    Additional Resources
    • Home
    • Elements
    • Products
    • Pricing
    • Careers
    • Help
    • Status
    • Events
    • Podcasts
    • Compliance Center
    Heroku Blog

    Heroku Blog

    Find out what's new with Heroku on our blog.

    Visit Blog
  • Log inorSign up
View categories

Categories

  • Heroku Architecture
    • Dynos (app containers)
    • Stacks (operating system images)
    • Networking & DNS
    • Platform Policies
    • Platform Principles
  • Command Line
  • Deployment
    • Deploying with Git
    • Deploying with Docker
    • Deployment Integrations
  • Continuous Delivery
    • Continuous Integration
  • Language Support
    • Node.js
    • Ruby
      • Working with Bundler
      • Rails Support
    • Python
      • Working with Django
      • Background Jobs in Python
    • Java
      • Working with Maven
      • Java Database Operations
      • Working with Spring Boot
      • Java Advanced Topics
    • PHP
    • Go
      • Go Dependency Management
    • Scala
    • Clojure
  • Databases & Data Management
    • Heroku Postgres
      • Postgres Basics
      • Postgres Getting Started
      • Postgres Performance
      • Postgres Data Transfer & Preservation
      • Postgres Availability
      • Postgres Special Topics
    • Heroku Data For Redis
    • Apache Kafka on Heroku
    • Other Data Stores
  • Monitoring & Metrics
    • Logging
  • App Performance
  • Add-ons
    • All Add-ons
  • Collaboration
  • Security
    • App Security
    • Identities & Authentication
    • Compliance
  • Heroku Enterprise
    • Private Spaces
      • Infrastructure Networking
    • Enterprise Accounts
    • Enterprise Teams
    • Heroku Connect (Salesforce sync)
      • Heroku Connect Administration
      • Heroku Connect Reference
      • Heroku Connect Troubleshooting
    • Single Sign-on (SSO)
  • Patterns & Best Practices
  • Extending Heroku
    • Platform API
    • App Webhooks
    • Heroku Labs
    • Building Add-ons
      • Add-on Development Tasks
      • Add-on APIs
      • Add-on Guidelines & Requirements
    • Building CLI Plugins
    • Developing Buildpacks
    • Dev Center
  • Accounts & Billing
  • Troubleshooting & Support
  • Integrating with Salesforce
  • Databases & Data Management
  • Heroku Postgres
  • Postgres Basics
  • Managing Heroku Postgres using the CLI

Managing Heroku Postgres using the CLI

English — 日本語に切り替える

Last updated February 15, 2023

Table of Contents

  • pg:info
  • pg:psql
  • pg:push and pg:pull
  • pg:ps, pg:kill, pg:killall
  • pg:promote
  • Promotions When Using Connection Pooling
  • pg:credentials
  • pg:reset

Heroku Postgres is integrated directly into the Heroku CLI and offers many helpful commands that simplify common database tasks.

pg:info

To see all PostgreSQL databases provisioned by your application and the identifying characteristics of each (such as database size, status, number of tables, and PG version), use the heroku pg:info command:

$ heroku pg:info -a example-app
=== HEROKU_POSTGRESQL_RED
Plan         Standard 0
Status       available
Data Size    82.8 GB
Tables       13
PG Version   12.5
Created      2012-02-15 09:58 PDT
=== HEROKU_POSTGRESQL_GRAY
Plan         Standard 2
Status       available
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 -a example-app

pg:psql

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
Connecting to HEROKU_POSTGRESQL_RED... done
psql (12.5, server 12.5)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

rd2lk8ev3jt5j50=> SELECT * FROM users;

The database located at DATABASE_URL is used by default. If you have more than one database, specify the database you want to connect to as the first argument to the command. You can use just the color as a shorthand:

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

pg:push and pg:pull

For a more in-depth guide on working with backups, read the import and export guide.

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 DATABASE_URL mylocaldb --app example-app

This command creates a new local database named mylocaldb and then pulls data from the database at DATABASE_URL from the app example-app. You can also specify the name of the database add-on you want to pull data from:

$ heroku pg:pull postgresql-animate-91581 mylocaldb --app example-app

To prevent accidental data overwrites and loss, the local database must not exist. You’ll be prompted to drop an 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 DATABASE_URL mylocaldb --app example-app

As with all pg:* commands, you can use shorthand database identifiers here. For example, to pull data from HEROKU_POSTGRESQL_RED on the app example-app, you could run heroku pg:pull example-app::RED mylocaldb.

pg:push

pg:push pushes data from a local database into a remote Heroku Postgres database. The command looks like this:

$ heroku pg:push mylocaldb DATABASE_URL --app example-app

This command takes the local database mylocaldb and pushes it to the database at DATABASE_URL on the app example-app. You can also specify the name of the database add-on you want to push data to:

$ heroku pg:push postgresql-animate-91581 mylocaldb --app example-app

To prevent accidental data overwrites and loss, the remote database must be empty. You’ll be prompted to pg:reset a remote database that isn’t empty.

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

$ PGUSER=postgres PGPASSWORD=password heroku pg:pull DATABASE_URL mylocaldb --app example-app

Troubleshooting

These commands rely on the pg_dump and pg_restore binaries that are included in a Postgres installation. It’s 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: 12.5; pg_dump version: 10.14
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_activity view 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 and it attempts to cancel the query. If it fails, use the --force option to issue pg_terminate_backend to drop 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 cancels or terminates every query on your database.

pg:promote

Promote a database to be the primary for your app with the heroku pg:promote command.

pg:promote updates the value of the DATABASE_URL config var with the newly promoted database’s connection string. It also creates an alternate attachment for the old primary database, assigned with a new HEROKU_POSTGRESQL_<color>_URL config var. The promotion process triggers a release and restarts the app.

$ heroku pg:promote HEROKU_POSTGRESQL_GRAY_URL -a example-app
Ensuring an alternate alias for existing DATABASE_URL... HEROKU_POSTGRESQL_PINK_URL
Promoting HEROKU_POSTGRESQL_GRAY_URL to DATABASE_URL... done

Running pg:promote for a follower database doesn’t automatically make it unfollow its leader. Run pg:unfollow on followers to stop replication before promoting it.

 

After a promotion, the demoted database still exists and incurs charges. Delete your old primary database with heroku addons:destroy HEROKU_POSTGRESQL_COLOR.

Promotions When Using Connection Pooling

If your old primary used Connection Pooling attached with the default name DATABASE_CONNECTION_POOL, pg:promote reattaches the connection pooler to the new primary.

If you use Connection Pooling attachments under non-default names, you must update them after running pg:promote. Activate Connection Pooling on your new primary to repoint the connection pooling URL:

$ heroku pg:connection-pooling:attach DATABASE_URL --as MY_DATABASE_CONNECTION_POOL -a example-app

pg:credentials

Heroku Postgres provides convenient access to the credentials and location of your database. This makes it easier to use a GUI to access your instance.

The database name argument must be provided with pg:credentials:url command. Use DATABASE for your primary database.

$ heroku pg:credentials:url 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’s a good security practice to rotate the credentials for important services on a regular basis. On Heroku Postgres, you can use heroku pg:credentials:rotate to rotate credentials.

$ heroku pg:credentials:rotate HEROKU_POSTGRESQL_GRAY_URL

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, old credentials aren’t removed immediately. All of the open connections remain open until the currently running tasks complete, then those credentials are updated. This ensures 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

Keep reading

  • Postgres Basics

Feedback

Log in to submit feedback.

Upgrading the Version of a Heroku Postgres Database Provisioning Heroku Postgres

Information & Support

  • Getting Started
  • Documentation
  • Changelog
  • Compliance Center
  • Training & Education
  • Blog
  • Podcasts
  • Support Channels
  • Status

Language Reference

  • Node.js
  • Ruby
  • Java
  • PHP
  • Python
  • Go
  • Scala
  • Clojure

Other Resources

  • Careers
  • Elements
  • Products
  • Pricing

Subscribe to our monthly newsletter

Your email address:

  • RSS
    • Dev Center Articles
    • Dev Center Changelog
    • Heroku Blog
    • Heroku News Blog
    • Heroku Engineering Blog
  • Heroku Podcasts
  • Twitter
    • Dev Center Articles
    • Dev Center Changelog
    • Heroku
    • Heroku Status
  • Facebook
  • Instagram
  • Github
  • LinkedIn
  • YouTube
Heroku is acompany

 © Salesforce.com

  • heroku.com
  • Terms of Service
  • Privacy
  • Cookies
  • Cookie Preferences