Overview

PGPool-II is open source package which provides a lot of features for PostgreSQL like:

Load balancing

Session management

Automatic failover

Others

In a nutshell, the PGPool is external program which connects to the master and the slave(s) and is able to detect when the master is down, which query needs only read and which query needs also write. Thus directing some sessions to the slaves and others to the master.

Below you can see basic architecture:

From the architecture it is visible that the PGPool-II assigns a Virtual IP (VIP) which the client (application) will user. In case the master server fails, that VIP will be migrated to the other server, thus “failing over” and “failover command” will be needed.

So, what are we waiting for. Let's use our configuration which we already have.

Master

postgresqlmaster - 192.168.0.178

Slaves

postgresqlslaveone - 192.168.0.199

postgresqlslavetwo - 192.168.0.200

This is the configuration which we've done with you in the previous sections. Now I will setup a new VM for the pgpool-II

pgPool

postgresqlpgpool VIP - 192.168.0.220

Configure

PGpool has a current stable version of 4.1. So let's use that one.

We have Linux 7 so we can download the rpm from here Once we download it, we have download one more package

Download Packages

Let's tart by downloading the packages on the VM (PostgresqlPGPool) We need 2 additional packages except the PGPool:

libevent

libmemcached

Download required Libraries

After that we can install them

Install packages

Install

After that we could finally install the PGPool as you can see above.

Setup

Now we start with the interesting part :)

Firstly,we have to do passwordless connection between all servers for root and postgresql users:

Configure passwordless Connection

Allow passwordless connection for: root and postgres users

Because of the security reasons, we create a user repl solely used for replication purpose, and a user pgpool for streaming replication delay check and health check of Pgpool-II.

Create user on the Master

Then we have to configure the settings, which will be common for all servers:

Configure Common settings

Common settings in pgpool.conf

These values will be valid for all servers and now we have to specify the specific settings per host. These configurations include: Watch dog, healtcheck and others:

Configure Individual settings

PostgreSQLMaster settings

PostgreSQLSlaveOne settings

PostgreSQLSLaveTwo settings

All these settings are specified in the pgpool.conf file in /etc/pgpool-II After that is done, let's create the log destination:

Create log destination

PCP Config

Since user authentication is required to use the PCP command, specify user name and md5 encrypted password in pcp.conf. Here we create the encrypted password for pgpool user, and add “username:encrypted password” in /etc/pgpool-II/pcp.conf.

PCP password

Since follow_master_command script has to execute PCP command without entering the password, we create .pcppass in the home directory of Pgpool-II startup user (root user).

Allow access

In PGpool 4.0+ the only available password which can be used is either: AES encrypter or clear text. it is obvious to note that it isn't advised to use clear text password.

So we have to:

Modify: pg_hba.conf and pool_hba.conf

Encrypt our password

Store the decryption key in the process owner home on all servers

To modify the pg_hba.conf and pool_hba.conf, you can use any text editor you want, but you should follow the syntax:

Edit hba conf files

Once this is done, we have to provide encryption and decryption key for the password. As you know, AES is symmetric type of encryption which means it utilise the same key for encryption and decryption.

Create encryption/decryption key

Encrypt the password

Please be sure you have done that all servers and you have the: .pgpoolkey on all servers with PGpool on the home directory of the user, which runs the service. (In our case: postgres). Otherwise you will have the following error:

No .pgpoolkey available

P.S. Be sure to restart the PGPool after :) or use pg_ctl to reload the hba files.

Setup Replication

We installed PGPool on top of existing replication, but PGPool can be used to restore and recover and thus create replication configuration itself. Let's follow, the process. Firstly, please create both files in the appendix on the following locations on postgresqlmaster: /var/lib/pgsql/10/data/recovery_1st_stage /var/lib/pgsql/10/data/pgpool_remote_start

Start the Pgpool service

Start PGPool

Restore the database using the VIP:

Start PGPool

Failover

I was going to kill myself couple times, seriously. I was head banging for at least couple weeks because I Couldn't make PostgreSQL failover. So let me tell you couple things which I found out THE VERY HARD and PAINFUL way.

With PgPool, you can either use postgresql replication to migrate you the data, OR Pgpool replication. By default, when you install pgpool, it will be the FIRST thing you install, HOWEVER in our case was the second, so there is a need of a little modification.

