Re: Disabling triggers with psql (gforge 7.4 to 8.2 migration) - Mailing list pgsql-admin

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@gmail.com>
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>
Cc: pgsql-admin@postgresql.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@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