/ PostgreSQL

pg_restore and foreign key constraints

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 :)