Обсуждение: restoring databases with intensive foreign key use fails
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
> -----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
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?
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