Architecture Overview

Since high-availability and read scalability are not part of the core feature set of EDB Postgres Advanced Server, Advanced Server relies on external tools to provide this functionality. This document will focus on functionality provided by EDB Failover Manager and Pgpool-II and discuss the implications of a high-availability architecture formed around these tools. We will demonstrate how to best configure Failover Manager and Pgpool to leverage the benefits they provide for Advanced Server. Using the reference architecture described in the Architecture section, you can learn how to achieve high availability by implementing an automatic failover mechanism (with Failover Manager) while scaling the system for larger workloads and a high number of concurrent clients with read-intensive or mixed workloads to achieve horizontal scaling/read-scalability (with Pgpool).

The architecture described in this document has been developed and tested for EFM 3.9, EDB pgPool 4.0, and Advanced Server 12.

Documentation for Advanced Server and Failover Manager are available from EnterpriseDB at:

https://www.enterprisedb.com/resources/product-documentation

Documentation for pgPool-II can be found at:

http://www.pgpool.net/docs/latest/en/html

Failover Manager Overview

Failover Manager is a high-availability module that monitors the health of a Postgres streaming replication cluster and verifies failures quickly. When a database failure occurs, Failover Manager can automatically promote a streaming replication standby node into a writable master node to ensure continued performance and protect against data loss with minimal service interruption.

Basic EFM Architecture Terminology

A Failover Manager cluster is comprised of EFM processes that reside on the

following hosts on a network:

  • A Master node is the primary database server that is servicing database clients.

  • One or more Standby nodes are streaming replication servers associated with the master node.

  • The Witness node confirms assertions of either the Master or a Standby in a failover scenario. A cluster does not need a dedicated witness node if the cluster contains three or more nodes. If you do not have a third cluster member that is a database host, you can a dedicated Witness node; a cluster may include more than one witness node.

PgPool-II Overview

Pgpool-II (Pgpool) is an open source application that provides connection pooling and load balancing for horizontal scalability of SELECT queries on multiple standbys in EPAS and community Postgres clusters. Pgpool can be configured to use a backend_weight parameter to prevent read traffic to be directed to the master node. In such cases, data modification language (DML) queries (i.e., INSERT, UPDATE, and DELETE) are always sent to the master node, while read queries are load-balanced to the standbys, providing scalability with mixed and read-intensive workloads.

EnterpriseDB supports the following Pgpool functionality:

  • Load balancing

  • Connection pooling

  • High availability

  • Connection limits

PCP Overview

Pgpool provides an interface called PCP for administrators that performs management operations such as retrieving the status of Pgpool or terminating Pgpool processes remotely. PCP commands are UNIX commands that manipulate Pgpool via the network.

Pgpool Watchdog

watchdog is an optional sub process of Pgpool that provides a high availability feature. Features added by watchdog include:

  • Health checking of the pgpool service

  • Mutual monitoring of other watchdog processes

  • Changing active/standby state if certain faults are detected

  • Automatic virtual IP address assigning synchronous to server switching

  • Automatic registration of a server as a standby during recovery

More information about the Pgpool watchdog component can be found at:

http://www.pgpool.net/docs/latest/en/html/tutorial-watchdog.html