David Barron <david.barron@xxxxxxxxxx> writes:
> 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.
Your statement is clear, but you have not provided any explanation of
*why* pg_dump fails to cope with your database. It generally does manage to handle foreign key constraints without help.
> The solution I found was to use the -section option with pg_dump, like this:
Splitting up the dump is frequently counterproductive, so I have a feeling this isn't the best way to proceed. In any case, --section still emits all the same items in the same order, so it's not really likely to fix problems. Probably what masked the issue is your use of --disable-triggers.
regards, tom lane
If, for example, table a has a constraint that references rows in table b, table b has to be restored first, but pg_dump doesn't take that into account. So the restore tries to restore table a first, but can't because table b contains no data. That's what I ran into in general terms.
