Re: restoring databases with intensive foreign key use

Hi Joel,

nice to see you here :)

--On Tuesday, May 14, 2002 09:55:05 -0400 Joel Burton <joel(at)joelburton(dot)com>
wrote:

>> -----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.

The problem is, my tables are heavily meshed ;)

> 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.

Ah, nice idea, I will check this.
>
> 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.

Of course its the most current one: 7.2 and 7.2.1

Regards
Tino