Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id CDAA6632AD2
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 10 Feb 2010 05:37:52 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 84685-10 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 10 Feb 2010 09:37:42 +0000 (UTC)
X-Greylist: delayed 00:07:15.332995 by SQLgrey-1.7.6
Received: from hermes.vectorsf.com (firewall.vectorsf.com [62.82.80.62])
	by mail.postgresql.org (Postfix) with ESMTP id E0D3163265C
	for <pgsql-admin@postgresql.org>; Wed, 10 Feb 2010 05:37:41 -0400 (AST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by hermes.vectorsf.com (Postfix) with ESMTP id 4D16D11C00DE
	for <pgsql-admin@postgresql.org>; Wed, 10 Feb 2010 10:30:22 +0100 (CET)
X-Virus-Scanned: amavisd-new at hermes.vectorsf.com
Received: from hermes.vectorsf.com ([127.0.0.1])
	by localhost (hermes.vectorsf.com [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id fOa1jvBxSQDP for <pgsql-admin@postgresql.org>;
	Wed, 10 Feb 2010 10:30:17 +0100 (CET)
Received: from [10.70.4.200] (deimos.vectorsf.com [10.70.4.200])
	(Authenticated sender: imartinez)
	by hermes.vectorsf.com (Postfix) with ESMTP id E771F11C003F
	for <pgsql-admin@postgresql.org>; Wed, 10 Feb 2010 10:30:17 +0100 (CET)
Subject: Disabling triggers with psql (gforge 7.4 to 8.2 migration)
From: =?ISO-8859-1?Q?I=F1igo?= Martinez Lasala <imartinez@vectorsf.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
In-Reply-To: <87k4umg0c3.fsf@hi-media-techno.com>
References: <99024f5f1002071922j5b14c350mb1612313690f16ec@mail.gmail.com>
	<87k4umg0c3.fsf@hi-media-techno.com>
Content-Type: multipart/alternative; boundary="=-D5z9+Hk3Yp/orCborDHx"
Date: Wed, 10 Feb 2010 10:30:18 +0100
Message-ID: <1265794218.3714.12.camel@coyote>
Mime-Version: 1.0
X-Mailer: Evolution 2.28.1 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201002/75
X-Sequence-Number: 33678


--=-D5z9+Hk3Yp/orCborDHx
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

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. :-)



--=-D5z9+Hk3Yp/orCborDHx
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.28.1">
</HEAD>
<BODY>
Hi everybody.<BR>
<BR>
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.<BR>
<BR>
We are facing some problems with this.<BR>
<BR>
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.<BR>
<BR>
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.<BR>
<BR>
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?&nbsp; Or can we convert plain text dump to be processed by pg_restore? Perhaps a table by table trigger disabling script?<BR>
<BR>
Thanks in advance.<BR>
<BR>
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. :-)<BR>
<BR>
<BR>
</BODY>
</HTML>

--=-D5z9+Hk3Yp/orCborDHx--