The objective of this article is to show how to configure and use PCP commands.
The PCP commands are provided by a package called pgpool-II-pcp. You can find this package in either the PGDG repositories or EDB repositories. This package should be installed on the machine that will run the PCP commands against the PgPool node.
The PgPool installation from PGDG repositories does not automatically install the PCP commands package, so you need to install it separately. However, the PgPool installation from the EDB repositories includes the PCP commands.
dnf install pgpool-II-pcp
In the PgPool configuration file, you must define which IP PgPool is allowed to listen for PCP commands, also define a port for that.
The example below allows PgPool to listen to any IP with the option * and in the port 9898:
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/tmp'
To run PCP commands, a pcp.conf file must be located in the PgPool configuration directory (/etc/pgpool-II for community PgPool and /etc/sysconfig/edb/pgpool4.5/ for EDB PgPool). This file should contain a PCP user and its MD5-encrypted password, and it must be owned by the postgres or enterprisedb user. A sample file is available in the configuration directory. Additionally, you can configure more than one PCP user in the pcp.conf file.
Example for EDB PgPool:
cp /etc/sysconfig/edb/pgpool4.5/pcp.conf.sample /etc/sysconfig/edb/pgpool4.5/pcp.conf
chown enterprisedb:enterprisedb /etc/sysconfig/edb/pgpool4.5/pcp.conf
The PCP user is not an OS or PostgreSQL user; it exists only in the pcp.conf file. You can choose any username you like, but the password must be configured in pcp.conf using MD5 encryption. In this example, the username will be pcpuser, and the password is 12345678.
To encrypt the password with MD5, use the pg_md5 binary provided by the PgPool installation (found in the bin directory within the PgPool installation directory /usr/edb/pgpool4.5/bin/ or in /usr/bin):
Example:
pg_md5 12345678
25d55ad283aa400af464c76d713c07ad
Then, add the PCP user and encrypted password to the pcp.conf file as shown below:
pcpuser:25d55ad283aa400af464c76d713c07ad
The .pcppass file enables authentication for PCP commands without requiring a password when the -w option is used in the PCP command line. This file should be located in the home directory of the user running the PCP commands on the machine intended to execute these commands against the PgPool host (for example, it would need to be created on the Postgres host). It does not need to be on the same machine as PgPool unless they share the same host. Additionally, the file must have 0600 permissions.
Example:
su - enterprisedb
echo "*:9898:pcpuser:12345678" > ~/.pcppass
chmod 0600 ~/.pcppass
These are the environment variables that you can set for the application, for example:
-
PCP_USER=<pcp_user_name>; -
PCP_PORT=<pcp_port>; -
PCP_HOST=<pgpool_host_or_ip>; -
PGPOOL_PORT=<pgpool_port>; -
PCPPASSFILE=<path_to_.pcppass>; -
PGPOOL_PATH=<path_to_pcpcommands_directory>.
Below is an example of how to use each PCP command with the -w option, which allows you to avoid providing a password by retrieving it from the .pcppass file instead. These examples exclude the pcp_recovery_node and pcp_promote_node commands, as these are more complex commands that work in conjunction with scripts. Additionally, the pcp_watchdog_info command is covered in a separate article on Watchdog.
The examples below are executed against a PgPool host configured with three PostgreSQL backends.
-
PCP_NODE_COUNT: Count the number of Postgres backends configured in PgPool:
# COMMAND:
pcp_node_count -h <pgpool_host> -p <pcp_port> -U pcpuser -w -v
# OUTPUT:
Node Count
____________
3
-
PCP_NODE_INFO: Display backend information. Use the-voption for a more detailed output. You can also define the node ID after the-woption to display information only for a specific node:
# COMMAND:
pcp_node_info -h <pgpool_host> -p <pcp_port> -U pcpuser -w -v
# OUTPUT WITHOUT `-v` OPTION:
192.168.1.100 5444 2 0.333333 up unknown standby unknown 0 none none 2024-11-17 12:40:22
192.168.1.101 5444 2 0.333333 up unknown primary unknown 0 none none 2024-11-17 12:40:22
192.168.1.102 5444 2 0.333333 up unknown standby unknown 0 none none 2024-11-17 12:40:22
# OUTPUT WITH `-v` OPTION:
Hostname : 192.168.1.100
Port : 5444
Status : 1
Weight : 0.333333
Status Name : waiting
Backend Status Name : unknown
Role : standby
Backend Role : unknown
Replication Delay : 0
Replication State : none
Replication Sync State : none
Last Status Change : 2024-11-17 11:35:59
Hostname : 192.168.1.101
Port : 5444
Status : 1
Weight : 0.333333
Status Name : waiting
Backend Status Name : unknown
Role : primary
Backend Role : unknown
Replication Delay : 0
Replication State : none
Replication Sync State : none
Last Status Change : 2024-11-17 11:35:59
Hostname : 192.168.1.102
Port : 5444
Status : 1
Weight : 0.333333
Status Name : waiting
Backend Status Name : unknown
Role : standby
Backend Role : unknown
Replication Delay : 0
Replication State : none
Replication Sync State : none
Last Status Change : 2024-11-17 11:35:59
-
PCP_HEALTH_CHECK: Health check of a node. This command provides information only for a specific node; it does not allow checking all nodes simultaneously. Provide the node ID at the end of the command line, and you can use the-voption for detailed output:
# COMMAND:
pcp_health_check_stats -h <pgpool_host> -p <pcp_port> -U pcpuser -w -v 0
# OUTPUT WITHOUT `-v` OPTION:
0 192.168.1.100 5444 up standby 2024-11-17 12:40:22 0 0 0 0 0 0.000000 0 0 0 0.000000
# OUTPUT WITH `-v` OPTION:
Node Id : 0
Host Name : 192.168.1.100
Port : 5444
Status : waiting
Role : standby
Last Status Change : 2024-11-17 11:35:59
Total Count : 0
Success Count : 0
Fail Count : 0
Skip Count : 0
Retry Count : 0
Average Retry Count : 0.000000
Max Retry Count : 0
Max Health Check Duration : 0
Minimum Health Check Duration : 0
Average Health Check Duration : 0.000000
Last Health Check :
Last Successful Health Check :
Last Skip Health Check :
Last Failed Health Check :
-
PCP_PROC_COUNT: List the PID of each PgPool child process. The number of displayed processes corresponds to thenum_init_childrenparameter configured inpgpool.conf. In this example, thenum_init_childrenis set to 4, and you can use the-voption for detailed output:
# COMMAND:
pcp_proc_count -h <pgpool_host> -p <pcp_port> -U pcpuser -w -v
# OUTPUT WITHOUT `-v` OPTION:
2070 2071 2072 2073
# OUTPUT WITH `-v` OPTION:
No | PID
_____________________
0 | 2070
1 | 2071
2 | 2072
3 | 2073
-
PCP_PROC_INFO: Check information on a specific PgPool child process. If no client is connected via the specified child process, the messageNo process information availablewill be displayed, use the commandpcp_proc_countto verify the PID of each child process. Use the-voption for detailed output:
# COMMAND:
pcp_proc_info -h <pgpool_host> -p <pcp_port> -U pcpuser -w <child_process_pid> -v
# OUTPUT IF THERE ARE CONNECTIONS IN THE PROCESS WITHOUT `-v` option:
postgres postgres 2024-09-02 13:00:06 0 3 0 2024-09-02 13:50:50 2024-09-02 13:50:50 0 1 60708 1 59105 0 Idle 0
postgres postgres 2024-09-02 13:00:06 0 3 0 2024-09-02 13:50:50 2024-09-02 13:50:50 0 1 56703 1 59105 1 Idle 1
postgres postgres 2024-09-02 13:00:06 0 3 0 2024-09-02 13:50:50 2024-09-02 13:50:50 0 1 56677 1 59105 2 Idle 0
# OUTPUT IF THERE ARE CONNECTIONS IN THE PROCESS WITH `-v` option:
Database : postgres
Username : enterprisedb
Start time : 2024-11-17 11:36:00
Client connection count : 0
Major : 3
Minor : 0
Backend connection time : 2024-11-17 12:40:22
Client connection time : 2024-11-17 12:40:22
Client idle duration : 0
Client disconnection time :
Pool Counter : 1
Backend PID : 8677
Connected : 1
PID : 2071
Backend ID : 0
Status : Idle
Load balance node : 0
Database : postgres
Username : enterprisedb
Start time : 2024-11-17 11:36:00
Client connection count : 0
Major : 3
Minor : 0
Backend connection time : 2024-11-17 12:40:22
Client connection time : 2024-11-17 12:40:22
Client idle duration : 0
Client disconnection time :
Pool Counter : 1
Backend PID : 9522
Connected : 1
PID : 2071
Backend ID : 1
Status : Idle
Load balance node : 1
Database : postgres
Username : enterprisedb
Start time : 2024-11-17 11:36:00
Client connection count : 0
Major : 3
Minor : 0
Backend connection time : 2024-11-17 12:40:22
Client connection time : 2024-11-17 12:40:22
Client idle duration : 0
Client disconnection time :
Pool Counter : 1
Backend PID : 8731
Connected : 1
PID : 2071
Backend ID : 2
Status : Idle
Load balance node : 0
-
PCP_POOL_STATUS: Display status information of PgPool, similar to theSHOW POOL_STATUS;command. As the output is extensive, the example below shows only a few lines for illustration. Adding the-voption simply provides an enumeration for each parameter:
# COMMAND:
pcp_pool_status -h <pgpool_host> -p <pcp_port> -U pcpuser -w -v
# OUTPUT:
Name [ 0]: backend_clustering_mode
Value: 1
Description: clustering mode
Name [ 1]: listen_addresses
Value: *
Description: host name(s) or IP address(es) to listen on
Name [ 2]: port
Value: 9999
Description: pgpool accepting port number
Name [ 3]: unix_socket_directories
Value: /tmp
Description: pgpool socket directories
Name [ 4]: unix_socket_group
Value:
Description: owning user of the unix sockets
Name [ 5]: unix_socket_permissions
Value: 0777
Description: access permissions of the unix sockets.
Name [ 6]: pcp_listen_addresses
Value: *
Description: host name(s) or IP address(es) for pcp process to listen on
-
PCP_DETACH_NODE: Detach a node from PgPool. The status column for the specified node will showdown, indicating the node is detached. PgPool automatically detaches a node if it fails or if Postgres is stopped for a certain period. A detached node cannot receive connections. Specify the node ID at the end of the command line:
# COMMAND:
pcp_detach_node -h <pgpool_host> -p <pcp_port> -U pcpuser -w 2
# OUTPUT:
pcp_detach_node -- Command Successful
-
PCP_ATTACH_NODE: Attach a node to PgPool. This changes thedownstatus toupwithout needing to restart PgPool, confirming the node is attached to PgPool and can receive connections. Specify the node ID at the end of the command line:
# COMMAND:
pcp_attach_node -h <pgpool_host> -p <pcp_port> -U pcpuser -w 2
# OUTTPUT:
pcp_attach_node -- Command Successful
-
PCP_STOP_PGPOOL: Stop the PgPool process. This is not recommended if PgPool was started withsystemctl, as it stops PgPool but thesystemdservice for PgPool will continue running with errors. Optionally you can use the option-s clusterto stop PgPool processes on all PgPool nodes in Watchdog mode (the command will fail if all PgPool nodes are not online),-s localto stop only the specified PgPool process referenced by-h <pgpool_host>(it stops the specified PgPool node independently of the other PgPool nodes in the Watchdog cluster), and optionally use-mwithsmart, fast, or immediate:
# COMMAND:
pcp_stop_pgpool -h <pgpool_host> -p <pcp_port> -U pcpuser -w
# OUTPUT:
pcp_stop_pgpool -- Command Successful
-
PCP_RELOAD_CONFIG: Reload PgPool configurations. Use-s clusterto reload configurations on all PgPool nodes in Watchdog mode or-s localfor only the specified PgPool process referenced by-h <pgpool_host>:
# COMMAND:
pcp_reload_config -h <pgpool_host> -p <pcp_port> -U pcpuser -w
# OUTPUT:
pcp_reload_config -- Command Successful