From: Josh Kupershmidt <schmiddy@gmail.com>
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Disabling triggers with psql (gforge 7.4 to 8.2 migration)
Date: Wed, 10 Feb 2010 11:08:53 -0500
Message-ID: <4ec1cf761002100808q2d90917bx39c8763cf0557bbd@mail.gmail.com> (raw)
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>
[Resending, forgot to CC list]
On Wed, Feb 10, 2010 at 10:47 AM, Josh Kupershmidt <schmiddy@gmail.com>wrote:
>
> On Wed, Feb 10, 2010 at 10:09 AM, Iñigo 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 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.
>>
>>
> 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=c --file=test.schema.pgdump test
> pg_restore -Ujosh --dbname=restoration test.schema.pgdump
> pg_dump --data-only -Ujosh --format=p --disable-triggers
> --file=test.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 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 with, say, --format=c into a
> plaintext file loadable by SQL:
>
> pg_dump -Ujosh --format=c --file=test.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 to
> 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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: schmiddy@gmail.com, imartinez@vectorsf.com
Subject: Re: Disabling triggers with psql (gforge 7.4 to 8.2 migration)
In-Reply-To: <4ec1cf761002100808q2d90917bx39c8763cf0557bbd@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox