pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedupgrade postgres 8.1.21 to version 8.3.6
6+ messages / 5 participants
[nested] [flat]
* upgrade postgres 8.1.21 to version 8.3.6
@ 2010-07-13 09:30 Silvio Brandani <silvio.brandani@tech.sdb.it>
2010-07-13 10:58 ` Re: upgrade postgres 8.1.21 to version 8.3.6 Jens Wilke <jens.wilke@affinitas.de>
2010-07-13 16:35 ` Re: upgrade postgres 8.1.21 to version 8.3.6 Scott Marlowe <scott.marlowe@gmail.com>
2010-07-13 20:52 ` Re: upgrade postgres 8.1.21 to version 8.3.6 Iñigo Martinez Lasala <imartinez@vectorsf.com>
0 siblings, 3 replies; 6+ messages in thread
From: Silvio Brandani @ 2010-07-13 09:30 UTC (permalink / raw)
To: pgsql-admin
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.
--
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: upgrade postgres 8.1.21 to version 8.3.6
2010-07-13 09:30 upgrade postgres 8.1.21 to version 8.3.6 Silvio Brandani <silvio.brandani@tech.sdb.it>
@ 2010-07-13 10:58 ` Jens Wilke <jens.wilke@affinitas.de>
2 siblings, 0 replies; 6+ messages in thread
From: Jens Wilke @ 2010-07-13 10:58 UTC (permalink / raw)
To: pgsql-admin
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_perform_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-problems-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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: upgrade postgres 8.1.21 to version 8.3.6
2010-07-13 09:30 upgrade postgres 8.1.21 to version 8.3.6 Silvio Brandani <silvio.brandani@tech.sdb.it>
@ 2010-07-13 16:35 ` Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Scott Marlowe @ 2010-07-13 16:35 UTC (permalink / raw)
To: Silvio Brandani <silvio.brandani@tech.sdb.it>; +Cc: pgsql-admin
2010/7/13 Silvio Brandani <silvio.brandani@tech.sdb.it>:
> 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
We use slony to upgrade in place. total downtime is measured in
seconds, minutes at the most.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: upgrade postgres 8.1.21 to version 8.3.6
2010-07-13 09:30 upgrade postgres 8.1.21 to version 8.3.6 Silvio Brandani <silvio.brandani@tech.sdb.it>
@ 2010-07-13 20:52 ` Iñigo Martinez Lasala <imartinez@vectorsf.com>
2010-07-14 07:50 ` Re: upgrade postgres 8.1.21 to version 8.3.6 Gabriele Bartolini <Gabriele.Bartolini@2ndQuadrant.it>
2 siblings, 1 reply; 6+ messages in thread
From: Iñigo Martinez Lasala @ 2010-07-13 20:52 UTC (permalink / raw)
To: Silvio Brandani <silvio.brandani@tech.sdb.it>; +Cc: pgsql-admin
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.
--
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: upgrade postgres 8.1.21 to version 8.3.6
2010-07-13 09:30 upgrade postgres 8.1.21 to version 8.3.6 Silvio Brandani <silvio.brandani@tech.sdb.it>
2010-07-13 20:52 ` Re: upgrade postgres 8.1.21 to version 8.3.6 Iñigo Martinez Lasala <imartinez@vectorsf.com>
@ 2010-07-14 07:50 ` Gabriele Bartolini <Gabriele.Bartolini@2ndQuadrant.it>
2010-07-14 14:44 ` Re: upgrade postgres 8.1.21 to version 8.3.6 Silvio Brandani <silvio.brandani@tech.sdb.it>
0 siblings, 1 reply; 6+ messages in thread
From: Gabriele Bartolini @ 2010-07-14 07:50 UTC (permalink / raw)
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>; +Cc: Silvio Brandani <silvio.brandani@tech.sdb.it>; pgsql-admin
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 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. 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
--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
Gabriele.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: upgrade postgres 8.1.21 to version 8.3.6
2010-07-13 09:30 upgrade postgres 8.1.21 to version 8.3.6 Silvio Brandani <silvio.brandani@tech.sdb.it>
2010-07-13 20:52 ` Re: upgrade postgres 8.1.21 to version 8.3.6 Iñigo Martinez Lasala <imartinez@vectorsf.com>
2010-07-14 07:50 ` Re: upgrade postgres 8.1.21 to version 8.3.6 Gabriele Bartolini <Gabriele.Bartolini@2ndQuadrant.it>
@ 2010-07-14 14:44 ` Silvio Brandani <silvio.brandani@tech.sdb.it>
0 siblings, 0 replies; 6+ messages in thread
From: Silvio Brandani @ 2010-07-14 14:44 UTC (permalink / raw)
To: Gabriele Bartolini <Gabriele.Bartolini@2ndQuadrant.it>; +Cc: Iñigo Martinez Lasala <imartinez@vectorsf.com>; pgsql-admin
Gabriele Bartolini ha scritto:
> 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 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. 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
>
>
I have simulate the upgrade process in a test environment -
The casting problem in effect occurs, we will provide the required
modification at application code before proceeding with the upgrade.
thanks a lot -
Regards,
--
---
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.
--
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-07-14 14:44 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-13 09:30 upgrade postgres 8.1.21 to version 8.3.6 Silvio Brandani <silvio.brandani@tech.sdb.it>
2010-07-13 10:58 ` Jens Wilke <jens.wilke@affinitas.de>
2010-07-13 16:35 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-07-13 20:52 ` Iñigo Martinez Lasala <imartinez@vectorsf.com>
2010-07-14 07:50 ` Gabriele Bartolini <Gabriele.Bartolini@2ndQuadrant.it>
2010-07-14 14:44 ` Silvio Brandani <silvio.brandani@tech.sdb.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox