Importing and Exporting Heroku Postgres Databases with PG Backups

Last Updated: 26 May 2015

export import pg backups postgres

Table of Contents

On the surface, PG Backups provides a way to capture regular backups of your Heroku Postgres database. However, because of its general-purpose architecture and use of standard PostgreSQL utilities, it is also a useful tool capable of exporting and importing to/from external PostgreSQL databases.

Export

PG Backups uses the native pg_dump PostgreSQL tool to create its backup files, making it trivial to export to other PostgreSQL installations. Note that the resulting backup file uses the custom format option in pg_dump. As compared to the plain-text format, the custom format options results in backup files that can be much smaller.

Download backup

To export the data from your Heroku Postgres database, create a new backup and use any number of download tools, such as curl or wget, to store the backup locally.

$ heroku pg:backups capture
$ curl -o latest.dump `heroku pg:backups public-url -a sushi`

Restore to local database

Load the dump into your local database using the pg_restore tool. If objects exist in a local copy of the database already, you might run into inconsistencies when doing a pg_restore. Pg_restore does not drop all of the objects in the database when loading the dump.

This will usually generate some warnings, due to differences between your Heroku database and a local database, but they are generally safe to ignore.

$ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump

Import

PG Backups can be used as a convenient tool to import database dumps from other sources to your Heroku Postgres database.

If you are importing data as part of the initialization of a new application you will need to first create and configure the app on Heroku before performing the import.

Create dump file

Dump your local database in compressed format using the open source pg_dump tool:

$ PGPASSWORD=mypassword pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydb > mydb.dump

Import to Heroku Postgres

In order for PG Backups to access and import your dump file you will need to upload it somewhere with an HTTP-accessible URL. We recommend using Amazon S3

Use the raw file URL in the pg:backups restore command:

Be sure to use single quotes around the temporary S3 URL, as it contains ampersands and other characters that will confuse your shell otherwise.

$ heroku pg:backups restore 'https://s3.amazonaws.com/me/items/3H0q/mydb.dump' DATABASE_URL -a sushi

Note that the pgbackups restore command drops any tables and other database objects before recreating them.

Where DATABASE represents the HEROKU_POSTGRESQL_COLOR_URL of the database you wish to restore to. You must specify a database configuration variable to restore the database.

On completion of the import process be sure to delete the dump file from its storage location if it’s no longer needed.