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