Please ENSURE the following parameters are set on the notes:

master_slave_mode = on master_slave_sub_mode = 'stream'

This indicates that you ALREADY have streaming replication and that you take care of it.

Current State

After that, let's see our current state of the cluster:

[root@postgresqlslaveone tmp]# psql -h 192.168.0.220 -p 9999 -U postgres postgres -c "show pool_nodes" Password for user postgres: node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change ---------+--------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+--------------------- 0 | postgresqlmaster | 5432 | up | 0.500000 | primary | 0 | true | 0 | | | 2020-02-07 10:12:26 1 | postgresqlslaveone | 5432 | up | 0.500000 | standby | 0 | false | 0 | | | 2020-02-07 10:12:26 (2 rows) [root@postgresqlslaveone tmp]

That clearly states that the postgresqlmaster is the master and postgresqlslaveone is the slave :) I know, stupid naming but bare with me :)

Database Failover

So what happens after I shutdown the first database:

[root@postgresqlmaster pgpool-II]# su - postgres Last login: Fri Feb 7 10:01:53 EST 2020 on pts/3 -bash-4.2$ /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data/ -l logfile stop waiting for server to shut down..... done server stopped -bash-4.2$

Well…what can happen, the database will go down of course :)

2020-02-07 10:14:36.882 EST [15508] LOG: received fast shutdown request 2020-02-07 10:14:36.907 EST [15508] LOG: aborting any active transactions 2020-02-07 10:14:36.909 EST [15508] LOG: worker process: logical replication launcher (PID 15517) exited with exit code 1 2020-02-07 10:14:36.909 EST [15511] LOG: shutting down 2020-02-07 10:14:38.176 EST [15508] LOG: database system is shut down

And even more shokingly the PGPool will FINALLY recognize it:

