Re: Disabling and enabling constraints and triggers to make pg_restore work

"Ken Winter" <kwinter(at)umich(dot)edu> writes:
> 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.

The simplest answer is "don't do that". A full restore (schema+data)
will work correctly. A data-only restore cannot hope to guarantee
referential integrity, other than by dropping and later recreating all
the FK constraints, and there's a fatal flaw in that plan: what if it
doesn't know about all the FK constraints that are in the database it's
trying to load into? There could be permissions problems too, if you're
trying to do it as non-superuser.

> 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.

You should certainly not do anything as risky as messing with relchecks
by hand --- there was a case just a couple weeks ago where someone
destroyed his database by fat-fingering an update command like this :-(.
pg_dump has had a --disable-triggers option for years, and using that is
far safer. There's also (as of 8.1) an ALTER TABLE DISABLE TRIGGERS
command, which is even safer. However these still require superuser
privileges, because you can easily break referential consistency by
misusing them.

regards, tom lane