Infrastructure at your Service

Daniel Westermann

The dbi services PostgreSQL reference architecture (2) – The community approach

In the last post I looked at how dbi services designs a PostgreSQL architecture based on the products of EnterpriseDB. In this post I’ll look at the same questions but using pure community tools. If you do not need vendor support and do trust the community for providing help and fixes this is another way to go. And usually you can trust the community around PostgreSQL very well. The architecture itself is pretty much the same as in the last post. The tools are different.

As in the last post there is a “minimal” configuration:

pg_ref_community_minimal

The picture is almost the same as with the commercial approach. The only little thing that changed is the backup and recovery tool: This time it is Barman instead of BART. As just one tool changes but not the architecture as a whole the same restrictions apply as with the commercial setup:

  • The application probably needs to be reconfigured to connect to the standby database in case the master goes down for any reason
  • There is no integrated monitoring solution which alerts in case of issues. Sure, monitoring can be done by using scripts, check_postgres or other tools but it needs extra effort

Once there is the need for automated failover in case the primary goes down the next stage adds repmgr. But as repmgr (at least as of now) is not working with VIPs we’ll need to add another tool to the landscape: pgpool-II. Combining both connections can be redirected: Writes will go to the master and reads can go to the standby. In case of a failover pgpool will redirect both, reads and writes, to the surviving node.

pg_ref_community_extended

What is missing here, same issue than in the last post, is a monitoring solution over all components. This is where the dbi services DMK comes into the game. DMK brings a set of monitoring scripts and pre-defined thresholds that can be used to monitor all components.

pg_ref_community_full

The integration into monitoring tools such as Nagios, Icinga, Zabbix and many others is straight forward and easy. Its all based on customizable scripts and free for our customers. What else is missing? To add even more protection, flexibility and load balancing more replicas can be added to the landscape (one more in the picture below):

pg_ref_community_maa

Again, this is a simplified overview but it should help in understanding the concepts. Technically the implementation requires a bit more planning and needs a lot of testing especially from the application side (this is true for any HA implementation and not limited to PostgreSQL, of course). But once this up and running you’ll have a reliable, robust and safe PostgreSQL architecture all based on community products.

 

Leave a Reply


five + = 7

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure