Re: upgrade postgres 8.1.21 to version 8.3.6

With a 1GB database you should have no problem to perform upgrade in three quarters of hour.

You should script database migration process in order to make it faster.
Upgrading binaries is really simple. A yum upgrade should be enough.

However, prior upgrading binaries, you should perform a database dump. What I do:

- Stop access to postgres.
- Dump databases.
- Stop postgres 8.1
- Move datafiles or rename 8.1 datafile folder. (rolling back is no time consuming). With RHEL, postgresql.conf and pg_hba.conf are in the same folder that datafile folders, so you will have everything in place in order to roll back if needed.
- Upgrade postgres to 8.3 (or uninstall 8.1 and install 8.3).
- Modify postgresql.conf (and pg_hba.conf if you don't reuse 8.1 one). Of course, you should have a postgresql.conf/pg_hba.conf modified form a testing environment.
- Restore dumps.

However, your main problem will not be the upgrade itself. There are plenty changes between 8.3. Main problematic changes are tsearch modules (textual search) and data type checks.
I strongly suggest to perform an upgrade in a non-production server and check all your software with postgres 8.3 prioir upgrading production servers. I've migrated several postgres from 8.1 to 8.3 and we had problems with ALL servers. Fixes were simple in some cases and more complicated with tsearch2, having to modify database schema.
You will probably have to fix some queries to deal with data type checks (in postgres 8.1, you can assign a text value to an integer without problem. In postgres 8.3 and higher you have to perform an explicit cast conversion... for example,  SELECT integer FROM column WHERE value='12' must be rewritten as SELECT integer FROM column WHERE value=12 or SELECT integer FROM column WHERE value='12'::integer).

By the way, why not migrate to 8.4? You will find same problems that with 8.3 and has better performance. Parallel restore in 8.4 is fantastic.


-----Original Message-----
From: Silvio Brandani <silvio.brandani@tech.sdb.it>
To: pgsql-admin@postgresql.org
Subject: [ADMIN] upgrade postgres 8.1.21 to version 8.3.6
Date: Tue, 13 Jul 2010 11:30:20 +0200

We need to upgrade the postgres running on our production system under 
Red Hat Enterprise Linux Server release 5.1  from version 8.1.21 to 
version 8.3.6.

we could have a stop/maintenance window of 3/4 our the sum of size of 
databases is around  1G .
Which is the best practice to execute such upgrade with possible 
rollback operation to gain in 3/4 hour this job ??
Any suggestion higly appreciated

SB

---






Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/news/privacy.html
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/codice_penale_616.html
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.
--