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
