Thread: Fast major-version upgrade (was: [GENERAL] postgresql 8.0 advantages)
On Sun, Feb 27, 2005 at 09:27:41PM +0100, Tino Wildenhain wrote: > Hi, > > Am Samstag, den 26.02.2005, 15:35 -0600 schrieb Jim C. Nasby: > > On Sat, Feb 26, 2005 at 01:27:55AM -0800, Jeff Davis wrote: > > > On Fri, 2005-02-25 at 09:20 -0800, Si Chen wrote: > > > > Thanks! > > > > > > > > Is there any documentation on how to upgrade to 8.0? > > > > > > > > Is it possible to upgrade from 7.4 to 8.0 while keeping a production > > > > database running "in place"? Or should I install 8.0 in another > > > > directory/machine and then restore the database into it? Are there any > > > > incompatibilities/modifications to databases from the earlier veresion > > > > required? > > > > > > > > > > You might want to check out Slony-I <http://www.slony.info> for the > > > purpose of upgrading. Slony is actually a replication engine, but it can > > > be used to upgrade with little or no downtime. Otherwise, just > > > dump/upgrade/restore like normal. > > > > Has anyone tried moving a database from one location to another on the > > HD? I'd like to use slony to minimize downtime, but I'd also like my > > data to end up in the same place it is right now when I'm done. > > I used a straight copy of the filesystem with running database > (over the net in my case) and immediately after that, > stop the db and rsync for the last changes. This took only > 10 minutes (compared to 1.5h for the full filesystem copy) > and I could start up the db in new location. > > this could work for you too. I hadn't thought about using rsync; that's a great idea! Is there somewhere this could be documented? In an FAQ maybe? -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Mon, Feb 28, 2005 at 09:27:46PM +0100, Martijn van Oosterhout wrote: > On Mon, Feb 28, 2005 at 01:36:59PM -0600, Jim C. Nasby wrote: > > > I used a straight copy of the filesystem with running database > > > (over the net in my case) and immediately after that, > > > stop the db and rsync for the last changes. This took only > > > 10 minutes (compared to 1.5h for the full filesystem copy) > > > and I could start up the db in new location. > > > > > > this could work for you too. > > > > I hadn't thought about using rsync; that's a great idea! > > > > Is there somewhere this could be documented? In an FAQ maybe? > > It works only in the special case where the PostgreSQL version number > is the same and you're running on the same platform. How often are you > transferring databases like that. Even transferring from i386 to amd64 > wouldn't work like this AFAIUI. Absolutely true, although in the case of database version PostgreSQL will check that itself. But in the context this was originally brought up in (using Sloney to upgrade a machine from 7.4.x to 8.x), it would work great, and rsync would make a huge difference in downtime. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
Re: Fast major-version upgrade (was: [GENERAL] postgresql 8.0 advantages)
From
Martijn van Oosterhout
Date:
On Mon, Feb 28, 2005 at 01:36:59PM -0600, Jim C. Nasby wrote: > > I used a straight copy of the filesystem with running database > > (over the net in my case) and immediately after that, > > stop the db and rsync for the last changes. This took only > > 10 minutes (compared to 1.5h for the full filesystem copy) > > and I could start up the db in new location. > > > > this could work for you too. > > I hadn't thought about using rsync; that's a great idea! > > Is there somewhere this could be documented? In an FAQ maybe? It works only in the special case where the PostgreSQL version number is the same and you're running on the same platform. How often are you transferring databases like that. Even transferring from i386 to amd64 wouldn't work like this AFAIUI. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Jim C. Nasby wrote: > > > Has anyone tried moving a database from one location to another on the > > > HD? I'd like to use slony to minimize downtime, but I'd also like my > > > data to end up in the same place it is right now when I'm done. > > > > I used a straight copy of the filesystem with running database > > (over the net in my case) and immediately after that, > > stop the db and rsync for the last changes. This took only > > 10 minutes (compared to 1.5h for the full filesystem copy) > > and I could start up the db in new location. > > > > this could work for you too. > > I hadn't thought about using rsync; that's a great idea! > > Is there somewhere this could be documented? In an FAQ maybe? Added to documentation, patch attached. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: doc/src/sgml/backup.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/backup.sgml,v retrieving revision 2.56 diff -c -c -r2.56 backup.sgml *** doc/src/sgml/backup.sgml 25 Feb 2005 04:56:01 -0000 2.56 --- doc/src/sgml/backup.sgml 17 Mar 2005 05:01:54 -0000 *************** *** 374,379 **** --- 374,388 ---- </para> <para> + Another option is to use <application>rsync</> to perform a file + system backup. First, while the database server is running, + run <application>rsync</>, then shut down the database + server and perform a second <application>rsync</>, then + restart the database server. This allows a file system backup to be + performed with minimal downtime. + </para> + + <para> Note that a file system backup will not necessarily be smaller than an SQL dump. On the contrary, it will most likely be larger. (<application>pg_dump</application> does not need to dump