Importing and Exporting Heroku Postgres Databases
Last updated February 13, 2023
On the surface, PGBackups 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’s also a useful tool capable of exporting to or importing from external PostgreSQL databases.
PGBackups uses the native
pg_dump PostgreSQL tool to create its backup files, making it trivial to export to other PostgreSQL installations. The resulting backup file uses the custom format option in
pg_dump. As compared to the plain-text format, the custom format options result in backup files that can be much smaller.
In general, PGBackups are intended for moderately loaded databases up to 20 GB. Contention for the I/O, memory, and CPU needed for backing up a larger database becomes prohibitive at a moderate load and the longer run time increases the chance of an error that ends your backup capture prematurely. For databases that are larger than 20 GB, see Capturing Logical Backups on Larger Databases.
Capture and Download Backup with PGBackups
To export the data from your Heroku Postgres database, create a backup and download it:
$ heroku pg:backups:capture --app example-app $ heroku pg:backups:download --app example-app
Manual Dump with
If you need a partial backup of your Heroku Postgres database or a backup in a non-custom format, you can use
pg_dump to create your backup.
For example, to create a plain-text dump from your Heroku Postgres database:
$ pg_dump -Fp --no-acl --no-owner <DATABASE_CONNECTION_STRING > mydb.dump
Use any of the supported
pg_dump options as needed, such as
--table to create dumps of specific schemas or tables of your database. Read more about the supported options in the PostgreSQL documentation.
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 can run into inconsistencies when doing a
This process usually generates some warnings, due to differences between your Heroku database and a local database, but they’re generally safe to ignore.
$ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump
If you’re using an old version of
pg_restore, you can see an error such as
pg_restore: [archiver] unsupported version (1.13) in file header when you try to run
pg_restore. Ensure that the
pg_restore version you’re using is up-to-date and compatible with the version of the exported database. You can check your version of
pg_restore by running
You can optionally use the
--jobs <number of jobs> flag with
pg_restore to parallelise the restore of the dump. Only the custom and directory archive formats are supported with this option. More on this option can be found in the Postgres documentation.
PGBackups can be used as a convenient tool to import database dumps from other sources into your Heroku Postgres database.
If you’re importing data as part of the initialization of a new application, you must first create and configure the app on Heroku before performing the import.
Create Dump File
Dump your local database in compressed
custom format using the open source pg_dump tool:
# set the password in an environment variable export PGPASSWORD=mypassword # linux/mac set PGPASSWORD=mypassword # windows # create the database dump $ pg_dump -Fc --no-acl --no-owner -h localhost -U myuser -d mydb -f mydb.dump
Import to Heroku Postgres
In order for PG Backups to access and import your dump file, you must upload it somewhere with an HTTP-accessible URL.
pg:backups restore command drops any tables and other database objects before recreating them.
pg:backups:restore command expects the provided backup to use the compressed
custom format. Other backup formats result in restore errors.
$ aws s3 presign s3://your-bucket-address/your-object
Use the raw file URL in the
pg:backups restore command:
$ heroku pg:backups:restore '<SIGNED URL>' DATABASE_URL --app example-app
DATABASE_URL 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.
If you’re using a Unix-like operating system, make sure to use single quotes around the temporary S3 URL, because it can contain ampersands and other characters that confuse your shell. If you’re running Windows, you must use double-quotes.
When you’ve completed the import process, delete the dump file from its storage location if it’s no longer needed.