upgrade postgres 8.1.21 to version 8.3.6

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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: upgrade postgres 8.1.21 to version 8.3.6

Am Dienstag 13 Juli 2010 11:30:20 schrieb Silvio Brandani:
> 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.

Have a look here:
http://wiki.postgresql.org/wiki/RPM_Installation#How_do_I_pe rform_a_major_upgrade.3F
You should test the restore of your dump on another port or maschine first.
this might be of interest for you as well:
http://blog.endpoint.com/2010/01/postgres-upgrades-ten-probl ems-and.html

> Which is the best practice to execute such upgrade with possible
> rollback operation

Do a filesystem backup.
You should test the restore of your dump on another port or maschine first.

> to gain in 3/4 hour this job ??

That depends on your hardware.

Jens

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: upgrade postgres 8.1.21 to version 8.3.6

2010/7/13 Silvio Brandani :
> We need to upgrade the postgres running on our production system under Red
> Hat Enterprise Linux Server release 5.1 =A0from 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 =A01G .
> 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

We use slony to upgrade in place. total downtime is measured in
seconds, minutes at the most.

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: upgrade postgres 8.1.21 to version 8.3.6

--=-WOqm+FaOkk26nPluTGfw
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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.=20
- 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.=20
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=3D'12'
must be rewritten as SELECT integer FROM column WHERE value=3D12 or SELEC=
T
integer FROM column WHERE value=3D'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
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=20
Red Hat Enterprise Linux Server release 5.1 from version 8.1.21 to=20
version 8.3.6.

we could have a stop/maintenance window of 3/4 our the sum of size of=20
databases is around 1G .
Which is the best practice to execute such upgrade with possible=20
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 access=
o e agli altri Suoi diritti, sono riportate alla pagina http://www.savino=
delbene.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. 6=
16 codice penale http://www.savinodelbene.com/codice_penale_616.html
L'Azienda non si assume alcuna responsabilità giuridica qualora perv=
engano da questo indirizzo messaggi estranei all'attività lavorativa=
o contrari a norme.
--




--=-WOqm+FaOkk26nPluTGfw
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit








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 <>

To:

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 

Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale 

L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.

--

Re: upgrade postgres 8.1.21 to version 8.3.6

Ciao Silvio,

On Tue, 13 Jul 2010 22:52:51 +0200, Iñigo Martinez Lasala
> You should script database migration process in order to make it faster=
..
> Upgrading binaries is really simple. A yum upgrade should be enough.

Yes, faster and repeatable. Something you can launch with a script that
does everything from scratch. As Iñigo was pointing out, you must pa=
y a lot
of attention in setting up a test environment which helps you:

* simulate the migration process (and gives you accurate measures of
times)
* test the applications

As Iñigo said, on a typical usage pattern, the most likely set of er=
rors
you will encounter are data type checks which require casting. Therefore =
it
becomes crucial to:

* setup the test database (preferably on a different server)
* setup test applications that interface with the new Postgres database

I assume you are using 8.3 because of RPM availability, isn't it?
Otherwise, you should think of 8.4.

Ciao,
Gabriele

--=20
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
Gabriele.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: upgrade postgres 8.1.21 to version 8.3.6

Gabriele Bartolini ha scritto:
> Ciao Silvio,
>
> On Tue, 13 Jul 2010 22:52:51 +0200, Iñigo Martinez Lasala
> =20
>> You should script database migration process in order to make it faste=
r.
>> Upgrading binaries is really simple. A yum upgrade should be enough.
>> =20
>
> Yes, faster and repeatable. Something you can launch with a script that
> does everything from scratch. As Iñigo was pointing out, you must =
pay a lot
> of attention in setting up a test environment which helps you:
>
> * simulate the migration process (and gives you accurate measures of
> times)
> * test the applications
>
> As Iñigo said, on a typical usage pattern, the most likely set of =
errors
> you will encounter are data type checks which require casting. Therefor=
e it
> becomes crucial to:
>
> * setup the test database (preferably on a different server)
> * setup test applications that interface with the new Postgres database
>
> I assume you are using 8.3 because of RPM availability, isn't it?
> Otherwise, you should think of 8.4.
>
> Ciao,
> Gabriele
>
> =20

I have simulate the upgrade process in a test environment -
The casting problem in effect occurs, we will provide the required=20
modification at application code before proceeding with the upgrade.
thanks a lot -
Regards,


--

---






Utilizziamo i dati personali che la riguardano esclusivamente per nostre =
finalit=E0 amministrative e contabili, anche quando li comunichiamo a ter=
zi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e=
agli altri Suoi diritti, sono riportate alla pagina http://www.savinodel=
bene.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. 6=
16 codice penale http://www.savinodelbene.com/codice_penale_616.html
L'Azienda non si assume alcuna responsabilit=E0 giuridica qualora perveng=
ano da questo indirizzo messaggi estranei all'attivit=E0 lavorativa o con=
trari a norme.
--

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin