Обсуждение: 32-bit to 64-bit migration options

On Fri, 2012-02-10 at 07:45 -0700, Greg Spiegelberg wrote:
> All,
>
> I'm planning a migration for a customer with a PostgreSQL 8.4 database
> cluster running CentOS 4.8 32-bit.  The target platform is CentOS 6.2
> 64-bit and will be running PostgreSQL 8.4 (our application delivers and
> supports 8.4, don't bother bringing up 9.x).  If this were a small database
> cluster I wouldn't worry about it however the 8.4 database cluster is about
> 900 GB right now.  The documented and proper way to move this data is via a
> dump-restore however I'm not sure my customer wants days or potentially
> weeks of downtime so I'm searching for options.
>
> Option 1: dump-restore
> I've performed a handful of these for other customers and even the 100 GB
> database cluster using the network transfer method "pg_dumpall | ssh target
> -c 'cat - | psql postgres'" can be slow as in 8+ hours.
>

Right.

> Option 2: Slony-I
> Is Slony-I an alternative when moving data from 32-bit to 64-bit?
>

Yes, absolutely.

> Option 3: pg_upgrade
> Is this an option?  Remember, I'm going from 8.4 32-bit to 8.4 64-bit.
>

No because of the 32/64 bit change.

> Option 4: PITR
> I believe this is not a possibility because of the bit-ness change but I'm
> listing anyways in case I'm mistaken.
>

No because of the 32/64 bit change.

> Did I miss anything?
>

Well, you can also use Londiste, and probably Bucardo to do this. But I
guess they are your only options with Slony.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Greg Spiegelberg <gspiegelberg@gmail.com> wrote:

> I'm planning a migration for a customer with a PostgreSQL 8.4
> database cluster running CentOS 4.8 32-bit.  The target platform
> is CentOS 6.2 64-bit and will be running PostgreSQL 8.4 (our
> application delivers and supports 8.4, don't bother bringing up
> 9.x).

Just out of curiosity, have you found an incompatibility that
prevents your application from running on 9.0 or 9.1, or haven't you
gotten around to trying it yet?  If there is an incompatibility,
what is it?  (It's useful to the project to have some idea what
causes pain to users, so we can minimize that when possible.)

> If this were a small database cluster I wouldn't worry about it
> however the 8.4 database cluster is about 900 GB right now.  The
> documented and proper way to move this data is via a dump-restore
> however I'm not sure my customer wants days or potentially weeks
> of downtime so I'm searching for options.
>
> Option 1: dump-restore
> I've performed a handful of these for other customers and even the
> 100 GB database cluster using the network transfer method
> "pg_dumpall | ssh target -c 'cat - | psql postgres'" can be slow
> as in 8+ hours.

This is the simplest option.  If you have control over the hardware
environment I would recommend *not* using ssh.  A few other tips:

We found it to make a noticeable difference when we used a
cross-wired cable for a direct connection rather than going through
a switch.

We found that setting up the users and databases first and doing
pg_dump per database with the psql -1 option (to use a single
transaction) to make a big difference.  Something like:

  pg_dump -h host dbname | psql -1 dbname

We tested conversions with different configuration files and found
it ran much faster when the target had the "running with scissors"
settings: fsync = off, full_page_writes = off, synchronous_commit =
off, etc.  Be sure to set these back afterward.

You should really schedule a VACUUM FREEZE ANALYZE for sometime soon
after the bulk load.  When possible we do this before letting users
in.  When we need to minimize down time, we just run an ANALYZE on
key tables first, let people in, run ANALYZE on the whole database,
then run VACUUM FREEZE ANALYZE while people are working.

> Option 2: Slony-I
> Is Slony-I an alternative when moving data from 32-bit to 64-bit?

My shop has never used it, but I've heard a lot of others talk about
having done so with good results.  Down time can be measured in
single-digit minutes this way.

> Option 3: pg_upgrade
> Is this an option?  Remember, I'm going from 8.4 32-bit to 8.4
> 64-bit.

Not an option.  The on-disk format will be different.

> Option 4: PITR
> I believe this is not a possibility because of the bit-ness change
> but I'm listing anyways in case I'm mistaken.

Not an option.  The on-disk format will be different.

-Kevin