7.7 pgpool-II

Table of Contents Previous Next



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.
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.
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.5AS/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.5AS/data), adding the following replication parameter and values to the end of the file:
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.
With your choice of editor, create a file named recovery.conf( in the /opt/PostgresPlus/9.5AS/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.5AS/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.
The following table lists pgpool.conf parameters that are used when implementing connection pooling:
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 onreplicate_선택 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 on.
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.
When invoked at the psql prompt, the following SHOW command keywords will display pgpool-II information:
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 format
You 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:
pcp_node_count timeout host port username password
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


Table of Contents Previous Next