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

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