I work for a small company. We have been using PostgreSQL on Heroku as one of our core technologies. While, in general, we've been happy with Heroku, the company has made the decision that our monthly bill with them is getting prohibitively expensive. As a result, the engineering team has decided to port everything over to DigitalOcean. Since I'm the most experienced database developer on the team, I've been given the task of figuring out "what to do about the databases". To be clear, I've got almost 20 years of writing SQL statements and stored procedures and 4 years with PostgreSQL, but I'm definitely not a DBA (And frankly, I'm scared to death that I'm going to screw things up). I've spent every night for a week reading blogs and watching videos on configuration strategies for "high availability". What I've decided to do is create a master, with 2 hot standbys. For each service/product we have, the architecture is basically the same:
- 1:n webserver(s)
- 1:n jobserver(s)
- 1:n reporting server(s)
Web and job(s) will be pointed to master, and the reporting server(s) will be pointed to 1 of the hot standbys.
note: We are using Anisble for Cloud/IT automation.
So, the idea is that if the master fails, the most up-to-date standby gets promoted to Master. Obviously, that means that the other hot standby must be switched to the new master, and the all the servers (web, job, reporting) must be reconfigured and reconnected.
I've read a lot about how to do this with something like repmgr. But, nothing on how all of the other servers know about this change and are reconfigured. Is there a simple way this is done? With Anisible, we could have a playbook that connects and changes environment vars and restarts the services, but it's hard for me to believe this is the way everyone does it. It feels like there is a piece missing for me. It feels like you'd just need/want some kind of a proxy in the middle and just reconfigure it. Is this where people use something like PgBouncer or PgPool?
Any help will be greatly appreciated.
- PostgreSQL 9.3 (but, could move up to 9.4 if there is a reason)
- Ubuntu 14.04
- Python 2.7