thank you very much for those solutions; I shortened them, resend them for "increased google presence" :)
1) Use [...] --disable-trigger option [...]
2) foreign keys as "deferrable", do "set contraints alldeferred" before loading the data.
3) split the schema dump into two files, a) table definitions, b) f-k definitions. Restore a)- data-b)
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.
That really helps me to overcome my "dump the affected tables manually and reload them " approach.
- is pg_dump supposed to produce a "ordered dump" with --data-only?
(within documentation there is only a warning that --table will not dump the depending ones)
[than I have to create a extract to reproduce that "bug"]
- or if it is simply not implemented and an enhancement request;
(so I should file an enhancement-request and a "warning" -patch to the pg_dump docu, that pg_dump --dataonly MUST be used with --disable-trigger or similiar for backup-purposes)
- or if it is even on a theoretical basis impossible to derive the correct order. [circular foreign keys came to my mind] -> I also should submit a patch to documentation
Thanks for the solutions again, will really help in the upcoming 120 database reloads.
Harald
GHUM Harald Massa
persuasion python postgresql
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
