From: "Greg Sabino Mullane" <greg@turnstep.com>
To: pgsql-admin@postgresql.org
Subject: Re: Migrate postgres to newer hardware
In-Reply-To: <7965A9DCF12CC14984420BCC37B1608F25AB1AED49@Elzar.grant.co.uk>
Date: Tue, 30 Mar 2010 17:50:05 -0000
Message-ID: <d5a6b2c40ca3f2bf1df3612ee9454344@biglumber.com>


-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> What would be the easiest and fastest way to 
> migrate Postgres 8.2.24 32 BIT to a new server 64 Bit.
...
> If we cannot use PITR what would be the best approach, 
> we can't have down time I am afraid.
>
> Any ideas or suggestions would be very welcome.

You could also consider Bucardo. Its advantage is that it 
does not TRUNCATE and COPY over the entire data on startup 
(unless you ask it to), so you can use "pre-warmed" slaves 
and avoid the load and time of a full copy. So the procedure 
would be something like:

1. Install Bucardo and add all tables you want replicated.
2. Perform your usual pg_dump and restore to the new box.
   (or use a snapshot, bring up a PITR slave, etc.)
3. Drop the bucardo schema on the new box.
   Run ANALYZE on the new box.
4. Tell Bucardo about the new box, then start it up.
   This will copy over all changes made since the pg_dump.
5. Stop your app, or put the DB in readonly mode.
6. Do a final Bucardo sync of any changes since step 4.
7. Copy over any other tables that do not have primary keys.
8. Point your app at the new box.

For speed, you can do things like turn off fsync and some 
other settings on the new box until just before step 8.

And of course no matter which solution you choose, you'll 
want to test this out first using a complete copy of the db 
going from a 32-bit test box to a 64-bit test box. Heck, it 
might even be a good time to jump to version 8.4 :)

- -- 
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201003301345
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkuyOYIACgkQvJuQZxSWSsgPqwCg3DGv8O0klxkCdukecwuNyxir
TvAAni+5rbVbmwbAz3zsgahMuBHfuAUM
=ybeB
-----END PGP SIGNATURE-----