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:
- sync your db to a standby server
- 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:
- 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
- split braining the DRBD (i.e., bringing up the Passive side as a DRBD Primary)
- 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
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