From: John Rouillard <rouilj@renesys.com>
To: Kenneth Marshall <ktm@rice.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: How to upgrade PostgreSQL minor releases without a dump/restore?
Date: Fri, 4 Mar 2011 17:27:43 +0000
Message-ID: <20110304172743.GL2027@renesys.com> (raw)
In-Reply-To: <20110304143746.GE8169@aart.is.rice.edu>
References: <003701cbd7e5$b8eb1d90$2ac158b0$@com>
<1298966686.2128.16.camel@lenovo01-laptop03.gunduz.org>
<004501cbd7ef$4c02d240$e40876c0$@com>
<201103031943.46705.uwe@bunspace.com>
<20110304143746.GE8169@aart.is.rice.edu>
On Fri, Mar 04, 2011 at 08:37:46AM -0600, Kenneth Marshall wrote:
> On Thu, Mar 03, 2011 at 07:43:46PM -0800, Uwe Schroeder wrote:
> > > > Did you read the paragraph above?
> > > > Install latest version, and restart postmaster.
> > > Installing of latest version as-is will keep overwriting the existing
> > > installed directories/files/binaries but not the "/usr/local/pgsql/data/"
> > > directory right? Since this is our production server database, am just
> > > clarifying this question that came to my mind, before upgrading.
> > For sake of safety, I'd always make a backup of the data
> > directory. You don't need to dump/restore. Just copy the files
> > someplace else and then do the update. In case something goes
> > wrong you can always go back to your old version and just copy the
> > backup to the data directory.
> The "Just copy the files someplace else" can take a long, long
> time for a large database and you need to have the database off-line
> for the copy to be correct. Not really an option in many environments.
You can use something like rsync quite effectively in this case. Take
your first copy while the database is up. This moves the majority of
the data. Then take down the database and re-sync. Rsync will move
only the changed bits. Depending on how much of your data churns I
have seen the second rsync take under a minute where the first one was
running for 6 or so hours. The restore worked perfectly. Now if you
have a multi TB db YMMV.
An alternative is to do a PITR image backup. So tell postgres to start
archiving wal logs as part of PIRT, backup the database cluster, tell
postgres the backup is done, copy the archived wal logs to a safe
spot.
--
-- rouilj
John Rouillard System Administrator
Renesys Corporation 603-244-9084 (cell) 603-643-9300 x 111
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: rouilj@renesys.com, ktm@rice.edu
Subject: Re: How to upgrade PostgreSQL minor releases without a dump/restore?
In-Reply-To: <20110304172743.GL2027@renesys.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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