Thread: pg_dump data and foreign keys

Is data dumped from pg_dump -a supposed to be in order so it doesn't cause foreign key conflicts? I'm getting dumps where tables that rely on records in other tables are appearing before them.

John

unfortunately, no. You are best served by dumping the tables manually,
one a at atime, and concatenating them in the order that they need to
be.

Of course, making a script do this means you only have to figure this
out once.

John Smith wrote:
>
> Is data dumped from pg_dump -a supposed to be in order so it doesn't
> cause foreign key conflicts? I'm getting dumps where tables that rely
> on records in other tables are appearing before them.
>
> John
>
> ----------------------------------------------------------------------
> Do you Yahoo!?
> Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!

On Fri, 28 Mar 2003, John Smith wrote:

> Is data dumped from pg_dump -a supposed to be in order so it doesn't
> cause foreign key conflicts? I'm getting dumps where tables that rely
> on records in other tables are appearing before them.

No, although you can use --disable-triggers (I think I have that right) to
get it to disable triggers on the table (which includes the foreign key
constraints).

I think there should be an option to pg_dump like so:

1/ Dump tables in any order.
2/ Convert all column constraints to table level constrants, and list
all the previously table level constraints and the newly table level
constraits at the end of the dump.
3/ List all Foreign Key constraints at the end of the dump.
4/ Generate a test of all constraints for each table in a seperte file,
to be run by the DBA should they desire. That way they can see if the
database meets all the constraints.
5/ Also generate a DELETE WHERE file for all constraints that the DBA
can run to clean up the database, with or without using '4' above.

Dennis Gearon wrote:
>
> unfortunately, no. You are best served by dumping the tables manually,
> one a at atime, and concatenating them in the order that they need to
> be.
>
> Of course, making a script do this means you only have to figure this
> out once.
>
> John Smith wrote:
> >
> > Is data dumped from pg_dump -a supposed to be in order so it doesn't
> > cause foreign key conflicts? I'm getting dumps where tables that rely
> > on records in other tables are appearing before them.
> >
> > John
> >
> > ----------------------------------------------------------------------
> > Do you Yahoo!?
> > Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly