Postgres dump inserts in order

dev-rifaii 0
Viewed 38

I'm trying to write a bash script that dumps data from remote db and restores it inside a local docker container. The problem is there are like 90 tables with a lot of foreign keys but the data dump does not seem to care about the ordering of inserts. What can I do about this so it would actually insert all rows?

This is how I'm dumping things currently

#Dumps roles
pg_dumpall --roles-only -U $user -h $db_url -p $port --clean --file=roles.dump
#Dumps schema
pg_dump --clean -s --host $db_url --port $port --user $user $db_to_dump > backup-schema.dump
#Dumps data
pg_dump --column-inserts --host $db_url --port $port --user $user $db_to_dump -n $schema_to_dump > data.dump
bash postgresql
1 Answers

That's not how you do it. If you insist in splitting data and object definitions, do it like this:

pg_dump --section=pre-data --clean -f pre.sql mydb
pg_dump --section=data --clean -f data.sql mydb
pg_dump --section=post-data --clean -f post.sql mydb

Then you restore them in that order. pre.sql will contain the object definitions, data.sql the data and post.sql all the index and constraint definitions. That way, you restore the data before restoring the constraints, and there is no problem with the order of the tables. Besides, restoring the data is much faster if the indexes and constraints are not yet defined.

Laurenz Albe0
Related