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(dot)brandani(at)tech(dot)sdb(dot)it>
To: pgsql-admin(at)postgresql(dot)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.
--