Heroku creates the database backups using custom format option by default. Custom format backup brings some limitations as well as advantages while moving database backup across PostgreSQL versions.
In order to import a backup taken with the custom format, it is required that target has to be in the same or higher minor release. Otherwise pg_restore will throw the below error:
pg_restore: [archiver] unsupported version (1.13) in file header
Check the source and target database versions
NOTE: For the purposes of this article, the Engine Yard database is the target and Heroku database is the source.
Check Heroku database version
heroku pg:psql -c "select version()"
Check Engine Yard database version
psql -c "select version()"
The target has the same or higher version of PostgreSQL
The backups taken by Heroku can be used without any issue.
The target has the lower version of PostgreSQL
Importing the database backup taken in the custom format into a lower version of PostgreSQL will result in an error since pg_restore utility will not allow that. So it is required to export the database in plain text.
How to export Heroku database in plain text
See the configuration for database connectivity
heroku config:get DATABASE_URL -a [application_name]
The output will look like in the below format and contain the necessary configuration to connect through your PostgreSQL client.
Using pg_dump utility export your Heroku database.
pg_dump -h [host] -p [port] -U [user_name] -d [database] > [file_name].sql
Load your database
Import your Heroku database backup using load_foreign_postgres_db.sh script that Engine Yard provides on the instance. This script works with dumps created using pg_dump, both normal SQL text dumps (if the file is compressed, make sure to decompress it first) and dumps created using the pg_dump custom format.
WARNING! The script drops and recreates the database named [app_name]. The script assigns ownership of all non-system tables, views, sequences, and functions in the restored database to the deploy user. If the target database has data that you do not want to lose or if you need a more customized restore, submit a ticket with Engine Yard Support.
sudo /engineyard/bin/load_foreign_postgres_db.sh [filename] [app_name]
[app_name] is the name of the database.
[filename] is the name of the database backup file
sudo /engineyard/bin/load_foreign_postgres_db.sh latest.dump myapp