Configure/Add Postgres Repo
- Download the Centos 6 Repo RPM from HERE
- Install the Repo RPM
Now that you have the repo install you can use YUM to install pgpool-II from the PG repo
PGPool-II stores its configuration files in /etc/pgpool-II-91/. Installing the RPM will create sample configuration files.
Copy the /etc/pgpool-II-91/pgpool.conf.sample to /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
~ 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
~ 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
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.
Copy the /etc/pgpool-II-91/pcp.conf.sample to /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.
PCP commands are executed via network, so the port number must be configured with pcp_port parameter in pgpool.conf file.
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