7.7.1 Configuring Streaming Replication and pgpool-II Load Balancing

Table of Contents Previous Next


7 Advanced Server Supporting Components : 7.7 pgpool-II : 7.7.1 Configuring Streaming Replication and pgpool-II Load Balancing

When load balancing is enabled, pgpool-II distributes some types of SELECT statements to backend servers, allowing multiple database servers and hosts to share the processing load of SELECT statements issued by client applications.
If you use password authentication, the same password must be assigned to an associated user name on each database server. The same user name/password pair is used to connect pgpool-II to each backend connection.
Open an SSH session with the master node of the replication scenario, and modify the pg_hba.conf file (located in the /opt/PostgresPlus/9.4AS/data directory), adding connection information for the replication user (in the example that follows, edbrepuser resides on a standby node with an IP address of 107.178.217.178):
Modify the postgresql.conf file (located in /opt/PostgresPlus/9.4AS/data), adding the following replication parameter and values to the end of the file:
Use the sudo su - command to assume the identity of the enterprisedb database superuser:
With your choice of editor, create a .pgpass file in the home directory of the enterprisedb user. The .pgpass file holds the password of the replication user in plain-text form; if you are using a .pgpass file, you should ensure that only trusted users have access to the .pgpass file:
The server will enforce restrictive permissions on the .pgpass file; use the following command to set the file permissions:
Then, delete the data directory on the Standby node:
After deleting the existing data directory, use the pg_basebackup utility to copy the data directory of the Master node to the Standby:
The call to pg_basebackup specifies the IP address of the Master node and the name of the replication user created on the Master node. For more information about the options available with the pg_basebackup utility, see the PostgreSQL core documentation at:
When prompted by pg_basebackup, provide the password associated with the replication user.
With your choice of editor, create a file named recovery.conf( in the /opt/PostgresPlus/9.4AS/data directory) that includes:
Please note: the primary_conninfo parameter specifies connection information for the replication user on the master node of the replication scenario (in our example, edbrepuser resides on 146.148.46.44).
Change ownership of the recovery.conf file to enterprisedb:
Modify the postgresql.conf file (located in /opt/PostgresPlus/9.4AS/data), specifying the following values at the end of the file:
Use your choice of editor to modify the pgpool conf file. Within the pgpool.conf file, modify the parameter settings to specify that load balancing is enabled:
backend_hostname0 = '146.148.46.44'
backend_hostname1 = '107.178.217.178'
Use the backend_weight parameter to specify how queries will be distributed amongst the nodes. Specify a value of 1 to indicate that you wish (qualified) queries to be equally distributed across the nodes of the replication scenario.

7 Advanced Server Supporting Components : 7.7 pgpool-II : 7.7.1 Configuring Streaming Replication and pgpool-II Load Balancing

Table of Contents Previous Next