7 Advanced Server Supporting Components : 7.7 pgpool-II
7.7 pgpool-IIThe pgpool-II utility 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 following files:/opt/PostgresPlus/pgpool-II/etc/pgpool.conf
/opt/PostgresPlus/pgpool-II/etc/pcp.confAfter modifying the parameter settings that implement pgpool-II functionality for your installation, you must restart the ppas-pgpool service.The instructions in the following section assume that you have performed an installation with the interactive installer (unless otherwise noted).The pgpool-II service will not automatically start after an Advanced Server Installation. After specifying configuration information, use the Linux command line to start, stop or restart the ppas-pgpool service, or confirm the service status.The pgpool-II service script is named ppas-pgpool. The service script resides in the /etc/init.d directory. You can control the pgpool-II service, or check the status of the service with the following command:/etc/init.d/ppas-pgpool actionWhere action specifies:
• start to start the service.
• stop to stop the service
• restart to stop and then start the service.
• status to return the status of the service.Please 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.Modify the pid_file_name parameter to specify a safer directory location. For example:The /var/run directory will persist after a system reboot, and if removed by the operating system, the pgpool.pid file will be recreated by pgpool-II upon startup.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 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.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_space 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 (while 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.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: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