Currently setting up EFM and pgpool on our EDB instance. I'll be setting up a client connection system (which is the same as the EFM witness) and 2 servers in master/slave.
I have EFM working at this time, and am wondering if I should set up pgpool to point to the following:
backend0 is witness
backend1 is master
backend2 is slave
Please advise if this is good practice
Setting up EFM and pgpool on same instances is not best practice on the production server, becuase of its complexity. EDB Postgres Failover Manager (EFM) is a high availability module from EnterpriseDB (EDB) that monitors the health of Postgres clusters and verifies failures quickly. Should one occur, EFM can automatically promote a Standby node to Master to ensure continued performance and protect against data loss.
Where as Pgpool, the open source middleware that sits between the database clients and the Postgres server. The Pgpool module helps in connection pooling and load balancing of SELECT queries on multiple standbys in EDB Postgres clusters.
If you would like to Implement both EFM and pgpool in EDB Instance refer the below blog for your reference.
I've seen the doc you've referenced, and it appears that the pgpool server acts as a witness. I can still do a watchdog instance with efm running the witness on the primary watchdog node, correct? Otherwise, I'm misunderstanding the doc.
Hi, The integration article talks about using a fencing script that will be called by EFM on the promoting node to also change the master node in pgpool using the pgp PCP commands. It also states to disable "allow to failover" in pgpool. My question is that since pgpool has an internal mechanism to detect the master node why would we need to do this? I understand its to prevent a split-brain situation put if pgpool is auto detecting the primary why would this be the case?
Hi Dave - EDB recommends use of EFM for the failover/quorum, rather than pgpool; pgpool, in this design, would be intended for load-balancing rather than the persistence of the cluster itself. By disabling the features in pgpool, it prevents the conflicts you rightly pointed out. If someone chooses to use pgpool to manage both load balancing and HA, then that would be different (and likely exclude EFM from the solution); the added benefit of using EFM for that element means that you can actually use pgpool *or* alternative load-balancing utilities as well, since they would be called by the user's own fencing scripts.
Thanks for the clarification. I just want to be sure as this is important because I have found that the pgpool docs are less than stellar. From what I read, using pcp_promote_node does not actually failover the DB node; instead, it only changes the pgpool's view of the world.
My question is that does allow_failover promote the backend DB or just like pcp_promote_node it only changes the internal pgpool pointers when it detects a new primary node.
Also, I found some errors in the sample script
PCP_USER=enterprisedb # PCP user name
does not work. The comment is added to the varible.
The allow_to_failover is just a flag that tells pgpool that failover is allowed for the node.
Actual failover is performed by the failover script in the pgpool for e.g, below is the parameter in pgpool.conf file
failover_command = '/usr/local/etc/failover.sh %d %P %H %R'
And suppose the failover.sh file is as below :
FALLING_NODE=$1 # %d
OLDPRIMARY_NODE=$2 # %P
NEW_PRIMARY=$3 # %H
PGDATA=$4 # %R
if [ $FALLING_NODE = $OLDPRIMARY_NODE ]; then
if [ $UID -eq 0 ]
su postgres -c "ssh -T postgres@$NEW_PRIMARY touch $PGDATA/trigger"
ssh -T postgres@$NEW_PRIMARY touch $PGDATA/trigger
In the above failover script pgpool performs the failover by touching the trigger file on the Standby server (New Master), after it detects the Master database has failed.
As mentioned by Jamie, if you want to use the combination of pgpool and EFM, it is best to use EFM to do the failover part and pgpool for pooling and load balancing.
From EFM 3.2, you can attach/detach the EFM nodes from the load balancer after the failure using the script.
Please refer the below parameters of the EFM in the documentation :
Please let us know if you have any further queries regarding this issue.
Glad to hear that we have answered to your queries. If you do not have any further queries, could you please mark this issue as solved.
I would like to say thanks for your discussion, but could you please
explain how to integrate pgpool and efm when using pgpool as connection
pooling purposes only.
If you would like to Implement both EFM and pgpool connection pooling refer the below blog for your reference.
We have newer information that includes more recent features in EFM. Hopefully someone from the pgpool team can share the scripts they've worked on that plug into EFM for this.