At Zesty, we recently migrated a lot of data from one database into another. Both databases were using PostgreSQL and are hosted on Heroku. Using
pg_dump we successfully dumped all data (in fact, we only wanted the data and not the schema).
pg_dump -f database.dump --format=c --data-only --no-owner -T users -T users_id_seq -T schema_migrations DATABASE_URL
Above command dumps all data into a file called database.dump.
-T excludes tables and sequences and the
--data-only flag makes sure we only export the data. The schema was already recreated in the other database using Rails migrations.
The import using
pg_restore was a bit tricky since pg_restore doesn't import data in order for foreign key constraints to work. The trick here is to use
--single-transaction and make all foreign keys deferrable. Deferrable foreign keys are only evaluated when a transaction is being COMMITted and the single transaction flag makes sure that all data is imported in a single transaction.
Obviously all your data needs to be in a consistent state for the import to succeed. Otherwise, enjoy cleaning up your data :)