Обсуждение: How do you upgrade for production servers?
Hi all, This is a general question about which procedure you follow when you upgrade your productions servers. Let's say we have a server running and older version and I want to install the latest version on the same server. Do you install slony? do you dump all the databases, install the latest version and after you restore? any other procedure? I'd like to have my production server down as less time as possible. Thank you very much and have a great new year :D -- Arnau
If upgrading between minor versions in the same series (e.g. 8.1.3 - 8.1.5) you can simply use the same data directory. However, if the major version changes (e.g. 8.1.x - 8.2.x), you must: 1. Dump the databases from your old server (preferably using the new version client, I believe) while your old server is still running 2. Stop your old version 3. Start your new version 4. Restore the backup into your new version For the minimum downtime, the best thing to do is get the 2 servers running together (e.g. run your new version on port 5433) - then dump your old database, stop your old server (so no updates can get in after your backup), restore the backup into the new server, and restart your new server on port 5432. It all depends on how big your databases are, and the length of time it'll take to restore your backup. Happy New Year to you too. Andy. Arnau wrote: > Hi all, > > This is a general question about which procedure you follow when you > upgrade your productions servers. Let's say we have a server running > and older version and I want to install the latest version on the same > server. Do you install slony? do you dump all the databases, install > the latest version and after you restore? any other procedure? I'd > like to have my production server down as less time as possible. > > > Thank you very much and have a great new year :D -- Andy Shellam NetServe Support Team the Mail Network "an alternative in a standardised world" p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834
Realistically you should include some time for testing ... a few things may fail to work (not many -- the core is quite goodabout legacy issues, me thinks), but you may well run into some things that take longer to run that they used to. ExplainAnalyze is your faithful companion, as is the performance list. Better to spend an extra day or two than start gettingphone calls and pages, IMHO. SO do a trial run, hopefully test it with real queries for speed, desired results andso on. (Generally we've found that newer versions do run faster but there are exceptions.) Greg Williamson DBA GlobeXplorer LLC -----Original Message----- From: pgsql-admin-owner@postgresql.org on behalf of Andy Shellam (Mailing Lists) Sent: Fri 12/29/2006 4:42 AM To: arnaulist@andromeiberica.com; pgsql-admin@postgresql.org Cc: Subject: Re: [ADMIN] How do you upgrade for production servers? If upgrading between minor versions in the same series (e.g. 8.1.3 - 8.1.5) you can simply use the same data directory. However, if the major version changes (e.g. 8.1.x - 8.2.x), you must: 1. Dump the databases from your old server (preferably using the new version client, I believe) while your old server is still running 2. Stop your old version 3. Start your new version 4. Restore the backup into your new version For the minimum downtime, the best thing to do is get the 2 servers running together (e.g. run your new version on port 5433) - then dump your old database, stop your old server (so no updates can get in after your backup), restore the backup into the new server, and restart your new server on port 5432. It all depends on how big your databases are, and the length of time it'll take to restore your backup. Happy New Year to you too. Andy. Arnau wrote: > Hi all, > > This is a general question about which procedure you follow when you > upgrade your productions servers. Let's say we have a server running > and older version and I want to install the latest version on the same > server. Do you install slony? do you dump all the databases, install > the latest version and after you restore? any other procedure? I'd > like to have my production server down as less time as possible. > > > Thank you very much and have a great new year :D -- Andy Shellam NetServe Support Team the Mail Network "an alternative in a standardised world" p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ------------------------------------------------------- Click link below if it is SPAM gsw@globexplorer.com "https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45950c6642261055016784&user=gsw@globexplorer.com&retrain=spam&template=history&history_page=1" !DSPAM:45950c6642261055016784! -------------------------------------------------------
Arnau <arnaulist@andromeiberica.com> writes: > Hi all, > > This is a general question about which procedure you follow when > you upgrade your productions servers. Let's say we have a server > running and older version and I want to install the latest version > on the same server. Do you install slony? do you dump all the > databases, install the latest version and after you restore? any > other procedure? I'd like to have my production server down as less > time as possible. From personal experience, the Slony aided upgrade between Postgres major versions results in the least downtime. The negatives are that if you've never worked with Slony before, getting familiar with it and then R&D needed to make sure your upgrade goes smoothly can be very time costly. You also have to be comfortable with the substantial complexity that Slony adds to the DB while in use. Still if you have DBs that would take hours or days to load from dump files, and business can't allow a service window for doing this, Slony is the way. I have a vague notion that someone was working on a contrib (maybe unofficial) package for fast upgrade but you'll have to investigate. HTH > Thank you very much and have a great new year :D > -- > Arnau > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- ------------------------------------------------------------------------------- Jerry Sievers 305 854-3001 (home) Production Database Administrator 305 321-1144 (mobil WWW E-Commerce Consultant
I install the new version to a new directory (eg, /usr/local/pg-8.2 ) then start that server on a different port to the live server (eg, 54321), create a new data directory (eg. /home/postgres/pgData-8.2) dump the live database, import to the new database to make sure it's all good, then once I know that's all good, I'll drop the live database back to "single user mode" using pg_hba.conf, dump the database again, shutdown the old version, restart the new version on the normal port, re-import the data then re-enable normal access with pg_hba.conf I have the advantage that our database generally isn't accessed out of hours, so I have the luxury to be able to have the server offline for 30 - 60 minutes while the second dump and import is happening. Depends how big your database is as to how long the server will be unavailable. At least by installing the new version to a new directory and doing a test import first lets me see how (if) the new version will affect the data without having the live server offline. Cheers, -p On Friday 29 December 2006 23:06, Arnau wrote: > Hi all, > > This is a general question about which procedure you follow when you > upgrade your productions servers. Let's say we have a server running and > older version and I want to install the latest version on the same > server. Do you install slony? do you dump all the databases, install the > latest version and after you restore? any other procedure? I'd like to > have my production server down as less time as possible. > > > Thank you very much and have a great new year :D -------------------------------------------------------