Architecture

A typical EFM and Pgpool configuration

The sample architecture diagram shows four nodes as described in the table below:

Systems	Components
Primary Pgpool/EFM witness node	The primary Pgpool node will only run Pgpool, and EFM witness, as such leaving as much resources available to Pgpool as possible. During normal runmode (no Pgpool Failovers), the Primary Pgpool node has attached the Virtual IP address, and all applications connect through the Virtual IP address to Pgpool. Pgpool will forward all write traffic to the Primary Database node, and will balance all read across all Standby nodes.On the Primary Pgpool node, the EFM witness process ensures that a minimum quota of three EFM agents remains available even if one of the database nodes fails. Some examples are when a node is already unavailable due to maintenance, or failure, and another failure occurs.
Primary Database node	The Primary Database node will only run Postgres (primary)and EFM, leaving all resources to Postgres. Read/Write traffic (i.e., INSERT, UPDATE, DELETE) is forwarded to this node by the Primary Pgpool node.
Standby nodes	The Standby nodes are running Postgres (standby), EFM and an inactive Pgpool process. In case of a primary database failure, EFM will promote Postgres on one of these Standby nodes to handle read-write traffic. In case of a Primary Pgpool failure, the Pgpool watchdog will activate Pgpool on one of the Standby nodes which will attach the VIP, and handle the forwarding of the application connections to the Database nodes. Note that in a double failure situation (both the primary Pgpool node and the Primary Database node are in failure), both of these primary processes might end up on the same node.

This architecture:

• Achieves high availability by providing two standbys that can be promoted in case of a primary Postgres node failure.

• Achieves high availability by providing at least three Pgpool processes in a watchdog configuration.

• Increases performance with mixed and read-intensive workloads by introducing increased read scalability with more than one standby for load balancing.

• Reduces load on the primary database node by redirecting read-only traffic with the primary pgpool node.

• Prevents resource contention between Pgpool and Postgres on the Primary Database node. By not running Pgpool on the Primary database node, the Primary Postgres process can utilize as much resources as possible.

• Prevents resource contention between pgpool and Postgres on the Primary Pgpool node. By not running Standby databases on the Primary Pgpool node, Pgpool can utilize as many resources as possible.

• Optionally, synchronous replication can be set up to achieve near-zero data loss in a failure event.

Note

The architecture also allows us to completely separate 3 virtual machines running Postgres from 3 virtual machines running Pgpool. This kind of setup requires 2 extra virtual machines, but it is a better choice if you want to prevent resource contention between Pgpool and Postgres in Failover scenarios. In this setup, the architecture can run without an extra 7th node running the EFM Witness Process. To increase failure resolution efm witness agents could be deployed on the Pgpool servers.

Deployment of EFM and Pgpool on separate virtual machines