RE: Backup strategy

David Barron <david.barron@zencos.com> writes:
> I'm responsible for a couple of databases that have constraints and indexes on most, if not all, of the tables, which
meansthat the tables have to be backed up and restored in the correct order to take the constraints into account.  But
pg_dumpand pg_restore don't take that into account, so when doing restores I was running into errors.  Hopefully that
isclear. 

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
whatmasked 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_dumpdoesn't take that into account.  So the restore tries to restore table a first, but can't because table b
containsno data.  That's what I ran into in general terms.