Skip site navigation (1) Skip section navigation (2)

Re: Disabling triggers with psql (gforge 7.4 to 8.2 migration)

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: Iñigo Martinez Lasala <imartinez(at)vectorsf(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Disabling triggers with psql (gforge 7.4 to 8.2 migration)
Date: 2010-02-10 16:08:53
Message-ID: 4ec1cf761002100808q2d90917bx39c8763cf0557bbd@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
[Resending, forgot to CC list]

On Wed, Feb 10, 2010 at 10:47 AM, Josh Kupershmidt <schmiddy(at)gmail(dot)com>wrote:

>
> On Wed, Feb 10, 2010 at 10:09 AM, Iñigo Martinez Lasala <
> imartinez(at)vectorsf(dot)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
>

In response to

Responses

pgsql-admin by date

Next:From: Josh KupershmidtDate: 2010-02-10 16:10:22
Subject: Re: Disabling triggers with psql (gforge 7.4 to 8.2 migration)
Previous:From: Iñigo Martinez LasalaDate: 2010-02-10 15:30:07
Subject: Re: Disabling triggers with psql (gforge 7.4 to 8.2 migration)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group