Обсуждение: 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 quandoli 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 eventualiallegati, 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. --
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
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.
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
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. --
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
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 quandoli 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 eventualiallegati, 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. --