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

Harald Armin Massa wrote:
> I migrated one database from 8.0 to 8.1
>
> That I used to do add "without oids" to all tables.
>
> First step so:
>
> pg_dump --schema-only -U user database
>
> the file was edited, all tables to "withoud oids"; and reloaded in 8.1
>
> After that I
>
> 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]

You can do this by creating a table of contents from your dump:
pg_restore -l -a ... > toc_file ...

Edit this to re-order the contents such that they restore in a safe
order and then restore using the modified TOC:
pg_restore -L toc_file ...

That works for me at least. It is a bit of a pain to have to do this.
Maybe there is an easier way?

Disabling triggers only affects triggers - not keys so doesn't help.

Hope that helps
Pete
--
Whitebeam Web Application Server
http://www.whitebeam.org
------