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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Kupershmidt | 2010-02-10 16:10:22 | Re: Disabling triggers with psql (gforge 7.4 to 8.2 migration) |
Previous Message | Iñigo Martinez Lasala | 2010-02-10 15:30:07 | Re: Disabling triggers with psql (gforge 7.4 to 8.2 migration) |