10:15:09 postgresqlmaster pgpool[16840]: [10-1] 2020-02-07 10:15:09: pid 16840: LOG: failed to connect to PostgreSQL server on "postgresqlmaster:5432", getsockopt() detected error "Connection refused" Feb 7 10:15:09 postgresqlmaster pgpool[16840]: [11-1] 2020-02-07 10:15:09: pid 16840: LOG: received degenerate backend request for node_id: 0 from pid [16840] Feb 7 10:15:09 postgresqlmaster pgpool[16802]: [26-1] 2020-02-07 10:15:09: pid 16802: LOG: new IPC connection received Feb 7 10:15:09 postgresqlmaster pgpool[16802]: [27-1] 2020-02-07 10:15:09: pid 16802: LOG: watchdog received the failover command from local pgpool-II on IPC interface Feb 7 10:15:09 postgresqlmaster pgpool[16802]: [28-1] 2020-02-07 10:15:09: pid 16802: LOG: watchdog is processing the failover command [DEGENERATE_BACKEND_REQUEST] received from local pgpool-II on IPC interface Feb 7 10:15:09 postgresqlmaster pgpool[16802]: [29-1] 2020-02-07 10:15:09: pid 16802: LOG: we do not need quorum to hold to proceed with failover Feb 7 10:15:09 postgresqlmaster pgpool[16802]: [29-2] 2020-02-07 10:15:09: pid 16802: DETAIL: proceeding with the failover Feb 7 10:15:09 postgresqlmaster pgpool[16802]: [29-3] 2020-02-07 10:15:09: pid 16802: HINT: failover_when_quorum_exists is set to false Feb 7 10:15:09 postgresqlmaster pgpool[16840]: [12-1] 2020-02-07 10:15:09: pid 16840: FATAL: failed to create a backend connection Feb 7 10:15:09 postgresqlmaster pgpool[16840]: [12-2] 2020-02-07 10:15:09: pid 16840: DETAIL: executing failover on backend Feb 7 10:15:09 postgresqlmaster pgpool[16800]: [15-1] 2020-02-07 10:15:09: pid 16800: LOG: Pgpool-II parent process has received failover request Feb 7 10:15:09 postgresqlmaster pgpool[16802]: [30-1] 2020-02-07 10:15:09: pid 16802: LOG: new IPC connection received Feb 7 10:15:09 postgresqlmaster pgpool[16802]: [31-1] 2020-02-07 10:15:09: pid 16802: LOG: received the failover indication from Pgpool-II on IPC interface Feb 7 10:15:09 postgresqlmaster pgpool[16802]: [32-1] 2020-02-07 10:15:09: pid 16802: LOG: watchdog is informed of failover start by the main process Feb 7 10:15:09 postgresqlmaster pgpool[16800]: [16-1] 2020-02-07 10:15:09: pid 16800: LOG: starting degeneration. shutdown host postgresqlmaster(5432) Feb 7 10:15:09 postgresqlmaster pgpool[16800]: [17-1] 2020-02-07 10:15:09: pid 16800: LOG: Restart all children Feb 7 10:15:09 postgresqlmaster pgpool[16800]: [18-1] 2020-02-07 10:15:09: pid 16800: LOG: execute command: /etc/pgpool-II/failover.sh 0 postgresqlmaster 5432 /var/lib/pgsql/10/data 1 postgresqlslaveone 0 0 5432 /var/lib/pgsql/10/data postgresqlmaster 5432 Feb 7 10:15:11 postgresqlmaster postgres[17112]: follow_master.sh: start: Standby node 0 Feb 7 10:15:11 postgresqlmaster postgres[17113]: + FAILED_NODE_ID=0 Feb 7 10:15:11 postgresqlmaster postgres[17113]: + FAILED_NODE_HOST=postgresqlmaster Feb 7 10:15:11 postgresqlmaster postgres[17113]: + FAILED_NODE_PORT=5432 Feb 7 10:15:11 postgresqlmaster postgres[17113]: + FAILED_NODE_PGDATA=/var/lib/pgsql/10/data Feb 7 10:15:11 postgresqlmaster postgres[17113]: + NEW_MASTER_NODE_ID=1 Feb 7 10:15:11 postgresqlmaster postgres[17113]: + OLD_MASTER_NODE_ID=0 Feb 7 10:15:11 postgresqlmaster postgres[17113]: + NEW_MASTER_NODE_HOST=postgresqlslaveone Feb 7 10:15:11 postgresqlmaster postgres[17113]: + OLD_PRIMARY_NODE_ID=0 Feb 7 10:15:11 postgresqlmaster postgres[17113]: + NEW_MASTER_NODE_PORT=5432 Feb 7 10:15:11 postgresqlmaster postgres[17113]: + NEW_MASTER_NODE_PGDATA=/var/lib/pgsql/10/data Feb 7 10:15:11 postgresqlmaster postgres[17113]: + PGHOME=/usr/pgsql-10 Feb 7 10:15:11 postgresqlmaster postgres[17113]: + ARCHIVEDIR=/walshared Feb 7 10:15:11 postgresqlmaster postgres[17113]: + REPLUSER=repl Feb 7 10:15:11 postgresqlmaster postgres[17113]: + PCP_USER=postgres Feb 7 10:15:11 postgresqlmaster postgres[17113]: + PGPOOL_PATH=/usr/bin Feb 7 10:15:11 postgresqlmaster postgres[17113]: + PCP_PORT=9898 Feb 7 10:15:11 postgresqlmaster postgres[17113]: + logger -i -p local1.info follow_master.sh: start: Standby node 0 Feb 7 10:15:11 postgresqlmaster postgres[17113]: + ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@postgresqlslaveone -i /var/lib/pgsql/.ssh/id_rsa ls /tmp Feb 7 10:15:11 postgresqlmaster postgres[17113]: Warning: Permanently added 'postgresqlslaveone,192.168.0.199' (ECDSA) to the list of known hosts. Feb 7 10:15:11 postgresqlmaster postgres[17113]: + '[' 0 -ne 0 ']' Feb 7 10:15:11 postgresqlmaster postgres[17113]: ++ /usr/pgsql-10/bin/initdb -V Feb 7 10:15:11 postgresqlmaster postgres[17113]: ++ awk '{print $3}' Feb 7 10:15:11 postgresqlmaster postgres[17113]: ++ sed 's/\..*//' Feb 7 10:15:11 postgresqlmaster postgres[17113]: ++ sed 's/\([0-9]*\)[a-zA-Z].*/\1/' Feb 7 10:15:11 postgresqlmaster postgres[17113]: + PGVERSION=10 Feb 7 10:15:11 postgresqlmaster postgres[17113]: + '[' 10 -ge 12 ']' Feb 7 10:15:11 postgresqlmaster postgres[17113]: + RECOVERYCONF=/var/lib/pgsql/10/data/recovery.conf Feb 7 10:15:11 postgresqlmaster postgres[17113]: + ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@postgresqlmaster -i /var/lib/pgsql/.ssh/id_rsa /usr/pgsql-10/bin/pg_ctl -w -D /var/lib/pgsq l/10/data status Feb 7 10:15:11 postgresqlmaster postgres[17113]: Warning: Permanently added 'postgresqlmaster,192.168.0.178' (ECDSA) to the list of known hosts.

