From: | "Ken Winter" <kwinter(at)umich(dot)edu> |
---|---|
To: | "PostgreSQL pg-general List" <pgsql-general(at)postgresql(dot)org> |
Subject: | Disabling and enabling constraints and triggers to make pg_restore work |
Date: | 2006-08-04 20:15:05 |
Message-ID: | 00fa01c6b802$ad8d1eb0$6403a8c0@kenxp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Im trying to do a data-only pg_restore. Im running into a roadblock
whenever the restore tries to populate a table with a foreign key before it
has populated the primary key table that it refers to: This violates the FK
constraint, which aborts the restore.
Given the complexity of the database Im trying to restore, the prospect of
having to manual order the restore process so that all PK tables are always
populated before all FK tables that reference them is daunting. Even if I
did that, I dont think that would handle recursive relationships, where a
FK refers to its own tables PK.
What I need is a way to disable FKs, so I can then restore all the data, and
then re-enable the FKs. I first looked for something like ALTER TABLE
mytab DISABLE CONSTRAINT mycon and ALTER TABLE mytab ENABLE CONSTRAINT
mycon à la Oracle. I finally found a French PostgreSQL forum
(http://www.postgresqlfr.org/?q=node/156#comment) that says theres no such
thing in PostgreSQL. Someone on that forum suggested update
pg_catalog.pg_class set relchecks=0 where relname =mytab to disable and
update pg_catalog.pg_class set relchecks=1 where relname =mytab to
re-enable. But to write to pg_catalog you apparently need to be a
superuser, which alas I'm not.
I also have some triggers that I think I may need to be able to disable.
pg_restore does have an option to do that, but according to
http://www.postgresql.org/docs/8.1/static/app-pgrestore.html this option is
available to superusers only.
(Perhaps I could write a program that drops all my FKs and triggers, and a
second program that recreates them after the data restore is complete. But
that seems a rather brutal and scary way to patch up a gap in the PostgreSQL
utilities.)
Any suggestions?
~ TIA
~ Ken Winter
From | Date | Subject | |
---|---|---|---|
Next Message | Jonathan Vallar | 2006-08-04 20:27:24 | Re: Dumping database using 8.1 or 7.1 |
Previous Message | Wayne Conrad | 2006-08-04 20:05:14 | Re: PITR Questions |