1

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.

Improve this question
4
  • You're asking for master-master replication actually. Which pg doesn't do natively. Can you not write everything to X and replicate to Y,Z and read from any?
    – Neil McGuigan
    Commented Feb 17, 2016 at 19:50
  • @Neil McGuigan : i would like to write everything to x, but read from individual servers, just when any one goes down, then the master can serve the requests.. Configuration wise X is the fastest..
    – We are Borg
    Commented Feb 17, 2016 at 19:53
  • which version of pg? say 9.4+ :)
    – Neil McGuigan
    Commented Feb 17, 2016 at 19:55
  • @Neil McGuigan : yes, 9.4.
    – We are Borg
    Commented Feb 17, 2016 at 19:56

1 Answer 1

Reset to default
1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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