Re: [GENERAL] pg_dump --data-only: is dump ordered to keep foreign-key-relations

[Florian G. Pflug]

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 this

foreign-key contraints during the restore

2) If all your foreign keys are defined as "deferrable", you can

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 foreign keys during the import. 1) relies on the fact the the
dump doesn't containt foreign-key violations.

greetings, Florian Pflug

1) 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 wrap the data-loading in a transaction, and do "set contraints all deferred" before loading the data. This will defer the constraint-checks 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

smime.p7s
Description: S/MIME Cryptographic Signature