In this article we are going to see how database servers work together to allow the second server to take over quickly if the primary server fails(high availability), and to allow multiple servers to serve the same data for SELECTs(horizontal read scalability).
Since both functionalities are not available in one product, we need to rely on external tools to provide this functionality and integrate them. EDB Postgres Failover Manager (EFM) is high-availability tool to monitor the health of Postgres Streaming Replication clusters to verify database failures quickly and automatically promote a standby node as primary without any data loss and with minimal service interruption(connection rollback). PgPool-II(pgpool) provides connection pooling and load balancing for horizontal scalability of SELECT queries on multiple standbys.
We need EDB Failover Manager (EFM) and PgPool-II functionalities to achieve high-availability and horizontal read scalability.
IMO, it would have been difficult to integrate EFM & PgPool, if the latest version of EFM 3.2 has not introduced Load Balancer Hooks(Thanks to EFM team). About EFM 3.2, it supports multiple ways to connect applications with the new master database after a failover or promotion. With latest version, two new script hooks introduced to execute before and after a database event of switchover or failover or standby node failure. These EFM new hooks can be used to update the PgPool and other load balancer configuration. All we need, write a simple script with set of commands to manipulate load balancer configuration on the database status and set the script in EFM load balancer hooks(script.load.balancer.attach / script.load.balancer.detach) in cluster properties file. When EFM takes action on any database event, these scripts hooks will be executed automatically and run load balancer configuration commands set in script to update the database node status. By this way, integration is transparent, well automated and with NO manual intervention in case of any database events.
Watch a short video demonstrating the integration with few testing scenarios.
How EFM & PgPool Integration works ?
EFM Load Balancer script hooks(script.load.balancer.attach / script.load.balancer.detach) are called by “EFM” user for every database event (down/up/promotion) before or after the event accordingly. A simple script should be created with PgPool PCP unix commands in it to update the PgPool configuration. When EFM performs Switchover/Failover, a node role will be changed and “pcp_promote_node” command can be used to update PgPool-II cluster for new master similarly for any node down or up “pcp_attach_node” & “pcp_detach_node” command can be used to add/remove node from PgPool Cluster. Remember, since EFM script hooks call PCP unix commands we MUST configure password-less authentication between database and PgPool Node to avoid interruption of Switchover/Failover.
Note: When pgpool pcp_promote_node command exeucted to update a New Master information in an event of Switchover/Failover performed by EFM, the connections which are established to old Master or Standby nodes will be disconnected and reconnected to new master after promotion.
Below are the list of scenarios tested in 1 Master and 2 Standby nodes architecture.
Note: Above scenarios are tested with EFM 3.2/PgPool-II 3.7/EPAS 10.x versions, behavior may vary if implemented with other versions. This article do not encourage to implement the architecture in production without thorough testing.
Operating System of all the servers - CentOS 7.x version(64 Bit)
In this section, we will cover the installation of all components required on each server as per the architecture. We are going to use RPM method of installation for all the components. Refer to the documentation link tagged with each component to complete the installation.
Install PgPool Binaries (on database nodes we need ONLY pgpool binaries to run PCP unix commands. No pgpool configuration or services should be started)
In this section, we are going to cover ONLY very important configuration required for EFM & PgPool integration. Streaming Replication & EFM configuration are not covered in details for the clarity reason of the article.
Note: Above PCP configuration steps should be followed on each database node. All nodes should pass the password-less authentication for a successful PgPool integration.
Configure EFM load balancer scripts hooks on each Database Nodes.
Connect to the database node
Switch as “EFM” user
#su - efm
Create directory as “EFM” user to store load balancer script hooks and it use also used for script logging.
-bash-4.2$ mkdir efm-scripts
-bash-4.2$ cd efm-scripts
Create two scripts with the content shared in this article below or clone from GitHub repository to the “efm-scripts” location.
Edit the script and adjust the EPAS, PGPOOL & PORTS as per your environment.
Start the Services
After installation/configuration, start the services of each components involved in architecture.
Start EPAS service on master & 2 standby nodes (systemctl start edb-as-10)
Start EFM service on master & 2 standby nodes (systemctl start efm-32)
After starting you check the efm cluster status using “efm cluster-status <clustername>”
Start PgPool service on PgPool Node (systemctl start pgpool.service)
Two EFM Load Balancer Scripts:
Two user-friendly scripts "efm_loadbalancer_attach.sh” and “efm_loadbalancer_detach.sh” written in BASH and they are wrapped with PCP unix commands which are executed by EFM Load balancer attach/detach hooks as “efm” user. Refer to GitHub Repository for sample configuration files and scripts. Please feel free to clone & use the scripts, if there's a scope of improvement or correction then don't hestitate to raise issue in the repository.
Note: Including script content in this article may risk of missing the real context of the article by readers, hence those details are moved to GitHub Repository Page.
After completing all the above steps, let's run few command and see the outputs from PgPool Load balancer for an EFM database event.
Consistent State Before Testing:
After configuring Asynchronous Streaming Replication, EDB Failover Manager Cluster & PgPool-II load balancing, below output shows the nodes status in PgPool.
PgPool Nodes Status after Switchover
In EFM, switchover can be performed using “efm promote <cluster name> -quiet -switchover” command. One of the priority standby will take the role of primary when performed switchover and old primary will be attached as standby to new primary. During this EFM operation, load balancer hooks will connect to pgpool cluster via pcp command to manipulate the nodes roles.
Note: Above result after the Switchover from the consistent state.
PgPool Nodes Status after Failover
In EFM, manual failover can be performed using “efm promote <cluster name>” command. One of the priority standby will take the role of primary when performed failover and old primary will be detached. During this EFM operation, load balancer hooks will connect to pgpool cluster via pcp command to manipulate the nodes roles.
Patroni is an open-source tool for managing replication and failover of a PostgreSQL cluster. It is popular to use while implementing high-availability due to its handling of replication and...
EDB provides the tool EDB Replication Server (xDB/EPRS) 7.4 that's a trigger or physical replication-based system with a managed process for data distribution. It can perform multi-master replication, but...