Re: Sobre Actualizacion/upgrading PostgreSQL

This is good timing... I am just working on upgrading our installation of GForge to the latest stable version and trying to do the same with PostgreSQL.
We are already running on GForge 5.5, so upgrading to the latest (5.6) will not be much of a jump. The following seems to work fairly well:
1. Create a dump file of the database schema and one of the data. Split the schema dump into the foreign keys and everything else.
2. Shutdown the PostgreSQL database & upgrade it.
3. Start up the database and import your schema except for the foreign keys. Now load the data. If you try to load your data with the foreign keys present, it will give you lots of errors for some reason.
4. Load the foreign keys.
5. Run the GForge upgrade script.

This seems to work well when upgrading to PostgreSQL 8.2, but if you go to 8.3 the changes to tsearch and some vector functions cause the import process to fail.
I believe the GForge team has a script to migrate your data from the 4.x to the 5.x schemas, but as we started using GForge recently, we've always been on 5.x.

Kenny Drobnack

From: pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Iñigo Martinez Lasala
Sent: Thursday, June 11, 2009 4:47 AM
To: pgsql-admin
Subject: Re: [ADMIN] Sobre Actualizacion

Hi Ricardo.

Remember you have to write in english in this forum. There is an specific forum in spanish language if you prefer to use spanish instead of english.

Anyway, migration from 7.4 to 8.x is not trivial. First you have to decide what is your target version. It's not the same migrate to 8.1 or 8.2 that 8.3. Migrate to 8.0, 8.1 or 8.2 is relatively easy. You have to perform an export of your database and import into new version. There could minor problems if you use tsearch (y recommend you to install tsearch in your new database before importing from 7.4) but basically everything will go without problems.

Migration to 8.3 is not so easy... Well, the process is the same, but due to changes in "tipado de los campos" you will probably have to perform some changes in your application. With < 8.3, postgres automatically converts data from some type (for example, an varchar) to destination type without asking for it. However, with 8.3 you will have to perform this conversion in an explicit way, that is, telling postgres that you have to convert it. So if the queries of your application or database functions or triggers were poorly designed (and believe me, this is very frequent) you will have to adapt them to work with 8.3 or higher. And in 8.3 tsearch2 has been included in core, so you will have to change tsearch calls or at least install the "translation" package. 

But, of course, 8.3 performance is quite better that 8.2 or 8.1. So, I will try first with 8.3 and if you don't have problems or your application doesn't require many changes, use it.

We migrated gforge 4.5 from postgres 7.4 to 8.1 two years ago and it was quite hard since we have to modify parts of data model and clean our database since there were strange characters inserted in our tables. We have planned to migrate to 8.3 this year, but we should modify gforge in order to acomplish with 8.3 requirements.... and this is quite hard. And since our gforge has been modified since 2005, not it's very difficult to revert our changes to gforge 4.7 trunk... so it's probably we will stay in 8.1 or 8.2 waiting for a future migration to Gforge AS (5).

-----Original Message-----
From: Ricardo Mercado Araneda <rmercado(at)dportales(dot)cl>
To: pgsql-admin(at)postgresql(dot)org
Subject: [ADMIN] Sobre Actualizacion
Date: Wed, 10 Jun 2009 17:16:39 -0400

Estimados:
Tengo instalado Postgresql 7.4.8 y quisiera migrar a postgres 8.x.

Mi pregunta es si hay alguna pauta para hacerlo. Solo se que puedo tener
problemas con la perdida de datos. Necesito me orientes por favor

Ricardo Mercado Araneda.

This communication is for informational purposes only. It is not
intended as an offer or solicitation for the purchase or sale of
any financial instrument or as an official confirmation of any
transaction. All market prices, data and other information are not
warranted as to completeness or accuracy and are subject to change
without notice. Any comments or statements made herein do not
necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
and affiliates.

This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and any
attachments are believed to be free of any virus or other defect
that might affect any computer system into which it is received and
opened, it is the responsibility of the recipient to ensure that it
is virus free and no responsibility is accepted by JPMorgan Chase &
Co., its subsidiaries and affiliates, as applicable, for any loss
or damage arising in any way from its use. If you received this
transmission in error, please immediately contact the sender and
destroy the material in its entirety, whether in electronic or hard
copy format. Thank you.

Please refer to http://www.jpmorgan.com/pages/disclosures for
disclosures relating to European legal entities.