PgPool-II and repmgr Automatic Failover

Question

I have pgpool-II for HA and repmgr for automatic failover. Pgpool-II also can also run failover I am just wondering doing automatic failover using pgpool or repmgr? If pgpool can do the failover do I need to use repmgr? and use shell scripts to the promotion of new master?

Answer 1

I want to do the automatic failover via repmgrd and use pgpool only as entry point for the applications. It almost works: I have set the failover_command in pgpool configuration file to an empty string (''). When the primary database fails pgpool executes the failover_command (which does nothing because failover_command is empty), and then it will repeatedly try to find a new master. When repmgrd has done the failover, then pgpool see the new master and it is fine.

If the master is stopped, repmgrd will do the failover after x seconds (depending on reconnect interval and attemps parameters). You have to make sure that the health check interval specified in pgpool is bigger than that otherwise pgpool will detach the stopped database too early : the parameters are health_chek_max_retries and health_check_retry_delay (there is also health_check_period which is not clear). You must also set failover_on_backend_error to no.

I am not very confident in this set-up though, I still need to test it more intensively.

Answer 2

pgpool-II: Connection Pooling, Load Balancing, Automated failover, Limiting Exceeding Connections

In pgpool virtual IP is their application connect to VIP( a delegated_ip parameter on pgpool. conf file) primary goes down then failover happens standby promote to primary ( automatic failover )then there are no-changes on application end.
failover_command to write a script then configure the failover.sh script location on pgpool.conf file failover_command="" parameter

repmgr: replication and failover

In Repmgr If the primary is stopped, it will do the failover after xx seconds (depending on reconnecting interval connect_timeout parameter on /etc/repmgr.conf