FINALLY, the failover script was executed after countless blood stains on the wall

You can see also that on the new master node's database:

2020-01-28 14:40:19.701 EST [22926] LOG: received promote request 2020-01-28 14:40:19.728 EST [22926] LOG: redo done at 0/1C000028 cp: cannot stat ‘/walshared/00000001000000000000001C’: No such file or directory cp: cannot stat ‘/walshared/00000002.history’: No such file or directory 2020-01-28 14:40:19.767 EST [22926] LOG: selected new timeline ID: 2 2020-01-28 14:40:19.876 EST [22926] LOG: archive recovery complete cp: cannot stat ‘/walshared/00000001.history’: No such file or directory 2020-01-28 14:40:20.012 EST [22924] LOG: database system is ready to accept connections

PGPool Failover

Now, on the slave (new master) you won't see anything on the Pgpool until you don't shutdown the pgpool too. Because usually when a master fails over, the entire server is dead :)

[root@postgresqlmaster pgpool-II]# service pgpool stop Redirecting to /bin/systemctl stop pgpool.service [root@postgresqlmaster pgpool-II]#

Once you do, you will recieve the migrate of the VIP as well :)

Jan 28 15:33:47 postgresqlslaveone pgpool[25956]: [50-1] 2020-01-28 15:33:47: pid 25956: LOG: watchdog node state changed from [STANDBY] to [JOINING] Jan 28 15:33:51 postgresqlslaveone pgpool[25956]: [51-1] 2020-01-28 15:33:51: pid 25956: LOG: watchdog node state changed from [JOINING] to [INITIALIZING] Jan 28 15:33:52 postgresqlslaveone pgpool[25956]: [52-1] 2020-01-28 15:33:52: pid 25956: LOG: I am the only alive node in the watchdog cluster Jan 28 15:33:52 postgresqlslaveone pgpool[25956]: [52-2] 2020-01-28 15:33:52: pid 25956: HINT: skipping stand for coordinator state Jan 28 15:33:52 postgresqlslaveone pgpool[25956]: [53-1] 2020-01-28 15:33:52: pid 25956: LOG: watchdog node state changed from [INITIALIZING] to [MASTER] Jan 28 15:33:52 postgresqlslaveone pgpool[25956]: [54-1] 2020-01-28 15:33:52: pid 25956: LOG: I am announcing my self as master/coordinator watchdog node Jan 28 15:33:56 postgresqlslaveone pgpool[25956]: [55-1] 2020-01-28 15:33:56: pid 25956: LOG: I am the cluster leader node Jan 28 15:33:56 postgresqlslaveone pgpool[25956]: [55-2] 2020-01-28 15:33:56: pid 25956: DETAIL: our declare coordinator message is accepted by all nodes Jan 28 15:33:56 postgresqlslaveone pgpool[25956]: [56-1] 2020-01-28 15:33:56: pid 25956: LOG: setting the local node "postgresqlslaveone:9999 Linux postgresqlslaveone" as watchdog cluster master Jan 28 15:33:56 postgresqlslaveone pgpool[25956]: [57-1] 2020-01-28 15:33:56: pid 25956: LOG: I am the cluster leader node. Starting escalation process Jan 28 15:33:56 postgresqlslaveone pgpool[25956]: [58-1] 2020-01-28 15:33:56: pid 25956: LOG: escalation process started with PID:29053 Jan 28 15:33:56 postgresqlslaveone pgpool[25956]: [59-1] 2020-01-28 15:33:56: pid 25956: LOG: new IPC connection received Jan 28 15:33:56 postgresqlslaveone pgpool[29053]: [58-1] 2020-01-28 15:33:56: pid 29053: LOG: watchdog: escalation started Jan 28 15:33:59 postgresqlslaveone pgpool[26021]: [24-1] 2020-01-28 15:33:59: pid 26021: LOG: forked new pcp worker, pid=29066 socket=8 Jan 28 15:33:59 postgresqlslaveone pgpool[25956]: [60-1] 2020-01-28 15:33:59: pid 25956: LOG: new IPC connection received Jan 28 15:33:59 postgresqlslaveone pgpool[26021]: [25-1] 2020-01-28 15:33:59: pid 26021: LOG: PCP process with pid: 29066 exit with SUCCESS. Jan 28 15:33:59 postgresqlslaveone pgpool[26021]: [26-1] 2020-01-28 15:33:59: pid 26021: LOG: PCP process with pid: 29066 exits with status 0 Jan 28 15:34:00 postgresqlslaveone pgpool[29053]: [59-1] 2020-01-28 15:34:00: pid 29053: LOG: successfully acquired the delegate IP:"192.168.0.220" Jan 28 15:34:00 postgresqlslaveone pgpool[29053]: [59-2] 2020-01-28 15:34:00: pid 29053: DETAIL: 'if_up_cmd' returned with success Jan 28 15:34:00 postgresqlslaveone pgpool[25956]: [61-1] 2020-01-28 15:34:00: pid 25956: LOG: watchdog escalation process with pid: 29053 exit with SUCCESS. Jan 28 15:34:16 postgresqlslaveone pgpool[26293]: [31-1] 2020-01-28 15:34:16: pid 26293: LOG: pool_reuse_block: blockid: 0 Jan 28 15:34:16 postgresqlslaveone pgpool[26293]: [31-2] 2020-01-28 15:34:16: pid 26293: CONTEXT: while searching system catalog, When relcache is missed Jan 28 15:34:19 postgresqlslaveone pgpool[25985]: [9-1] 2020-01-28 15:34:19: pid 25985: LOG: informing the node status change to watchdog Jan 28 15:34:19 postgresqlslaveone pgpool[25985]: [9-2] 2020-01-28 15:34:19: pid 25985: DETAIL: node id :1 status = "NODE DEAD" message:"No heartbeat signal from node" Jan 28 15:34:19 postgresqlslaveone pgpool[25956]: [62-1] 2020-01-28 15:34:19: pid 25956: LOG: new IPC connection received Jan 28 15:34:19 postgresqlslaveone pgpool[25956]: [63-1] 2020-01-28 15:34:19: pid 25956: LOG: received node status change ipc message Jan 28 15:34:19 postgresqlslaveone pgpool[25956]: [63-2] 2020-01-28 15:34:19: pid 25956: DETAIL: No heartbeat signal from node Jan 28 15:34:19 postgresqlslaveone pgpool[25956]: [64-1] 2020-01-28 15:34:19: pid 25956: LOG: remote node "postgresqlmaster:9999 Linux postgresqlmaster" is shutting down

