Could be a valid strategy to dump in tar format, untar files, fix via iconv each .dat file and tar them together again?
... and use pg_restore with --disable-triggers in order to avoid constraints, of course.
-----Original Message-----
From: Iñigo Martinez Lasala <
imartinez@vectorsf.com>
To: pgsql-admin <
pgsql-admin@postgresql.org>
Subject: [ADMIN] Disabling triggers with psql (gforge 7.4 to 8.2 migration)
Date: Wed, 10 Feb 2010 10:30:18 +0100
Hi everybody.
We are migrating an ancient gforge postgres 7.4 database to a less ancient postgres 8.2 one. We have not upgraded to 8.3 or 8.4 due to problems with implicit conversion for data types (we should had to review all sql code and we don't have time enough to do it). It's a quite large database, with a 45GB dump.
We are facing some problems with this.
First one is tsearch. It has changed from postgres 7.4 to 8.2, but I think we have solved. We have dumped schema of 7.4, removed all tsearch2 related and launch new tsearch2 from contrib folder in 8.2. We did it with a previous migration from 7.4 to 8.1 (two years ago) and everything worked, so we think this has been solved.
Second one is database has lot of stuff and bad ascii codes inside. We can dump, but we cannot restore due to malformed UTF-8 characters. We also solved this problem by facing a long conversion process with iconv (from utf-8 to utf-8) removing bad chars. It appears postgres 7.4 didn't checked utf-8 chars but 8.1 and 8.2 do it. We have performed a data dump in plain text to use iconv for fixing this.
Third one, and we haven't been able to fix it, is we cannot restore data dump via psql due to psql does not have an option to disable triggers. So, when restoring data, it fails. So... is there any way to disable in a psql session all triggers same way like with pg_restore --disable-triggers? Or can we convert plain text dump to be processed by pg_restore? Perhaps a table by table trigger disabling script?
Thanks in advance.
Of course, if there is an easier way to proceed with a 7.4 to 8.2 gforge database migration, It would be fantastic if anybody can explain me how to do it. :-)