From: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org> |
---|---|
To: | Harald Armin Massa <haraldarminmassa(at)gmail(dot)com> |
Cc: | PostgreSQL-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: pg_dump --data-only: is dump ordered to keep foreign-key-relations |
Date: | 2005-11-24 20:00:31 |
Message-ID: | 43861BDF.6070909@phlo.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
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
foreign-key contraints during the restore
2) If all your foreign keys are defined as "deferrable", you can
wrap 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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Wilson | 2005-11-24 20:16:37 | Re: pg_dump --data-only: is dump ordered to keep foreign-key-relations |
Previous Message | Qingqing Zhou | 2005-11-24 19:59:28 | Re: "invalid page header in block 597621 of relation..."error |