After failover

After all this is done, we can check the new status of the cluster :)

[root@postgresqlslaveone tmp]# pcp_watchdog_info -p 9898 -h 192.168.0.220 -U postgres Password: 2 YES postgresqlslaveone:9999 Linux postgresqlslaveone postgresqlslaveone postgresqlslaveone:9999 Linux postgresqlslaveone postgresqlslaveone 9999 9000 4 MASTER postgresqlmaster:9999 Linux postgresqlmaster postgresqlmaster 9999 9000 10 SHUTDOWN [root@postgresqlslaveone tmp]# psql -h 192.168.0.220 -p 9999 -U postgres postgres -c "show pool_nodes" Password for user postgres: node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change ---------+--------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+--------------------- 0 | postgresqlmaster | 5432 | down | 0.500000 | standby | 0 | false | 0 | | | 2020-01-28 14:40:20 1 | postgresqlslaveone | 5432 | up | 0.500000 | primary | 0 | true | 0 | | | 2020-01-28 15:34:16 (2 rows) [root@postgresqlslaveone tmp]#

Verify

After executing pcp_recovery_node command, vertify that postgresqlslaveone and postgresqlslavetwo are started as PostgreSQL standby server.

Verify PGPool

You can also verify the Watchdog deamon as follows:

Verify WatchDog

Appendix

recovery_1st_stage

pgpool_remote_start

/etc/pgpool-II/failover.sh

/etc/pgpool-II/follow_master.sh