Connections and Authentication

5.2.1. Connection Settings

listen_addresses ( string )

Specifies the hostname or IP address, on which Pgpool-II will accept TCP/IP connections. '*' accepts all incoming connections. '' disables TCP/IP connections. Default is 'localhost' . Connections via UNIX domain socket are always accepted.

This parameter can only be set at server start.

port ( integer )

The port number used by Pgpool-II to listen for connections. Default is 9999.

This parameter can only be set at server start.

socket_dir ( string )

The directory where the UNIX domain socket accepting connections for Pgpool-II will be created. Default is /tmp . Be aware that this socket might be deleted by a cron job. We recommend to set this value to /var/run or such directory.

This parameter can only be set at server start.

pcp_listen_addresses ( string )

Specifies the hostname or IP address, on which pcp process will accept TCP/IP connections. * accepts all incoming connections. "" disables TCP/IP connections. Default is * . Connections via UNIX domain socket are always accepted.

This parameter can only be set at server start.

pcp_port ( integer )

The port number used by PCP process to listen for connections. Default is 9898.

This parameter can only be set at server start.

pcp_socket_dir ( string )

The directory where the UNIX domain socket accepting connections for PCP process will be created. Default is /tmp . Be aware that this socket might be deleted by a cron job. We recommend to set this value to /var/run or such directory.

This parameter can only be set at server start.

num_init_children ( integer )

The number of preforked Pgpool-II server processes. Default is 32. num_init_children is also the concurrent connections limit to Pgpool-II from clients. If more than num_init_children clients try to connect to Pgpool-II , they are blocked (not rejected with an error, like PostgreSQL ) until a connection to any Pgpool-II process is closed . Up to listen_backlog_multiplier * num_init_children can be queued.

The queue is inside the kernel called "listen queue". The length of the listen queue is called "backlog". There is an upper limit of the backlog in some systems, and if num_init_children* listen_backlog_multiplier exceeds the number, you need to set the backlog higher. Otherwise, following problems may occur in heavy loaded systems: 1) connecting to Pgpool-II fails 2) connecting to Pgpool-II is getting slow because of retries in the kernel. You can check if the listen queue is actually overflowed by using "netstat -s" command. If you find something like:

535 times the listen queue of a socket overflowed
			

then the listen queue is definitely overflowed. You should increase the backlog in this case (you will be required a super user privilege).

# sysctl net.core.somaxconn
net.core.somaxconn = 128
# sysctl -w net.core.somaxconn = 256
			

You could add following to /etc/sysctl.conf instead.

net.core.somaxconn = 256
			

Number of connections to each PostgreSQL is roughly max_pool*num_init_children.

However, canceling a query creates another connection to the backend; thus, a query cannot be canceled if all the connections are in use. If you want to ensure that queries can be canceled, set this value to twice the expected connections.

In addition, PostgreSQL allows concurrent connections for non superusers up to max_connections - superuser_reserved_connections.

In summary, max_pool, num_init_children, max_connections, superuser_reserved_connections must satisfy the following formula:

max_pool*num_init_children <= (max_connections - superuser_reserved_connections) (no query canceling needed)
max_pool*num_init_children*2 <= (max_connections - superuser_reserved_connections) (query canceling needed)
			

This parameter can only be set at server start.

5.2.2. Authentication Settings

enable_pool_hba ( boolean )

If true , Pgpool-II will use the pool_hba.conf for the client authentication. See Section 6.1 for details on how to configure pool_hba.conf for client authentication. Default is false

This parameter can be changed by reloading the Pgpool-II configurations.

pool_passwd ( string )

Specify the password file name for md5 authentication. Default value is "pool_passwd" . Specifying '' (empty) disables the use of password file. See Section 6.2.2 for more details.

This parameter can only be set at server start.

authentication_timeout ( integer )

Specify the timeout in seconds for Pgpool-II authentication. Specifying 0 disables the time out. Default value is 60

This parameter can be changed by reloading the Pgpool-II configurations.