Good day all
I’m responsible for a couple of databases that have constraints and indexes on most, if not all, of the tables, which means that the tables have to be backed up and restored in the correct order to take the constraints into account. But pg_dump and pg_restore don’t take that into account, so when doing restores I was running into errors. Hopefully that is clear.
The solution I found was to use the –section option with pg_dump, like this:
pg_dump -h <server> -U <username> --if-exists -f /<backup path>/predata.sql -F p --section=pre-data -s -c <database>
pg_dump -h <server> -U <username> -f /<backup path>/data -F d -j 8 --section=data <database>
pg_dump -h <server> -U <username> -f /<backup path>/postdata.sql -F p --section=post-data -s <database>
This leaves me with two .sql files and a data directory containing compressed files of the data. The first sql file drops all the tables and schemas, then recreates them without most constraints or indexes. The second sql file creates all the constraints and indexes. The restore process that I use is like this:
psql -h <server> -U <username> -d <database> -f /<backup path>/predata.sql
pg_restore -h <server> -U <username> -d <database> -F d -j 8 --disable-triggers /<backup path>/data
psql -h <server> -U <username> -d <database> -f /<backup path>/postdata.sql
That works well in most cases, but not all. For some tables the predate.sql file still contains constraints inside the CREATE TABLE statements. So to get things to work I have to manually move the constraints out of the predate.sql and into the postdata.sql. Tedious.
First question, why are some constraints, but not all, still written to predate.sql? I assume it’s because the table was created that way originally?
Second, is there a way to make certain constraints are not written to predate.sql? I don’t see an option for pg_dump that will do that but it’s worth the ask.
Third, is there a better technique for backing up and restoring databases with constraints?
Thanks