Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id DF216632FDA for ; Wed, 10 Feb 2010 12:09:42 -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 85906-07 for ; Wed, 10 Feb 2010 16:09:32 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.27]) by mail.postgresql.org (Postfix) with ESMTP id 1696B632AE5 for ; Wed, 10 Feb 2010 12:09:13 -0400 (AST) Received: by qw-out-2122.google.com with SMTP id 5so39904qwd.61 for ; Wed, 10 Feb 2010 08:09:13 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type; bh=IfGHIzzt5l6EyDM5gHw2r8CLpnySi+AD71pqsxGWY0c=; b=CiEDTJmCQcGiT5OM6NH4hZ1lbs/TL3Cden2RtyBFzT8JLCJTG02Cmfx8Qax8r/EBc3 ndB+l/2Iw0jTmbqr8G+R00dXUxCKG5KHbSWLcB9HehkiAlBU9ENk3PnfEFx2GV9SMUv2 mi2fyQcp4rT8pT52DuIg/7lp1kxNjTTod0Ezc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=KzHq00ny/p+MQZote6qEAcmqejg9kWl6MVKtPUdwOp6annnOsK2yJsGfPbBRBZgkBg 6kWJmAMUngZI8J6g5n/xJma+XWq6iaIA/TZ7eZl7MyIKVP8mmFRhWwlhd1hvcnviOAzb 4k9oMLZbde+sAVHRuj4ypQpgoTII5NqgLWpPc= MIME-Version: 1.0 Received: by 10.229.213.11 with SMTP id gu11mr211172qcb.5.1265818153196; Wed, 10 Feb 2010 08:09:13 -0800 (PST) In-Reply-To: <4ec1cf761002100747w6502cc87u6843f3ac3da8a13b@mail.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> <1265814591.6369.16.camel@coyote> <4ec1cf761002100747w6502cc87u6843f3ac3da8a13b@mail.gmail.com> From: Josh Kupershmidt Date: Wed, 10 Feb 2010 11:08:53 -0500 Message-ID: <4ec1cf761002100808q2d90917bx39c8763cf0557bbd@mail.gmail.com> Subject: Re: Disabling triggers with psql (gforge 7.4 to 8.2 migration) To: =?ISO-8859-1?Q?I=F1igo_Martinez_Lasala?= Cc: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=0016362842645f1c6e047f414165 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.474 tagged_above=-10 required=5 tests=AWL=0.124, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/80 X-Sequence-Number: 33683 --0016362842645f1c6e047f414165 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable [Resending, forgot to CC list] On Wed, Feb 10, 2010 at 10:47 AM, Josh Kupershmidt wrot= e: > > On Wed, Feb 10, 2010 at 10:09 AM, I=F1igo Martinez Lasala < > imartinez@vectorsf.com> wrote: > >> 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 plai= n >> 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 disabl= e >> triggers and psql does not have an option in order to disable them. >> >> > Here's what I meant by the first routine. Let's dump database "test" and > restore into database "restoration". > > pg_dump --schema-only -Ujosh --format=3Dc --file=3Dtest.schema.pgdump t= est > pg_restore -Ujosh --dbname=3Drestoration test.schema.pgdump > pg_dump --data-only -Ujosh --format=3Dp --disable-triggers > --file=3Dtest.data.pgdump test > # clean up test.data.pgdump here > psql -Ujosh restoration < test.data.pgdump > > So for the restoration of the actual data, you'd use psql, but the > disabling of triggers would be handled for you (you should see ALTER TABL= E > ... DISABLE TRIGGER ALL; and ALTER TABLE ... ENABLE TRIGGER ALL; or simil= ar > in test.data.pgdump) > > Second one. You say I could restore a compressed dumpfile into a plaintex= t >> 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 onl= y >> accept tar or compressed file formats? >> > > To turn a pg_dump file which was dumped with, say, --format=3Dc into a > plaintext file loadable by SQL: > > pg_dump -Ujosh --format=3Dc --file=3Dtest.Fc.pgdump test > pg_restore test.Fc.pgdump > test.plaintext.pgdump > # clean up test.plaintext.pgdump here > psql -Ujosh restoration < test.plaintext.pgdump > > This was the second option I mentioned. You would then have to use psql t= o > restore this plaintext file. You might be able to jump through some hoops > and turn the plaintext dump back into a pg_restore compressed dump, but I > don't see any point in this -- the plaintext dump here should have the > CREATE TRIGGER statements after the table creation and population steps, > which is likely exactly what you want. > > Josh > --0016362842645f1c6e047f414165 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable [Resending, forgot to CC list]

On Wed, Fe= b 10, 2010 at 10:47 AM, Josh Kupershmidt <schmiddy@gmail.com> wrote:

On Wed, Feb 10, 2010 at 10= :09 AM, I=F1igo Martinez Lasala <imartinez@vectorsf.com> wrote:
=20 =20
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 pla= in format, don't you? pg_restore only works with tar or compressed form= ats, I think. I could restore data-only dump with psql, but then I've t= o disable triggers and psql does not have an option in order to disable the= m.


Here's what I meant by the first = routine. Let's dump database "test" and restore into database= "restoration".

=A0 pg_dump --schema-only -Ujosh --format= =3Dc --file=3Dtest.schema.pgdump test
=A0 pg_restore -Ujosh --dbname=3Drestoration test.schema.pgdump
=A0 pg_d= ump --data-only -Ujosh --format=3Dp --disable-triggers --file=3Dtest.data.p= gdump test
=A0 # clean up test.data.pgdump here
=A0 psql -Ujosh resto= ration < test.data.pgdump

So for the restoration of the actual data, you'd use psql, but the = disabling of triggers would be handled for you (you should see ALTER TABLE = ... DISABLE TRIGGER ALL; and ALTER TABLE ... ENABLE TRIGGER ALL; or similar= in test.data.pgdump)

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?

To turn a pg_dump file which was dumped w= ith, say, --format=3Dc into a plaintext file loadable by SQL:

=A0 p= g_dump -Ujosh --format=3Dc --file=3Dtest.Fc.pgdump test
=A0 pg_restore t= est.Fc.pgdump > test.plaintext.pgdump
=A0 # clean up test.plaintext.pgdump here
=A0 psql -Ujosh restoration &l= t; test.plaintext.pgdump

This was the second option I mentioned. You= would then have to use psql to restore this plaintext file. You might be a= ble to jump through some hoops and turn the plaintext dump back into a pg_r= estore compressed dump, but I don't see any point in this -- the plaint= ext dump here should have the CREATE TRIGGER statements after the table cre= ation and population steps, which is likely exactly what you want.=A0
=A0
Josh

--0016362842645f1c6e047f414165--