1.- Capture a backup of your database in heroku
$ heroku pgbackups:capture
2.- Save the backup locally
$ curl -o latest.dump `heroku pgbackups:url`
3.- Copy the database backup file to the database instance that you want to load it on (in Engine yard).
$ scp [database file] [username]@[database host]:[target directory]/[filename]
where
[database file]
is the name of the database backup file.
[username]
is the user for the database instance. (The default user for the Engine Yard Cloud database is deploy.) [database host] is the hostname of the database instance.
[target directory]
is the directory that you want to copy the backup file to.
[filename]
is the name for the file in its new location.
for example
$ scp latest.dump deploy@ec2-172-16-139-19.us-west-1.compute.amazonaws.com:/tmp/postgres/dumpfile.dump
4.- Via SSH, connect to the application and database instance (for single server environment) or the master database instance (for a clustered environment), and change to the directory where you copied the database backup file in Step 1 (e.g.cd /tmp/postgres).
5.- Import the database backup file to the database using a script (load_foreign_postgres_db.sh) that Engine Yard provides on the instance.
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.
$ sudo /engineyard/bin/load_foreign_postgres_db.sh [filename] [app_name]