We have currently 3 servers(X,Y,Z) running Debian X64, two of them host projects(Y,Z), which don't require that much database, as they serve more static content, but the third server(X) is largely dependent on database and is a critical component for the project.
All the three servers use PostgreSQL. For this reason, I was thinking of creating a clustering setup, where server X would act like a master, and Y,Z as the slaves.
What I am looking for is, create a setup, where the database from server X is the master. But as the Y,Z host projects as well, the changes done to it, need to be synchronized back to server X.
I presume this technology is called replication & clustering? How can I proceed ahead with this problem for PostgreSQL.
If anyone can help me outline a strategy for this, and how can I proceed ahead. Any help would be nice. Thank you. If you feel, that my understanding of replication and/or clustering is wrong, please feel free to correct me.
Best Answer
You probably need 5+ servers for "real" high availability:
2x pgpool-II proxies (they send writes to master and reads to anyone)
3x postgresql servers (master, slave, witness)
Writes to master get sent to a slave via streaming replication
Use something like repmgr to do automatic failover
You're going to want to read this thoroughly: http://www.postgresql.org/docs/current/static/high-availability.html