On Wednesday, August 22, 2012 05:17:10 PM Scott Briggs wrote:
> So we have a large TB database that we need to migrate to 9.1 and I'm
> wondering if there's a way to do this process in stages.
>
> Since the date/time storage types changes between 8.3 and 8.4, I
> realize we'll have to dump the database and my plan is to create a
> backup using pg_start_backup(), run that backup on another server
> using postgres 8.3 which I could then dump and import into a 9.1
> server.
>
> My question is, is there some way I can take WAL files created since
> the beginning of the original backup on the 8.3 server, convert those
> into text that I could then apply to the 9.1 server? The mysql
> equivalent to this is mysqlbinlog which you can apply to mysql binary
> log files.
The most likely way to get this done is with Slony. Setup a Slony slave,
upgrade the slave to 9.1 with a dump/reload, run it and let Slony catch it up,
and then promote it to be the Slony cluster master and switch your clients
over.
Slony is table based and certainly not trivial to setup, but it'll work. If
your change rate isn't too horrendously high, anyway.
The Slony tables need some updating after a dump/reload to fix OID issues.
There are tools included to do that. You'd need to experiment to nail down the
process. And you would want to make sure no one is making DDL changes outside
of Slony during the whole process.
If you aren't already familiar with Slony, this is probably weeks of work to
fully prepare for and get right. But it will let you avoid the downtime.