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

[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
>