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

Hi Josh.

I have followed your suggestion and restored bad tables into a file.
This has shortened the conversion process since I've only restored to
file bad chars tables, and only 8 showed this issue. So, instead of
having to fix full dump (about 45G) I only had to process 10MB (8
tables). This is what I've done:

- Dump schema, fix it in order to accomodate to postgres 8.2, and import
into database via psql.
- Dump data and import into database via pg_restore with triggers
disabled.

pg_dump -f gforge-database.dmp -Fc -a -O --disable-triggers -x -E utf-8
-U gforge -h 192.168.7.54 gforge -W
pg_restore -Fc -a -O --disable-triggers -x -U gforge -d gforge
gforge-database.dmp 2>import-error.log

- Locate in import-log what tables have problem (8 in my import9.
- Restore bad tables into plain text files:

pg_restore -t artifact ../gforge.dmp > artifact.dmp
pg_restore -t artifact_history ../gforge.dmp > artifact_history.dmp
pg_restore -t artifact_idx ../gforge.dmp > artifact_idx.dmp
pg_restore -t doc_data_idx ../gforge.dmp > doc_data_idx.dmp
pg_restore -t frs_release_idx ../gforge.dmp > frs_release_idx.dmp
pg_restore -t groups_idx ../gforge.dmp > groups_idx.dmp
pg_restore -t project_task_idx ../gforge.dmp > project_task_idx.dmp
pg_restore -t users_idx ../gforge.dmp > users_idx.dmp

- Modify each dump file, changing set client enconding from UTF-8 to
ISO-8859-1 (it appears that this was my problem, ISO-8859-1 chars in
database that had not been properly converted to UTF-8)
SET client_encoding = 'UTF8';

Alternatively, you could user iconv in order to eliminate bad chars
(iconv -f utf-8 -t utf-8 -c -f table_exported.dmp >
table_exported_fixed.dmp). But truncating bad chars could lead to errors
when importing since some registers could become NULL registers and fail
on import.

- Import each table DISABLING TRIGERS temporary
(add ALTER TABLE tablename DISABLE TRIGGER ALL; at beginning and ALTER
TABLE tablename ENABLE TRIGGER ALL; at the end).

Et voila. Import finished.

Thank you very much to all for your suggestions. They have been very
useful for me.

-----Original Message-----
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: [ADMIN] Disabling triggers with psql (gforge 7.4 to 8.2
migration)
Date: Wed, 10 Feb 2010 11:08:53 -0500

[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