Setup PGPool-II

| Comments

Configure/Add Postgres Repo

  1. Download the Centos 6 Repo RPM from HERE
1
wget http://yum.pgrpms.org/9.1/redhat/rhel-6-x86_64/pgdg-redhat91-9.1-5.noarch.rpm
  1. Install the Repo RPM
1
sudo rpm -Uvh pgdg-redhat91-9.1-5.noarch.rpm

Install PGPool-II

Now that you have the repo install you can use YUM to install pgpool-II from the PG repo

1
sudo yum install pgpool-II-91

Configure PGPool-II

PGPool-II stores its configuration files in /etc/pgpool-II-91/. Installing the RPM will create sample configuration files.

Configure pgpool.conf

Copy the /etc/pgpool-II-91/pgpool.conf.sample to /etc/pgpool-II-91/pgpool.conf

1
cp /etc/pgpool-II-91/pgpool.conf.sample /etc/pgpool-II-91/pgpool.conf

~ Connection Settings ~

By default PGPool-II only accepts connections from the localhost using port 9999. If you wish to receive conenctions from other hosts, set listen_addresses to ‘*’.

1
2
3
4
5
From:
listen_addresses = 'localhost'

To:
listen_addresses = '*'

~ Backend Connection Settings ~

This section provides deatils about the nodes that PGPool-II is aware of and how it should interface with them. I’ll be show basic settings here for a 2 node “cluster”.

I’m assuming that PGPool-II is installed on the same host as your Postgresql server. To give a better example I’ll be using the hostname of the first node that has both postgresql and pgpool-II in place of using “localhost”.

Notice below that backend_hostname”X” has an incrementing numerial identifier for each additional node ( Example – node1 = 0, node2 = 1, node2 = 3).

Also notice that the backend_port”X” setting is set to the default postgresql port 5432 for every node.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
backend_hostname0 = 'pg1.domain.net'
                                   # Host name or IP address to connect to for backend 0
backend_port0 = 5432
                                   # Port number for backend 0
backend_weight0 = 1
                                   # Weight for backend 0 (only in load balancing mode)
backend_data_directory0 = '/var/lib/pgsql/9.1/data'
                                   # Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
                                   # Controls various backend behavior
                                   # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER
backend_hostname1 = 'pg2.domain.net'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.1/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

~ REPLICATION MODE ~

In order to use Replication Mode with PGPool-II you’ll need to configure all setting in this section and all section above the REPLICATION MODE section.

For a basic setup you just need to enable replication by setting replication_mode = on. By default this setting is set to off

~ LOAD BALANCING MODE ~

When using Replication Mode with PGPool-II you have the option to enable load balancing by setting the load_balance_mode = on. By deafult this setting is set to off

Configure pcp.conf

PGPool-II has an interface for administration purpose to retrieve information on database nodes, shutdown PGPool-II, etc. via network. To use PCP commands, user authentication is required. This authentication is different from PostgreSQL’s user authentication. A username and password need to be defined in pcp.conf file. In the file, a username and password are listed as a pair on each line, and they are separated by a colon (:). Passwords are encrypted in md5 hash format.

1
postgres:e8a48653851e28c69d0506508fb27fc5

Copy the /etc/pgpool-II-91/pcp.conf.sample to /etc/pgpool-II-91/pcp.conf

1
cp /etc/pgpool-II-91/pcp.conf.sample /etc/pgpool-II-91/pcp.conf

To encrypt your password into md5 hash format, use pg_md5 command, which is installed as a part of pgpool-II executables. pg_md5 takes text as an command line argument, and displays its md5-hashed text.

For example, give “postgres” as the command line argument, at pg_md5 displays md5-hashed text to the standard output.

1
2
[smbambling@pg1 pgpool-II-91]$ /usr/bin/pg_md5 postgres
e8a48653851e28c69d0506508fb27fc5

PCP commands are executed via network, so the port number must be configured with pcp_port parameter in pgpool.conf file.

1
pcp_port 9898

Configure Postgres Client Access

By default Postgres only allows local users/connections. You need to grant access to the PGPool-II server in pg_hba.conf.

Reminder that PGPool-II doesn’t support replication over IPv6

Grant access to the PGPool-II server ( Even if postgres is installed on the same box). In the exapmle below 10.1.22.28/25 is the IP/Netmask fo the PGPool-II server and we are setting the method to trust

1
host    all             all             10.1.22.28/25            trust

Comments