Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Oct 17 '12 at 18:35

This question came from our site for professional and enthusiast programmers.

1  
Look into repmgr (repmgr.org) and barman (pgbarman.org) –  Craig Ringer Oct 17 '12 at 22:20
    
Is Postgres-XC consider production ready? If so, it seems like an interesting option. It is a PostgreSQL based cluster solution. postgres-xc.sourceforge.net Here is an example how to easily do a test setup: michael.otacoo.com/postgresql-2/… –  JohnP Oct 20 '12 at 17:05
add comment

2 Answers

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 .

share|improve this answer
2  
further methods please have a look at postgresql.org/docs/9.1/static/log-shipping-alternative.html –  cc4re Oct 17 '12 at 18:27
    
Can you explain me these steps little bit or suggest me any link to study. –  Sunil Kumar Oct 17 '12 at 18:30
1  
There are some links here that may be of interest: wiki.postgresql.org/wiki/Category:Replication –  kgrittn Oct 17 '12 at 20:30
add comment

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

share|improve this answer
    
Note that a good and well tested STONITH setup is utterly vital for a shared storage configuration. If two postmasters ever use the same storage at the same time, catastrophic and unrecoverable database corruption is likely to occur. –  Craig Ringer Oct 18 '12 at 10:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.