Thread: Migrating from 8.3 to 9.1 - date/time storage types do not match

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.

Cheers,
Scott

On 08/22/12 2:17 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.


postgres makes changes to the binary format with every x.y version.
there's far more changes than just the binary date/time format.


those WAL files contain, for all practical purposes, binary deltas to
the data files.  they can only be applied to an exact file by file
duplicate of the original.  when you do your dump/import from 8.x to
9.1, there's no resemblance between the original and 9.1 files.



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

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.

On Wednesday, August 22, 2012 02:43:05 PM Alan Hodgson wrote:
> 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.
>

Or, of course, just setup a clean 9.1 and let Slony populate it. Duh. Too long
since I did that ...