[ale] Sync Postgres to second server for fault tolerance

[Michael B. Trausch]

On Tue, 28 Jul 2009, Geoffrey wrote:

> James Taylor wrote:
>> I need a method to keep a second postgres database in sync so that I
>> can quickly make the second server live in the event of a system
>> failure, or if I need to do maintenance.
>>
>> I found this, which looks fairly easy to implement.
>>
>> https://developer.skype.com/SkypeGarage/DbProjects/SkyTools Is
>> recommended, or is the another, better solution?
>
> Sorry, never heard of it.
>
> I've done extensive research as well as two different implementations
> for just such a situation.  Wal shipping or slony.  Both have their pros
> and cons.  Both are relatively painless to set up.

The first thing I would do is consult the PostgreSQL documentation.  While 
PostgreSQL does not provide a replication solution, it does provide a great 
deal of information.  You will need to consult the documentation for the major 
series (x.y, where either number is different).

The PostgreSQL 8.4 documentation suggests several situations, and 
software/hardware to deal with them:

  Shared Disk Failover: use NAS
  File System Replication: use DRBD
  Warm Standby: use WAL shipping with PITR
  Master/Slave Replication: use Slony
  Statement-based replication: use pgpool-II
  Asynchronous Multimaster Replication: use Bucardo

WAL shipping enables you to run a server in constant-recovery mode; it's a 
"warm" standby, in that it doesn't just automatically come up.  In order to 
use it, you must ensure that all WAL records are shipped and the DB is 
current, and then switch it from recovery mode to running.  When you're ready 
to put it back, then, you switch back to recovery mode and begin reading WAL 
records again.

 	--- Mike