EnterpriseDB supports replication scenarios that use pgpool-II load balancing with PostgreSQL streaming replication or Slony replication. The supported replication methods ensure that database updates made by client applications are applied to multiple backend servers. For detailed information about the benefits of each replication method and detailed configuration instructions, please review project documentation for each utility.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.When configuring pgpool-II load balancing, it is crucial that the initial database environments in all backend servers are identical:
• 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.Within a replication scenario, each backend is uniquely identified by the host name (or IP address) and port number on which the database server instance is listening for connections. You must ensure that the pool_hba.conf and pg_hba.conf files allow a connection between that server and the host on which pgpool-II will be running.The following example demonstrates how to implement pgpool-II load balancing with two servers (the master and replica nodes) in a Streaming Replication scenario. Configuring pgpool-II load balancing for a Slony replication scenario is similar; please consult the Slony documentation for information about configuring Slony replication.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 188.8.131.52):The connection information should specify the address of the standby node of the replication scenario, and your preferred authentication method.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:Save the configuration file, and issue the following command in the OS Terminal window to restart the server:Use the sudo su - command to assume the identity of the enterprisedb database superuser:Then, start a psql session, connecting to the edb database:At the psql command line, create a user with the replication attribute:Open an SSH session with the Standby server, and assume the identity of the database superuser (enterprisedb):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:You must stop the database server before replacing the data directory on the Standby node with the data directory of the Master node. Use the command: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:pg_basebackup --pgdata=/opt/PostgresPlus/9.4AS/data
--format=p --label=standby --host=184.108.40.206
--username=edbrepuser --password --xlog-method=streamThe 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.After copying the data directory, change ownership of the directory to the database superuser (enterprisedb):With your choice of editor, create a file named recovery.conf( in the /opt/PostgresPlus/9.4AS/data directory) that includes:standby_mode = on
trigger_file = /tmp/trigger_file
primary_conninfo = 'host=220.127.116.11 port=5444
user=edbrepuser password=password'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 18.104.22.168).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:The data file has been copied from the Master node, and will contain the replication parameters specified previously.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:Then, specify the connections settings for the master database node in the parameter set that ends with a 0. For example:backend_hostname0 = '22.214.171.124'Then, specify the connections settings for each node to which queries will be distributed. Increment the number that follows the parameter name for each node, and provide connection details:backend_hostname1 = '126.96.36.199'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.