Re: dump/restore with a hidden dependency? - Mailing list pgsql-general

On 08/07/2014 10:00 AM, Chris Curvey wrote:

> I’ve done some searching and am coming up empty.  Is there a way to get
> pg_restore to apply constraints AFTER loading all the tables

Kinda. PostgreSQL applies constraints with hidden system-level triggers.
An easy way to turn them off is to use this syntax:

ALTER TABLE foo DISABLE TRIGGER ALL;

Then you just need to generate one of these for each of your tables, and
run it between your table schema restore, and the data import. You can
generate a script that does it all with something like this:

COPY (
   SELECT 'ALTER TABLE ' || schemaname || '.' || quote_ident(tablename)
          || ' DISABLE TRIGGER ALL;'
     FROM pg_tables
) TO '/tmp/stop_triggers.sql'

After your data is loaded, just change DISABLE to ENABLE, and run the
script again.

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email