Re: restoring databases with intensive foreign key use fails

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Tino Wildenhain
> Sent: Tuesday, May 07, 2002 6:15 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] restoring databases with intensive foreign key use
> fails
>
> restoring the database only from pg_dump/pg_restore
> seems to be impossible if one uses foreign keys much.
> The tables referenced are most of the time not available
> by the time the referencing tables are created. Even
> restoring by OID order does not help.
>
> How are people doing this? The only solution I found was
> editing the restore script by hand and transform all
> constraints to ALTER TABLE statements at the end.
>
> The other problem was that there are apparently no user
> information in the dump to restore users too.
>
> What solutions are available?
>
> I've tried to go thru the source code of pg_dump
> buts a bit organic ;) I think it schould move
> all constraints out of the table definition and
> put them after the whole restore.

Tino --

Hi. Good to see a familiar face from the zope.org list here.

What version of PG are you using? I remember problems with foreign key
dependencies, but it's been a while since I've seen it happen.

If you use pg_dumpall, you'll get the statements to recreate users (&
groups, etc.). `pg_dumpall -g` will give you just this information, if
you've dumped the databases separately.

Of course, one solution would be a sed/perl/python/whatever script to easily
pull the constraints to the end, but first let us know what PG ver this is.

- J.

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant