Moving postresql 9.1 database - live

Question

This question has probably been asked and answered before, and if so, I would appreciate if that could be pointed out to me.

I have googled this point quite a bit, but I want to be very sure as to what I am doing before performing this task:

I need to move my current postresql 9.1 database to a new server. This new server will be taking over on that account and the other "turned off".

I need to do this live without, as much as possible, disturbing the users or having to shut down operation.

I also need to move without any possibility of data-loss (anything being written during the move).

Like I said above, if this is already answered/laid out anywhere, I would much appreciate being pointed to it.

Note:

I am currently running on Gentoo Linux and will be moving to a Ubuntu server.

Answer 1

If the PostgreSQL version and architecture is the same on the target so they're binary compatible: Set up streaming replication with WAL archive fallback, so the new host replicates from the old one. Enable hot_standby mode so you can connect to the new host read-only. Test. Then when you're ready to cut over, stop the old server and pg_ctl promote the new server to a read/write master.

If the PostgreSQL version is different on the target: Set up Londiste or Slony-I logical replication from the old DB to the new one. Let it sync up. When ready to switch over, shut down the old server, stop the replication service, switch clients to write to the new server.