[ADMIN] 32-bit to 64-bit migration options

[Greg Spiegelberg]

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.

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

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

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.


Did I miss anything?

TIA,
Greg

[Guillaume Lelarge]

Post by Greg Spiegelberg
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.

Post by Greg Spiegelberg
Option 2: Slony-I
Is Slony-I an alternative when moving data from 32-bit to 64-bit?

Yes, absolutely.

Post by Greg Spiegelberg
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.

Post by Greg Spiegelberg
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.

Post by Greg Spiegelberg
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

[Kevin Grittner]

Post by Greg Spiegelberg
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.)

Post by Greg Spiegelberg
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.

Post by Greg Spiegelberg
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.

Post by Greg Spiegelberg
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.

Post by Greg Spiegelberg
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