Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 532326328D8
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 10 Feb 2010 11:38:15 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 45220-02 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 10 Feb 2010 15:38:04 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from hermes.vectorsf.com (firewall.vectorsf.com [62.82.80.62])
	by mail.postgresql.org (Postfix) with ESMTP id 75B0B635557
	for <pgsql-admin@postgresql.org>; Wed, 10 Feb 2010 11:30:15 -0400 (AST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by hermes.vectorsf.com (Postfix) with ESMTP id BBCC111C0045;
	Wed, 10 Feb 2010 16:30:10 +0100 (CET)
X-Virus-Scanned: amavisd-new at hermes.vectorsf.com
Received: from hermes.vectorsf.com ([127.0.0.1])
	by localhost (hermes.vectorsf.com [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id SsQ8MxrVZalR; Wed, 10 Feb 2010 16:30:06 +0100 (CET)
Received: from [10.70.4.200] (deimos.vectorsf.com [10.70.4.200])
	(Authenticated sender: imartinez)
	by hermes.vectorsf.com (Postfix) with ESMTP id 8E3C911C0011;
	Wed, 10 Feb 2010 16:30:05 +0100 (CET)
Subject: Re: Disabling triggers with psql (gforge 7.4 to 8.2
 migration)
From: =?ISO-8859-1?Q?I=F1igo?= Martinez Lasala <imartinez@vectorsf.com>
To: Josh Kupershmidt <schmiddy@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
In-Reply-To: <971254BD-6919-41BB-8D26-160C14D5BFE2@gmail.com>
References: <99024f5f1002071922j5b14c350mb1612313690f16ec@mail.gmail.com>
	<87k4umg0c3.fsf@hi-media-techno.com> <1265794218.3714.12.camel@coyote>
	<971254BD-6919-41BB-8D26-160C14D5BFE2@gmail.com>
Content-Type: multipart/alternative; boundary="=-N2O5KWWwEyMAkoJN/Klz"
Date: Wed, 10 Feb 2010 16:30:07 +0100
Message-ID: <1265815807.6369.19.camel@coyote>
Mime-Version: 1.0
X-Mailer: Evolution 2.28.1 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201002/79
X-Sequence-Number: 33682


--=-N2O5KWWwEyMAkoJN/Klz
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 8bit

Hmmmm...

What about 
UPDATE pg_trigger SET tgenabled = false;

Restore data, and after done:
UPDATE pg_trigger SET tgenabled = true;

I'm going to test it.

-----Original Message-----
From: Josh Kupershmidt <schmiddy@gmail.com>
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] Disabling triggers with psql (gforge 7.4 to 8.2
migration)
Date: Wed, 10 Feb 2010 09:55:15 -0500


On Feb 10, 2010, at 4:30 AM, Iñigo Martinez Lasala wrote:
> 
> Third one, and we haven't been able to fix it, is we cannot restore
> data dump via psql due to psql does not have an option to disable
> triggers. So, when restoring data, it fails. So... is there any way to
> disable in a psql session all triggers same way like with pg_restore
> --disable-triggers?  Or can we convert plain text dump to be processed
> by pg_restore? Perhaps a table by table trigger disabling script?
> 

How about:
 1. restore your schema, with triggers, using a pg_dump
--schema-only ... ; pg_restore ... 
 2. Create a psql-loadable data-only dump using pg_dump --data-only
--disable-triggers --format=p ...
 3. clean the data-only dump and restore


Or, you could do a full pg_dump -Fc ... of your database, then use
pg_restore to restore the compressed dumpfile into a plaintext file
(i.e. pg_restore [options] filename). Then run your cleanup on the
plaintext file, and reload. 


Josh


--=-N2O5KWWwEyMAkoJN/Klz
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.28.1">
</HEAD>
<BODY>
Hmmmm...<BR>
<BR>
What about <BR>
UPDATE pg_trigger SET tgenabled = false;<BR>
<BR>
Restore data, and after done:<BR>
UPDATE pg_trigger SET tgenabled = true;<BR>
<BR>
I'm going to test it.<BR>
<BR>
-----Original Message-----<BR>
<B>From</B>: Josh Kupershmidt &lt;<A HREF="mailto:Josh%20Kupershmidt%20%3cschmiddy@gmail.com%3e">schmiddy@gmail.com</A>&gt;<BR>
<B>To</B>: I&#241;igo Martinez Lasala &lt;<A HREF="mailto:%3d%3fISO-8859-1%3fQ%3fI%3dF1igo%3f%3d%20Martinez%20Lasala%20%3cimartinez@vectorsf.com%3e">imartinez@vectorsf.com</A>&gt;<BR>
<B>Cc</B>: pgsql-admin &lt;<A HREF="mailto:pgsql-admin%20%3cpgsql-admin@postgresql.org%3e">pgsql-admin@postgresql.org</A>&gt;<BR>
<B>Subject</B>: Re: [ADMIN] Disabling triggers with psql (gforge 7.4 to 8.2 migration)<BR>
<B>Date</B>: Wed, 10 Feb 2010 09:55:15 -0500<BR>
<BR>
<BR>
On Feb 10, 2010, at 4:30 AM, I&#241;igo Martinez Lasala wrote:
<BLOCKQUOTE TYPE=CITE>
    <BR>
    Third one, and we haven't been able to fix it, is we cannot restore data dump via psql due to psql does not have an option to disable triggers. So, when restoring data, it fails. So... is there any way to disable in a psql session all triggers same way like with pg_restore --disable-triggers?&nbsp; Or can we convert plain text dump to be processed by pg_restore? Perhaps a table by table trigger disabling script?<BR>
    <BR>
</BLOCKQUOTE>
<BR>
How about:
&nbsp;1. restore your schema, with triggers, using a pg_dump --schema-only ... ; pg_restore ...&nbsp;
&nbsp;2. Create a psql-loadable data-only dump using pg_dump --data-only --disable-triggers --format=p ...
&nbsp;3. clean the data-only dump and restore
<BR>
<BR>
Or, you could do a full pg_dump -Fc ... of your database, then use pg_restore to restore the compressed dumpfile into a plaintext file (i.e. pg_restore [options] filename). Then run your cleanup on the plaintext file, and reload.&nbsp;
<BR>
<BR>
Josh
<BR>
</BODY>
</HTML>

--=-N2O5KWWwEyMAkoJN/Klz--