The objective of this article is to introduce the user to each PgPool SHOW command, providing a brief overview and information about the utility of each command.
These commands can be executed by connecting to Postgres via PgPool, and they can also be run from the command line using:
psql -p <pgpool_port> -h <pgpool_host> -U <db_user> -d <any_database> -c '<SHOW_COMMAND>;'
Displays the PgPool version.
Sample output:
pool_version
----------------------
4.5.1 (hotooriboshi)
(1 row)
This is probably the most used command, providing information about the current status of each Postgres backend configured in PgPool.
Sample output:
node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+---------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | 192.168.1.100 | 5444 | down | up | 0.333333 | standby | standby | 0 | false | 0 | | | 2024-11-17 08:35:40
1 | 192.168.1.101 | 5444 | up | up | 0.333333 | primary | primary | 0 | false | 0 | | | 2024-11-17 08:35:48
2 | 192.168.1.102 | 5444 | up | up | 0.333333 | standby | standby | 0 | true | 0 | | | 2024-11-17 08:35:48
(3 rows)
Notable columns:
-
node_id: References the integer defined in thebackend_ parameters; -
hostname: Value defined in thebackend_hostnameparameter; -
port: Value defined in thebackend_portparameter; -
status: Indicates if a Postgres node is attached or detached from PgPool (upfor attached,downfor detached,waitingfor reloading status of backends once at least one of them got detached or attached while client connected to PgPool, the client must reconnect to PgPool for the status to be displayed correctly, andquarantinefor nodes no yet detached from the PgPool due to lack of consensus of Watchdog quorum); -
pg_status: Indicates if the Postgres instance is online or offline, monitored by thehealth_check_*parameters; -
lb_weight: Load balance weight. This represents the probability of a backend receiving a SELECT statement. Controlled by thebackend_weightparameter; a weight of0prevents the backend from receivingSELECTstatements; -
role: Streaming replication role of each node, monitored by thesr_check_*parameters. PgPool cannot identify the primary node without these parameters. -
pg_role: Streaming replication role monitored byhealth_check_*parameters. This must match therolecolumn value; -
select_cnt: Number ofSELECTstatements sent to each node by load balancing. This value resets when PgPool is restarted; -
load_balance_node: Indicates the backend currently connected for load balancing (true for the active backend). Ifload_balance_modeisoff, connections will likely route to the same backend. For session-level load balancing (statement_level_load_balance = off), this flag changes at the session level. For statement-level load balancing (statement_level_load_balance = on), this flag changes with everySELECT; -
last_status_change: Timestamp of the last change in thestatuscolumn. This resets when PgPool is restarted and is not shared across PgPool nodes.
For more information, consult the documentation: SHOW POOL NODES
This command provides information about the PgPool child processes. The number of rows displayed is equivalent to the number of initialized PgPool child processes, as defined by the num_init_children parameter. In the example below, num_init_children = 2.
The view also displays the time remaining before each child process is restarted. This duration is managed by the child_life_time parameter, which is used to restart child processes to clean up idle connections and prevent memory leaks. If child_life_time is set to 0, the child processes will never restart.
Sample output:
pool_pid | start_time | client_connection_count | database | username | backend_connection_time | pool_counter | status
----------+------------------------------------------------------+-------------------------+----------+--------------+-------------------------+--------------+---------------------
2110 | 2024-11-17 09:10:13 (4:57 before process restarting) | 9 | | | | | Wait for connection
2111 | 2024-11-17 09:10:13 | 6 | postgres | enterprisedb | 2024-11-17 10:02:10 | 1 | Execute command
(2 rows)
For more information, consult the documentation: SHOW POOL_PROCESSES
This command provides information about the pools managed by the connected PgPool node. Note that this information is not shared with other PgPool nodes in a Watchdog cluster.
The number of rows returned is calculated as:
num_init_children * max_pool * number_of_backends
In the example below:
num_init_children = 2
max_pool = 4
number_of_backends = 3 (Postgres nodes)
Sample output:
pool_pid | start_time | client_connection_count | pool_id | backend_id | database | username | backend_connection_time | client_connection_time | client_disconnection_time | client_idle_duration | majorversion | minorversion | pool_counter | pool_backendpid | pool_connected | status | load_balance_node
----------+---------------------+-------------------------+---------+------------+----------+--------------+-------------------------+------------------------+---------------------------+----------------------+--------------+--------------+--------------+-----------------+----------------+---------------------+-------------------
2190 | 2024-11-17 10:54:48 | 0 | 0 | 0 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Wait for connection | 0
2190 | 2024-11-17 10:54:48 | 0 | 0 | 1 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Wait for connection | 0
2190 | 2024-11-17 10:54:48 | 0 | 0 | 2 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Wait for connection | 0
2190 | 2024-11-17 10:54:48 | 0 | 1 | 0 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Wait for connection | 0
2190 | 2024-11-17 10:54:48 | 0 | 1 | 1 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Wait for connection | 0
2190 | 2024-11-17 10:54:48 | 0 | 1 | 2 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Wait for connection | 0
2190 | 2024-11-17 10:54:48 | 0 | 2 | 0 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Wait for connection | 0
2190 | 2024-11-17 10:54:48 | 0 | 2 | 1 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Wait for connection | 0
2190 | 2024-11-17 10:54:48 | 0 | 2 | 2 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Wait for connection | 0
2190 | 2024-11-17 10:54:48 | 0 | 3 | 0 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Wait for connection | 0
2190 | 2024-11-17 10:54:48 | 0 | 3 | 1 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Wait for connection | 0
2190 | 2024-11-17 10:54:48 | 0 | 3 | 2 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Wait for connection | 0
2189 | 2024-11-17 10:54:25 | 1 | 0 | 0 | postgres | enterprisedb | 2024-11-17 11:00:34 | 2024-11-17 11:00:34 | 2024-11-17 11:00:32 | 0 | 3 | 0 | 1 | 6221 | 1 | Execute command | 0
2189 | 2024-11-17 10:54:25 | 1 | 0 | 1 | postgres | enterprisedb | 2024-11-17 11:00:34 | 2024-11-17 11:00:34 | 2024-11-17 11:00:32 | 0 | 3 | 0 | 1 | 6784 | 1 | Execute command | 1
2189 | 2024-11-17 10:54:25 | 1 | 0 | 2 | postgres | enterprisedb | 2024-11-17 11:00:34 | 2024-11-17 11:00:34 | 2024-11-17 11:00:32 | 0 | 3 | 0 | 1 | 6285 | 1 | Execute command | 0
2189 | 2024-11-17 10:54:25 | 1 | 1 | 0 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Execute command | 0
2189 | 2024-11-17 10:54:25 | 1 | 1 | 1 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Execute command | 0
2189 | 2024-11-17 10:54:25 | 1 | 1 | 2 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Execute command | 0
2189 | 2024-11-17 10:54:25 | 1 | 2 | 0 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Execute command | 0
2189 | 2024-11-17 10:54:25 | 1 | 2 | 1 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Execute command | 0
2189 | 2024-11-17 10:54:25 | 1 | 2 | 2 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Execute command | 0
2189 | 2024-11-17 10:54:25 | 1 | 3 | 0 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Execute command | 0
2189 | 2024-11-17 10:54:25 | 1 | 3 | 1 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Execute command | 0
2189 | 2024-11-17 10:54:25 | 1 | 3 | 2 | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | Execute command | 0
(24 rows)
For details on each column, please refer to the documentation: SHOW POOL_POOLS.
Provides information about the cache storage for SELECT statements. It requires the parameter memory_cache_enabled = on to be set. If this parameter is not enabled, all column values will display as 0.
Sample output:
num_cache_hits | num_selects | cache_hit_ratio | num_hash_entries | used_hash_entries | num_cache_entries | used_cache_entries_size | free_cache_entries_size | fragment_cache_entries_size
----------------+-------------+-----------------+------------------+-------------------+-------------------+-------------------------+-------------------------+-----------------------------
1 | 1 | 0.50 | 1048576 | 3 | 3 | 454 | 67108410 | 0
(1 row)
Refer to the documentation for further details: SHOW POOL_CACHE.
For information on the cache mechanism, consult: In Memory Query Cache.
Displays health check statistics collected by the health check process. It requires the health_check_* parameters to be configured to gather statistics.
Sample output:
node_id | hostname | port | status | role | last_status_change | total_count | success_count | fail_count | skip_count | retry_count | average_retry_count | max_retry_count | max_duration | min_duration | average_duration | last_health_check | last_successful_health_check | last_skip_health_check | last_failed_health_check
---------+---------------+------+--------+---------+---------------------+-------------+---------------+------------+------------+-------------+---------------------+-----------------+--------------+--------------+------------------+---------------------+------------------------------+------------------------+--------------------------
0 | 192.168.1.100 | 5444 | up | standby | 2024-11-17 11:46:14 | 90 | 90 | 0 | 0 | 0 | 0.000000 | 0 | 179 | 30 | 98.388889 | 2024-11-17 12:00:58 | 2024-11-17 12:00:58 | |
1 | 192.168.1.101 | 5444 | up | primary | 2024-11-17 11:46:14 | 90 | 90 | 0 | 0 | 0 | 0.000000 | 0 | 165 | 38 | 92.344444 | 2024-11-17 12:00:58 | 2024-11-17 12:00:58 | |
2 | 192.168.1.102 | 5444 | up | standby | 2024-11-17 11:46:14 | 90 | 90 | 0 | 0 | 0 | 0.000000 | 0 | 150 | 32 | 90.166667 | 2024-11-17 12:00:58 | 2024-11-17 12:00:58 | |
(3 rows)
Refer to the documentation for detailed information: SHOW POOL_HEALTH_CHECK_STATS
Provides statistics on the status of each PostgreSQL backend, including metrics such as the number of SELECTs, INSERTs, UPDATEs, and DELETEs executed on each backend.
Sample output:
node_id | hostname | port | status | role | select_cnt | insert_cnt | update_cnt | delete_cnt | ddl_cnt | other_cnt | panic_cnt | fatal_cnt | error_cnt
---------+---------------+------+--------+---------+------------+------------+------------+------------+---------+-----------+-----------+-----------+-----------
0 | 192.168.1.100 | 5444 | up | standby | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
1 | 192.168.1.101 | 5444 | up | primary | 10 | 3 | 0 | 0 | 2 | 1 | 0 | 0 | 0
2 | 192.168.1.102 | 5444 | up | standby | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
(3 rows)
Refer to the documentation for further details: SHOW POOL_BACKEND_STATS
This command provides a list of PgPool configuration parameters, including their names, values, and descriptions.
Sample output:
item | value | description
---------------------------------------+-------------------------------------------------------------------------+---------------------------------------------------------------------------------
backend_clustering_mode | 1 | clustering mode
listen_addresses | * | host name(s) or IP address(es) to listen on
port | 9999 | pgpool accepting port number
unix_socket_directories | /tmp | pgpool socket directories
unix_socket_group | | owning user of the unix sockets
unix_socket_permissions | 0777 | access permissions of the unix sockets.
pcp_listen_addresses | * | host name(s) or IP address(es) for pcp process to listen on
pcp_port | 9898 | PCP port # to bind
pcp_socket_dir | /tmp | PCP socket directory
enable_pool_hba | 1 | if true, use pool_hba.conf for client authentication
pool_passwd | pool_passwd | file name of pool_passwd for md5 authentication
authentication_timeout | 60 | maximum time in seconds to complete client authentication
allow_clear_text_frontend_auth | 0 | allow to use clear text password auth when pool_passwd does not contain passwor
ssl | 0 | SSL support
ssl_key | | SSL private key file
ssl_cert | | SSL public certificate file
ssl_ca_cert | | single PEM format file containing CA root certificate(s)
ssl_ca_cert_dir | | directory containing CA root certificate(s)
ssl_crl_file | | SSL certificate revocation list file
ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL | allowed SSL ciphers
ssl_prefer_server_ciphers | 0 | Use server's SSL cipher preferences
ssl_ecdh_curve | prime256v1 | the curve to use in ECDH key exchange
ssl_dh_params_file | | path to the Diffie-Hellman parameters contained file
ssl_passphrase_command | | external command to be invoked when a passphrase for decrypting an SSL file suc
listen_backlog_multiplier | 2 | determines the size of the queue for pending connections
serialize_accept | 0 | whether to serialize accept() call
reserved_connections | 0 | number of reserved connections
max_pool | 4 | max # of connection pool per child
process_management_mode | 1 | process management mode
process_management_strategy | 2 | process management strategy
num_init_children | 2 | # of children initially pre-forked
min_spare_children | 5 | min # of spare children waitting for connection
max_spare_children | 10 | max # of spare children waitting for connection
child_life_time | 300 | if idle for this seconds, child exits
child_max_connections | 0 | if max_connections received, child exits
connection_life_time | 0 | if idle for this seconds, connection closes
client_idle_limit | 0 | if idle for this seconds, child connection closes
log_destination | stderr | logging destination
log_line_prefix | %m: %a pid %p: | printf-style string to output at beginning of each log line
log_error_verbosity | 1 | controls how much detail about error should be emitted
client_min_messages | 18 | controls which message should be sent to client
log_min_messages | 19 | controls which message should be emitted to server log
log_connections | 0 | if true, print incoming connections to the log
log_disconnections | 0 | if true, print closing connections to the log
log_pcp_processes | 1 | if true, print PCP process forks and anormal exit status to the log
log_hostname | 0 | if true, resolve hostname for ps and log print
log_statement | 0 | if non 0, logs all SQL statements
log_per_node_statement | 0 | if non 0, logs all SQL statements on each node
notice_per_node_statement | 0 | if non 0, logs all SQL statements on each node as notice message
log_client_messages | 0 | if non 0, logs any client messages
log_standby_delay | 2 | how to log standby delay
syslog_facility | LOCAL0 | syslog local facility
syslog_ident | pgpool | syslog program ident string
pid_file_name | /run/edb/pgpool4.5/edb-pgpool-4.5.pid | path to pid file
logdir | /var/log/edb/pgpool4.5 | PgPool status file logging directory
connection_cache | 1 | if true, cache connection pool
reset_query_list | ABORT;DISCARD ALL | queries issued at the end of session
replicate_select | 0 | non 0 if SELECT statement is replicated
insert_lock | 1 | insert lock
lobj_lock_table | | table name used for large object replication control
replication_stop_on_mismatch | 0 | stop replication mode on fatal error
failover_if_affected_tuples_mismatch | 0 | failover if affected tuples are mismatch
load_balance_mode | 1 | non 0 if operating in load balancing mode
ignore_leading_white_space | 1 | ignore leading white spaces
read_only_function_list | | functions those do not write to database
write_function_list | | functions those write to database
primary_routing_query_pattern_list | | query patterns that should be sent to primary node
disable_load_balance_on_write | 2 | Load balance behavior when write query is received
dml_adaptive_object_relationship_list | (null) | list of relationships between objects
statement_level_load_balance | 0 | statement level load balancing
sr_check_period | 10 | sr check period
sr_check_user | enterprisedb | sr check user
sr_check_database | postgres | sr check database
delay_threshold | 0 | standby delay threshold
delay_threshold_by_time | 0 | standby delay threshold by time
prefer_lower_delay_standby | 0 | load balancing considering streaming delay
follow_primary_command | | follow primary command
user_redirect_preference_list | (null) | redirect by user name
database_redirect_preference_list | (null) | redirect by database name
app_name_redirect_preference_list | (null) | redirect by application name
allow_sql_comments | 0 | allow SQL comments
health_check_period | 10 | health check period
health_check_timeout | 20 | health check timeout
health_check_user | enterprisedb | health check user
health_check_database | postgres | health check database
health_check_max_retries | 3 | health check max retries
health_check_retry_delay | 5 | health check retry delay
connect_timeout | 10000 | connect timeout
failover_command | | failover command
failback_command | | failback command
failover_on_backend_error | 0 | failover on backend error
failover_on_backend_shutdown | 0 | failover on backend shutdown
detach_false_primary | 0 | detach false primary
auto_failback | 0 | auto_failback
auto_failback_interval | 60 | auto_failback_interval
recovery_user | nobody | online recovery user
recovery_1st_stage_command | | execute a command in first stage.
recovery_2nd_stage_command | | execute a command in second stage.
recovery_timeout | 90 | max time in seconds to wait for the recovering node's postmaster
search_primary_node_timeout | 10 | max time in seconds to search for primary node after failover
client_idle_limit_in_recovery | 0 | if idle for this seconds, child connection closes in recovery 2nd stage
relcache_expire | 0 | relation cache expiration time in seconds
relcache_size | 256 | number of relation cache entry
check_temp_table | 1 | enable temporary table check
check_unlogged_table | 1 | enable unlogged table check
enable_shared_relcache | 1 | If true, relation cache stored in memory cache
relcache_query_target | 1 | Target node to send relcache queries
use_watchdog | 1 | non 0 if operating in use_watchdog
wd_ipc_socket_dir | /tmp | watchdog ipc socket directory
wd_lifecheck_method | 2 | method of watchdog lifecheck
clear_memqcache_on_escalation | 1 | If true, clear all the query caches in shared memory when escalation occurs
wd_escalation_command | | command executed when escalation occurs
wd_de_escalation_command | | command executed when leader pgpool resigns occurs
trusted_servers | | upper server list to observe connection
trusted_server_command | ping -q -c3 %h | command executed when upper servers are observed
delegate_ip | 192.168.1.249 | delegate IP address of leader pgpool
wd_priority | 1 | watchdog priority
wd_interval | 10 | life check interval (second)
ping_path | /bin | path to ping command
if_cmd_path | /sbin | path to interface up/down command
if_up_cmd | /usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev enp0s3 label (PGPOOL-VIP) | virtual interface up command with full parameters
if_down_cmd | /usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev enp0s3 | virtual interface down command with full parameters
arping_path | /usr/sbin | path to arping command
arping_cmd | /usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I eth0 | send ARP REQUEST to neighbour host
wd_heartbeat_keepalive | 2 | interval time of sending heartbeat signal (sec)
wd_heartbeat_deadtime | 30 | deadtime interval for heartbeat signal (sec)
wd_life_point | 3 | retry times of life check
wd_lifecheck_query | SELECT 1 | lifecheck query to pgpool from watchdog
wd_lifecheck_dbname | template1 | database name connected for lifecheck
wd_lifecheck_user | nobody | watchdog user monitoring pgpools in lifecheck
hostname0 | 192.168.1.103 | hostname or IP address of local pgpool node
pgpool_port0 | 9999 | local pgpool port number
wd_port0 | 9000 | local watchdog port number
hostname1 | 192.168.1.104 | hostname or IP address of pgpool node #1
pgpool_port1 | 9999 | pgpool port number of pgpool node #1
wd_port1 | 9000 | watchdog port number of pgpool node #1
hostname2 | 192.168.1.105 | hostname or IP address of pgpool node #2
pgpool_port2 | 9999 | pgpool port number of pgpool node #2
wd_port2 | 9000 | watchdog port number of pgpool node #2
heartbeat_hostname0 | 192.168.1.103 | IP address or hostname for receiving heartbeat
heartbeat_device0 | enp0s3 | name of NIC device for receiving hearbeat
heartbeat_port0 | 9694 | port number for receiving heartbeat
heartbeat_hostname1 | 192.168.1.104 | destination IP address or hostname for sending heartbeat to pgpool node #1
heartbeat_device1 | enp0s3 | name of NIC device for sending hearbeat to pgpool node #1
heartbeat_port1 | 9694 | destination port for sending heartbeat to pgpool node #1
heartbeat_hostname2 | 192.168.1.105 | destination IP address or hostname for sending heartbeat to pgpool node #2
heartbeat_device2 | enp0s3 | name of NIC device for sending hearbeat to pgpool node #2
heartbeat_port2 | 9694 | destination port for sending heartbeat to pgpool node #2
wd_monitoring_interfaces_list | | interfaces to monitor by watchdog
memory_cache_enabled | 1 | If true, use the memory cache functionality, false by default
memqcache_method | 1 | Cache store method. either shmem(shared memory) or Memcached. shmem by default
memqcache_memcached_host | localhost | Memcached host name. Mandatory if memqcache_method=memcached
memqcache_memcached_port | 11211 | Memcached port number. Mandatory if memqcache_method=memcached
memqcache_total_size | 67108864 | Total memory size in bytes for storing memory cache. Mandatory if memqcache_met
memqcache_max_num_cache | 1000000 | Total number of cache entries
memqcache_expire | 0 | Memory cache entry life time specified in seconds. 60 by default
memqcache_auto_cache_invalidation | 1 | If true, invalidation of query cache is triggered by corresponding DDL/DML/DCL(
memqcache_maxcache | 409600 | Maximum SELECT result size in bytes
memqcache_cache_block_size | 1048576 | Cache block size in bytes. 8192 by default
memqcache_cache_oiddir | /var/log/edb/pgpool4.5/oiddir | Temporary work directory to record table oids
memqcache_stats_start_time | Sun Nov 17 11:45:58 2024 +| Start time of query cache stats
| |
memqcache_no_cache_hits | 10 | Number of SELECTs not hitting query cache
memqcache_cache_hits | 4 | Number of SELECTs hitting query cache
cache_safe_memqcache_table_list | | tables to memqcache
cache_unsafe_memqcache_table_list | | tables not to memqcache
backend_hostname0 | 192.168.1.100 | backend #0 hostname
backend_port0 | 5444 | backend #0 port number
backend_weight0 | 0.333333 | weight of backend #0
backend_data_directory0 | /var/lib/edb/as16/data | data directory for backend #0
backend_status0 | up | status of backend #0
standby_delay0 | 0 | standby delay of backend #0
backend_flag0 | ALLOW_TO_FAILOVER | backend #0 flag
backend_application_name0 | server0 | application_name for backend #0
backend_hostname1 | 192.168.1.101 | backend #1 hostname
backend_port1 | 5444 | backend #1 port number
backend_weight1 | 0.333333 | weight of backend #1
backend_data_directory1 | /var/lib/edb/as16/data | data directory for backend #1
backend_status1 | up | status of backend #1
standby_delay1 | 0 | standby delay of backend #1
backend_flag1 | ALLOW_TO_FAILOVER | backend #1 flag
backend_application_name1 | | application_name for backend #1
backend_hostname2 | 192.168.1.102 | backend #2 hostname
backend_port2 | 5444 | backend #2 port number
backend_weight2 | 0.333333 | weight of backend #2
backend_data_directory2 | /var/lib/edb/as16/data | data directory for backend #2
backend_status2 | up | status of backend #2
standby_delay2 | 0 | standby delay of backend #2
backend_flag2 | ALLOW_TO_FAILOVER | backend #2 flag
backend_application_name2 | | application_name for backend #2
(189 rows)
Refer to the documentation for further details: SHOW POOL_STATUS