cancel
Showing results for 
Search instead for 
Did you mean: 

EFM and pgpool

Level 3 Adventurer

EFM and pgpool

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

12 REPLIES
EDB Team Member

Re: EFM and pgpool

Hi,

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.

1) Failover with EFM.
2) Connection pooling and load balancing with Pgpool.
 
 
Level 3 Adventurer

Re: EFM and pgpool

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.

EDB Team Member

Re: EFM and pgpool

Hi,

 

Yes you can still do a watchdog with efm running the witness.

Level 3 Adventurer

Re: EFM and pgpool

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?

 

thanks

Dave

Community Manager

Re: EFM and pgpool

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!

 

Jamie

Level 3 Adventurer

Re: EFM and pgpool

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

etc...

does not work.  The comment is added to the varible.

 

 

EDB Team Member

Re: EFM and pgpool

Hi Dave,

 

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 :

 

#!/bin/bash -x

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 ]

    then

        su postgres -c "ssh -T postgres@$NEW_PRIMARY touch $PGDATA/trigger"

    else

        ssh -T postgres@$NEW_PRIMARY touch $PGDATA/trigger

    fi

    exit 0;

fi;

exit 0;

 

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 :

 

script.load.balancer.attach

script.load.balancer.detach

 

https://www.enterprisedb.com/docs/en/3.2/edbfm/EDB_Failover_Manager_Guide.1.14.html#

 

Please let us know if you have any further queries regarding this issue.

 

Regards,

Sudhir

Level 3 Adventurer

Re: EFM and pgpool

Wow...this is great!  I have all I need now to be dangerous.

 

Thanks again

Dave

EDB Team Member

Re: EFM and pgpool

Hi Dave,

 

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.

 

Regards,

Sudhir