7 Advanced Server Supporting Components : 7.7 pgPool-II
7.7 pgPool-IIUnless otherwise noted, the commands and paths noted in the following section assume that you have performed an installation with the interactive installer.The pgPool-II utility package acts as a middleman between client applications and Server database servers. pgPool-II functionality is transparent to client applications; client applications connect to pgPool-II instead of directly to Advanced Server, and pgPool-II manages the connection. EnterpriseDB supports the following pgPool-II features:pgPool-II runs as a service on Linux systems, and is not supported on Windows systems. pgPool-II functionality is controlled via configuration parameter settings made in the configuration files. If you have used the graphical installer, sample configuration files are located in:After modifying the parameter settings that implement pgPool-II functionality for your installation, you must restart the pgPool-II service. For detailed information about controlling the pgPool-II service, see Section 5Please note that the configuration options for pgPool-II are extensive; the options listed below should be considered a starting point only. For more information about configuring and using pgPool-II, please consult the project website at:When pgPool-II starts, it records its process ID in a file whose name is determined by the pid_file_name configuration parameter. The initial value of the pid_file_name parameter in the sample file is:Please note that the contents of the /var/run directory (including the pgpool directory) may be removed by the operating system during a reboot. The /var/run/pgpool directory should NOT be used as the location for the pgpool.pid file.pgPool-II provides a set of child processes that maintain cached connections to one or more database servers. When a client connects, pgPool-II attempts to reuse a connection from its pool, thus avoiding the overhead of opening and closing client connections.A connection in the pool can be reused only if the target database and the connection user match a prior connection that is currently in the pool. Connection pooling configuration options (such as the number of child processes, and the maximum number of cached connections per child) are specified in the pgpool.conf file.
1. Configure the pg_hba.conf file on the pgPool-II host to permit connections between the clients and the server.
2. Copy the pgpool.conf.sample file to pgpool.conf, and modify the file, setting the connection_cache parameter to on, and specifying connection properties for your database server.Note that in the pgpool.conf file, connection parameters have an appended digit that specifies a cluster node identifier. Database node 0 specifies values for the master node.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/edb/as9.6/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 220.127.116.11):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/edb/as9.6/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:Then, start a psql session, connecting to the edb database: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:Use your platform-specific command to stop the database server before replacing the data directory on the Standby node with the data directory of the Master node. For information about controlling the service, see Section 5.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.After copying the data directory, change ownership of the directory to the database superuser (enterprisedb):Navigate into the data directory:With your choice of editor, create a file named recovery.conf ( in the /opt/edb/as9.6/data directory) that includes:Please note that 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/edb/as9.6/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: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: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.The following table lists pgpool.conf parameters that are used when implementing connection pooling:
Host name or IP address used by pgPool-II to listen for connections. Default is localhost. Change to '*' for all addresses. Host name or IP address for backend 0. You can specify '' if the backend and pgPool-II are running on the same host. Weight for backend 0 (only in load balancing mode). Specify 1 for each backend if you want the load equally balanced, or decimal values (.9, .1, etc.) to weight the load towards certain backends. Set to on to use pool_hba.conf for client authentication. Set to on to enable connection pooling.The following table lists pgpool.conf parameters that are used when implementing replication and load balancing:
Set to on to activate load balancing mode. If load_balance_mode is on そして replicate_select is off, SELECT statements are sent to one backend. The proportion of SELECT statements each backend receives is determined by parameter backend_weight<N> Ignore leading white spaces of each query. Certain APIs such as DBI/DBD::Pg for Perl add white space that the user cannot control. Default is onWhen pgPool-II is enabled, client applications connect to pgPool-II, which acts as a middleman for a Postgres server. A connecting client application is first authenticated with the pgPool-II server, and then authenticated with the Postgres server.pgPool-II authentication properties are determined by parameter settings in the pool_hba.conf configuration file. The pool_hba.conf file is similar in format and function to the Postgres pg_hba.conf configuration file. Please consult the pgPool-II documentation for detailed information about pool_hba.conf entries.
1. Copy file pool_hba.conf.sample to pool_hba.conf.
2. Modify the pool_hba.conf file, specifying authentication information for servers or users that you wish to allow to connect. Entries follow the same format used in the pg_hba.conf file.
3. Modify the pgpool.conf file, setting the enable_pool_hba parameter to on.Note: user names and passwords specified in the pool_hba.conf file will be used for authentication with the database server; you must also specify those user names and passwords in the database server's pg_hba.conf file.Client applications should connect directly to the pgPool-II listener port on the pgPool-II host. For example, to connect to the edb database using pgPool-II functionality, enter:When invoked at the psql prompt, the following SHOW command keywords will display pgPool-II information:
PCP is an administrative interface for pgPool-II that allows you to retrieve information about database nodes, pgPool-II child processes, etc. You should issue PCP commands from the Linux command line.Before using PCP commands, you must modify the pcp.conf file, providing user names and passwords that you supply whenever you issue a PCP command. The user names in the pcp.conf file are completely independent of the database server user names and passwords.
1. Copy the pcp.conf.sample file to pcp.conf.
2. Add an entry to the pcp.conf file of the following form:username is a PCP user name.md5_password is the PCP password in md5 formatYou can use the pg_md5 program to generate the encrypted password from the clear-text form as shown below:For example, the entry in the pcp.conf file for a PCP user named pcpuser with the password of mypassword is:
3. After configuring PCP, you can use PCP commands to control pgPool-II and retrieve information. Specify the following arguments when calling PCP commands:
The PCP user name (as specified in pcp.conf.)
pcp_node_count timeout host port username password Total number of nodes defined in pgpool.conf pcp_node_info timeout host port username password nodeid pcp_proc_count timeout host port username password pcp_proc_info timeout host port username password processid pcp_detach_node [-g] timeout host port username password nodeid Detaches the node specified by <nodeid> from pgPool-II. If -g is given, wait until all clients are disconnected (unless client_idle_limit_in_recovery is -1 or recovery_timeout is expired). pcp_attach_node timeout host port username password nodeid Attaches the node specified by <nodeid> to pgPool-II.
7 Advanced Server Supporting Components : 7.7 pgPool-II