AutoFailover Setup

Automatic Failover

Ok, so the database is now replicated between several machines and the read queries are load balanced among them in a transparent way to the user. It's not highly available yet, though, since a failure of the Master machine will put the database into a read-only state.

I made a script, failover.sh, that will be used to automate the process described on the Manual Failover page. The script is called by PgPool II whenever a node is detached, so you'll have to repeat these steps for both PgPool machines:

1. copy the failover.sh script to /etc/pgpool-II/

2. open the script file and edit the nodeIDs, nodeIPs and nodePorts arrays to match your PostgreSQL machines. Also change the sudo user and postgres user (the postgres user is the one that owns the data folder where recovery.conf is located)

3. open the file /etc/pgpool-II/pgpool.conf,
   find the line failover_command = '' and change it to

   failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %M %m %H %P %r %R >> /tmp/failover.log'

Note that the failover command redirects the logs to /tmp/failover.log

4. enable passwordless ssh (RSA-keys) login from both PgPool servers to all the PostgreSQL machines for both the postgres user and a user with sudo access

To do this open a terminal, change to the respective user (su user) and run:

1. ssh-keygen -t rsa - do not enter any password
2. ssh-copy-id -i 192.168.1.1 - change the IP for the respective machine
3. try logging in to the machine to check if it requires a password: ssh user@192.168.1.1

check /var/log/secure if you have issues

5. run sudo pgpool reload (or start it if it wasn't running)

If you want to change something in the script behaviour, here is a handy table of what each of the arguments passed by pgpool to the script mean:

Bash Variable	Symbol	Description	Example
$1	%d	Backend ID of a detached node	1
$2	%h	Hostname of a detached node	192.168.129.143
$3	%p	Port number of a detached node	5432
$4	%D	Database cluster directory of a detached node	/var/lib/pgsqli/9.4/data
$5	%M	Old master node ID	1
$6	%m	New master node ID	2
$7	%H	Hostname of the new master node	192.168.129.202
$8	%P	Old primary node ID	1
$9	%r	New master port number	5432
$10	%R	New master database cluster directory	/var/lib/pgsqli/9.4/data