Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id F3F3E633C2D for ; Wed, 10 Feb 2010 11:11:43 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 73879-06-4 for ; Wed, 10 Feb 2010 15:11:22 +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 47EB8632202 for ; Wed, 10 Feb 2010 11:09:58 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by hermes.vectorsf.com (Postfix) with ESMTP id DFDE211C00DE for ; Wed, 10 Feb 2010 16:09:53 +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 5WpT0um4alX7 for ; Wed, 10 Feb 2010 16:09:49 +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 62FBB11C0046 for ; Wed, 10 Feb 2010 16:09:49 +0100 (CET) Subject: Re: Disabling triggers with psql (gforge 7.4 to 8.2 migration) From: =?ISO-8859-1?Q?I=F1igo?= Martinez Lasala To: pgsql-admin 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="=-AcNicroz14NxwO2pcPLt" Date: Wed, 10 Feb 2010 16:09:51 +0100 Message-ID: <1265814591.6369.16.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/78 X-Sequence-Number: 33681 --=-AcNicroz14NxwO2pcPLt Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: 8bit Two questions. I could, of course, create a data-only dump (in fact I've already done it). However, when restoring, I cannot use pg_restore since it's in plain format, don't you? pg_restore only works with tar or compressed formats, I think. I could restore data-only dump with psql, but then I've to disable triggers and psql does not have an option in order to disable them. Second one. You say I could restore a compressed dumpfile into a plaintext file. Is this possible? How? And after cleaning this plaintext file, how do I restore it again into database without using psql since pg_restore only accept tar or compressed file formats? Thank you, Josh. -----Original Message----- From: Josh Kupershmidt To: Iñigo Martinez Lasala Cc: pgsql-admin 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 --=-AcNicroz14NxwO2pcPLt Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: 7bit Two questions.

I could, of course, create a data-only dump (in fact I've already done it). However, when restoring, I cannot use pg_restore since it's in plain format, don't you? pg_restore only works with tar or compressed formats, I think. I could restore data-only dump with psql, but then I've to disable triggers and psql does not have an option in order to disable them.

Second one. You say I could restore a compressed dumpfile into a plaintext file. Is this possible? How? And after cleaning this plaintext file, how do I restore it again into database without using psql since pg_restore only accept tar or compressed file formats?

Thank you, Josh.


-----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
--=-AcNicroz14NxwO2pcPLt--