Disabling and enabling constraints and triggers to make pg_restore work - Mailing list pgsql-general

I’m trying to do a data-only pg_restore.  I’m 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 I’m 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 don’t think that would handle recursive relationships, where a
FK refers to its own table’s 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 there’s 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