Обсуждение: pg_dump and foreign keys troubles
Hi I have a dump of a large DB executed with pg_dump (schema and data together). The original schema declares some foreign keys constaints. The problem is in the pg_dump all the foreign keys disappear from the tables because thay are rendered instead as CREATE CONSTRAINT TRIGGERS. My problem is that I have to load the dump (from a 7.3) in a brand new database (8.1) and then I need to do quite a few changes to the schema dropping some old foreign constraint and adding some different ones. Since the new datadabase restored from the dump doesn't know about the old foreign keys but has just a bunch of constraint trigger, it becomes really trichy to do this kind of job. How can I tell to pg_dump to dump the schema with foreign keys construct instead of creating constraint trigger? If it's impossible what is the common practice to work arount this problem? thanks Paolo
"Paolo Negri" <hungrylist@gmail.com> writes:
> My problem is that I have to load the dump (from a 7.3) in a brand new
> database (8.1) and then I need to do quite a few changes to the schema
> dropping some old foreign constraint and adding some different ones.
> ...
> How can I tell to pg_dump to dump the schema with foreign keys
> construct instead of creating constraint trigger?
You can't: the information just isn't there in 7.3. You'll need to look
at the trigger arguments and reconstruct the FKs.
IIRC the contrib/adddepend script might be able to help you with this.
regards, tom lane
On Tue, Nov 07, 2006 at 04:42:35PM -0500, Tom Lane wrote: > "Paolo Negri" <hungrylist@gmail.com> writes: > > My problem is that I have to load the dump (from a 7.3) in a brand new > > database (8.1) and then I need to do quite a few changes to the schema > > dropping some old foreign constraint and adding some different ones. > > ... > > How can I tell to pg_dump to dump the schema with foreign keys > > construct instead of creating constraint trigger? > > You can't: the information just isn't there in 7.3. You'll need to look > at the trigger arguments and reconstruct the FKs. > > IIRC the contrib/adddepend script might be able to help you with this. Would a 7.4 or newer pg_dump connecting to a 7.4 database know enough to do this for you? If so, Paolo could restore his dump to a 7.3 install and go from there... -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Thank you all but I've fixed the problem doing that 1) retrieved the original schema of the DB as a SQL script. 2) created a new database with just the empty schema 3) pg_restore on the new DB of a 'c' format dump of the production DB with the options --data-only and --disable-triggers thanks Paolo On 09/11/06, Jim C. Nasby <jim@nasby.net> wrote: > On Tue, Nov 07, 2006 at 04:42:35PM -0500, Tom Lane wrote: > > "Paolo Negri" <hungrylist@gmail.com> writes: > > > My problem is that I have to load the dump (from a 7.3) in a brand new > > > database (8.1) and then I need to do quite a few changes to the schema > > > dropping some old foreign constraint and adding some different ones. > > > ... > > > How can I tell to pg_dump to dump the schema with foreign keys > > > construct instead of creating constraint trigger? > > > > You can't: the information just isn't there in 7.3. You'll need to look > > at the trigger arguments and reconstruct the FKs. > > > > IIRC the contrib/adddepend script might be able to help you with this. > > Would a 7.4 or newer pg_dump connecting to a 7.4 database know enough to > do this for you? If so, Paolo could restore his dump to a 7.3 install > and go from there... > -- > Jim Nasby jim@nasby.net > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) >