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

Setup

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

After that we can install them

Install packages

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

Configuration

Now we start with the interesting part :)

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

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:

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:

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

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).

Finally, let's start it:

Start the PGPool

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.

Check the configuration

We installed PGPool on top of existing replication, but PGPool can be used to restore and recover and thus create replication configuration

For that, we have to create couple scripts on the master:

Create Recovery Scripts