Thread: restoring databases with intensive foreign key use fails

restoring databases with intensive foreign key use fails

From
Tino Wildenhain
Date:
Hi,

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.

Regards
Tino

Re: restoring databases with intensive foreign key use fails

From
"Joel Burton"
Date:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tino Wildenhain
> Sent: Tuesday, May 07, 2002 6:15 PM
> To: pgsql-general@postgresql.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@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant


Re: restoring databases with intensive foreign key use

From
Stephan Szabo
Date:
On Wed, 8 May 2002, Tino Wildenhain wrote:

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

I haven't used any of the custom dump formats, but the text
one should put the constraint information at the bottom after
table and data creation as create constraint trigger statements.
What does your dump look like?



Re: restoring databases with intensive foreign key use

From
Tino Wildenhain
Date:
Hi Joel,

nice to see you here :)

--On Tuesday, May 14, 2002 09:55:05 -0400 Joel Burton <joel@joelburton.com>
wrote:

>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org
>> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tino Wildenhain
>> Sent: Tuesday, May 07, 2002 6:15 PM
>> To: pgsql-general@postgresql.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