Harald Armin Massa wrote: > [snipped text]
pg_dump --data-only -U user database and tried to reload the data. But it fails on foreign keys: depending tables are being dumped before the tables they depend on. I solved it by manually dumping the relevant tables and reloading them, Now I cannot find documentation - if pg_dump is supposed to produce a "ordered dump" so that not doing is a bug and I need to present a showcase - or if it is simply not implemented and an enhancement request; - or if it is even on a theoretical basis impossible to derive the correct order. [circular foreign keys came to my mind]
Hi There are three possibilities to solve this1) Use pg_dump (or pg_restore, if you are using custom-format dumps) with the --disable-trigger option (check the man-page for the exact syntax). This will disable all triggers, including those which check the
foreign-key contraints during the restore 2) If all your foreign keys are defined as "deferrable", you canwrap the data-loading in a transaction, and do "set contraints all deferred" before loading the data. This will defer the constraint-checks
until you issue commit. 3) If you have a schema and data-dump in seperate files, you could manually split the schema dump into two files, one containing all table definitions, the other containing the f-k definitions. You can then first restore the schema, then your data, and finally your foreign keys.The ordering of the three options in terms of speed is 1 < 3 < 2, I believe - but 2 and 3 give you additional security, because they check
the foreign keys during the import. 1) relies on the fact the the dump doesn't containt foreign-key violations. greetings, Florian Pflug
Attachment:
smime.p7s
Description: S/MIME Cryptographic Signature
