I am new to database design. I have a task to create a high-availability cluster. I Googled a lot, however I haven’t found any good way to do this on PostgreSQL.

Where should I start and what are the steps involved in creating a high-availability cluster?

There are many ways to set up an HA cluster for PostgreSQL. Some ways are big and tough.

The easiest method is the following:

1. sync your db to a standby server
2. Install heartbeat and do a asymmetric clustering

This method is the easiest method to build a HA cluster with graceful degradation .

Most methods of Replication are usually asynchronous. I recommend the following:

• Install PostgreSQL on two DB servers.
• Install DRBD on two DB servers
• install either ucarp or Linux Heartbeat to create
  • DB VIP
  • Automatic Failover
• Mount DRBD Primary on default PostgreSQL data
• Copy PostgreSQL data into it
• Startup PostgreSQL on DRBD Primary

Remember, DRBD is synchronous disk-level replication.

UPDATE 2012-10-18 12:01 EDT

Since DRBD is Active/Passive Network RAID-1, doing STONITH (Shoot the Other Node in the Head) should be accommodated by properly scripted automatic failover. In addition, the Passive Size (aka DRBD Secondary) does not have PostgreSQL running. DRBD will simple have the network RAID-1 setup perform disk replication. The upscript you set up for ucarp or Heartbeat should be responsible for being pessimistic by doing the following:

1. assuming DBVIP
   • a. If DBVIP is still in use on DRBD Primary, DRBD Secondary cannot assume DBVIP
   • b. If DBVIP is not in use on DRBD Primary, DRBD Secondary can assume DBVIP
2. split braining the DRBD (i.e., bringing up the Passive side as a DRBD Primary)
3. mounting /dev/drbd0 on /var/lib/pgsql
4. starting up PostgreSQL

If you are using a DBVIP that only comes up on one server only, do not worry about the split-brain scenario. Whoever has the DBVIP will determine where data are written. If the DRBD Secondary (aka Passive Size) executes the aforementioned four steps, you must make sure the upscript for ucarp or HeartBeat detects that the DBVIP (i.e., runs ping -c on DBVIP and comes back with nothin) is available for you before you run ip addr add DBVIP/32 dev (interface).

Once an automatic failover has occurred, your metaphorically handle STONITH by running the downscript for ucarp or HeartBeat to

• ShutDown PostgreSQL
• Unmount /var/lib/pgsql from /dev/drbd0
• drbdadm disconnect drbd0
• drbdadm secondary drbd0

Then, when you know DBVIP is on the new Primary, goto the new DRBD Primary and run drbdadm connect drbd0 and monitor cat /proc/drbd to make sure the new DRBD Primar syncs up the new DRBD Secondary