Re: Migrate postgres to newer hardware

From: "Greg Sabino Mullane" <greg(at)turnstep(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Migrate postgres to newer hardware
Date: 2010-03-30 17:50:05
Message-ID: d5a6b2c40ca3f2bf1df3612ee9454344@biglumber.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin


-----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(at)turnstep(dot)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-----

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Alex Hunsaker 2010-03-30 20:28:09 Re: ERROR: could not open segment 1 of relation 1663/743352/743420 (target block 6407642): No such file or directory
Previous Message Greg Smith 2010-03-30 17:05:40 Re: Migrate postgres to newer hardware