-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> My best idea so far is to do a pg_dump and somehow archive all the DML
> in the original db from that point in time for later insertion in the
> new db, but I dont know how that would be done practically. And I
> dont even know if thats the best way to go, as I said, its only an
> idea.
What you need is a replication system. Take a look at Slony or Bucardo.
Basically, you copy over everything except for the data to the new
database, switch the replication system on, let it catch up, then
stop apps from hitting the server, wait for the new one to catch up,
and point your apps to the new one.
Important factors that you left out are exactly how big your database is,
what version you are on, what version you are moving to, and how busy your
system is. Also keep in mind that both Bucardo and Slony are trigger based
on primary keys or unique indexes, so tables without such constraints
cannot be replicated: you'll need to either add a unique constraint to
the tables, or copy them separately (e.g. pg_dump -t tablename or
Bucardo's fullcopy mode).
If you weren't also moving your OS and server, pg_migrator (aka pg_upgrade)
might work for you as well: it does an inplace, one-time upgrade but only
supports a limited number of versions at the moment.
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201006110927
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAkwSOk8ACgkQvJuQZxSWSsgt6QCfYgx6mBibJjNNY88iPBOJNmSL
+FAAoLEVuYUw/VJWg3tRC25VH+ZrNsiH
=yhFJ
-----END PGP SIGNATURE-----