pgpool2 exporter metrics issue

[masonlu2014]

hi team,

need u help check my pgpool2 exporter error log, pls help advis how i can fix that ?

An error has occurred while serving metrics:

12 error(s) occurred:

• collected metric "pgpool2_pool_nodes_status" { label:<name:"hostname" value:"" > label:<name:"port" value:"0" > label:<name:"role" value:"standby" > gauge:<value:0 > } was collected before with the same name and label values
• collected metric "pgpool2_pool_nodes_select_cnt" { label:<name:"hostname" value:"" > label:<name:"port" value:"0" > label:<name:"role" value:"standby" > counter:<value:0 > } was collected before with the same name and label values
• collected metric "pgpool2_pool_nodes_replication_delay" { label:<name:"hostname" value:"" > label:<name:"port" value:"0" > label:<name:"role" value:"standby" > gauge:<value:0 > } was collected before with the same name and label values
• collected metric "pgpool2_pool_nodes_status" { label:<name:"hostname" value:"" > label:<name:"port" value:"0" > label:<name:"role" value:"standby" > gauge:<value:0 > } was collected before with the same name and label values
• collected metric "pgpool2_pool_nodes_select_cnt" { label:<name:"hostname" value:"" > label:<name:"port" value:"0" > label:<name:"role" value:"standby" > counter:<value:0 > } was collected before with the same name and label values
• collected metric "pgpool2_pool_nodes_replication_delay" { label:<name:"hostname" value:"" > label:<name:"port" value:"0" > label:<name:"role" value:"standby" > gauge:<value:0 > } was collected before with the same name and label values
• collected metric "pgpool2_pool_nodes_status" { label:<name:"hostname" value:"" > label:<name:"port" value:"0" > label:<name:"role" value:"standby" > gauge:<value:0 > } was collected before with the same name and label values
• collected metric "pgpool2_pool_nodes_select_cnt" { label:<name:"hostname" value:"" > label:<name:"port" value:"0" > label:<name:"role" value:"standby" > counter:<value:0 > } was collected before with the same name and label values
• collected metric "pgpool2_pool_nodes_replication_delay" { label:<name:"hostname" value:"" > label:<name:"port" value:"0" > label:<name:"role" value:"standby" > gauge:<value:0 > } was collected before with the same name and label values
• collected metric "pgpool2_pool_nodes_status" { label:<name:"hostname" value:"" > label:<name:"port" value:"0" > label:<name:"role" value:"standby" > gauge:<value:0 > } was collected before with the same name and label values
• collected metric "pgpool2_pool_nodes_select_cnt" { label:<name:"hostname" value:"" > label:<name:"port" value:"0" > label:<name:"role" value:"standby" > counter:<value:0 > } was collected before with the same name and label values
• collected metric "pgpool2_pool_nodes_replication_delay" { label:<name:"hostname" value:"" > label:<name:"port" value:"0" > label:<name:"role" value:"standby" > gauge:<value:0 > } was collected before with the same name and label values

[pengbo0328]

collected metric "pgpool2_pool_nodes_status" { label:<name:"hostname" value:"" > label:<name:"port" value:"0" > label:<name:"role" value:"standby" > gauge:<value:0 > } was collected before with the same name and label values

It seems the same name and label values has been collected before.
The hostname is "".
I think pgpool can't connect to PostgreSQL.

If it is possible, can you show the result of "show pool_nodes"?

[masonlu2014]

thanks for checking this

[masonlu2014]

we are have 5 node pG db , each one is a statusful set, and we have 5 pgpool pod in one deploment, deploment replicaset is 5

[masonlu2014]

nchoredb=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+-----------------------------------------------+------+--------+-----------+---------+------------+-------------------+-------------------
0 | | 0 | unused | 0.000000 | standby | 0 | false | 0
1 | | 0 | unused | 0.000000 | standby | 0 | false | 0
2 | | 0 | unused | 0.000000 | standby | 0 | false | 0
3 | | 0 | unused | 0.000000 | standby | 0 | false | 0
4 | | 0 | unused | 0.000000 | standby | 0 | false | 0
5 | pg-db-node6-service.imagescan.svc.26.tess.io | 5432 | up | 0.200000 | primary | 6464333 | false | 0
6 | pg-db-node7-service.imagescan.svc.26.tess.io | 5432 | up | 0.200000 | standby | 1828328 | true | 2568
7 | pg-db-node8-service.imagescan.svc.27.tess.io | 5432 | up | 0.200000 | standby | 1370226 | false | 2568
8 | pg-db-node9-service.imagescan.svc.27.tess.io | 5432 | up | 0.200000 | standby | 1194569 | false | 2568
9 | pg-db-node10-service.imagescan.svc.27.tess.io | 5432 | up | 0.200000 | standby | 1479570 | false | 2568
(10 rows)

[masonlu2014]

anchoredb=# show pool_version;
pool_version

3.6.7 (subaruboshi)
(1 row)

[masonlu2014]

anchoredb=# show pool_status;
item | value | description
--------------------------------------+--------------------------------------------------------------------+---------------------------------------------------------------------------------
listen_addresses | * | host name(s) or IP address(es) to listen on
port | 5432 | pgpool accepting port number
socket_dir | /var/run/postgresql | pgpool socket directory
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 | /var/run/postgresql | 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 | 30 | maximum time in seconds to complete client authentication
ssl | 0 | SSL support
ssl_key | | path to the SSL private key file
ssl_cert | | path to the SSL public certificate file
ssl_ca_cert | | path to a single PEM format file
ssl_ca_cert_dir | | directory containing CA root certificate(s)
num_init_children | 200 | # of children initially pre-forked
listen_backlog_multiplier | 32 | determines the size of the queue for pending connections
serialize_accept | 0 | whether to serialize accept() call
max_pool | 2 | max # of connection pool per child
child_life_time | 3600 | if idle for this seconds, child exits
child_max_connections | 0 | if max_connections received, chile exits
connection_life_time | 300 | if idle for this seconds, connection closes
client_idle_limit | 900 | if idle for this seconds, child connection closes
log_destination | stderr | logging destination
log_line_prefix | %t: 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_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
log_standby_delay | 3 | how to log standby delay
syslog_facility | LOCAL0 | syslog local faclity
syslog_ident | pgpool | syslog program ident string
pid_file_name | /var/run/postgresql/pgpool.pid | path to pid file
logdir | /var/log/postgresql | 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
replication_mode | 0 | non 0 if operating in replication mode
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
white_function_list | | functions those do not write to database
black_function_list | nextval,setval | functions those write to database
master_slave_mode | 1 | if true, operate in master/slave mode
master_slave_sub_mode | 2 | master/slave sub mode
sr_check_period | 30 | sr check period
sr_check_user | anchore_dbuser | sr check user
sr_check_database | postgres | sr check database
delay_threshold | 0 | standby delay threshold
follow_master_command | | follow master command
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 | 30 | health check period
health_check_timeout | 10 | health check timeout
health_check_user | anchore_dbuser | health check user
health_check_database | postgres | health check database
health_check_max_retries | 471 | health check max retries
health_check_retry_delay | 10 | health check retry delay
connect_timeout | 10000 | connect timeout
failover_command | echo ">>> Failover - that will initialize new primary node search" | failover command
failback_command | | failback command
fail_over_on_backend_error | 1 | fail over on backend error
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 | 0 | 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 statge
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
use_watchdog | 0 | non 0 if operating in use_watchdog
wd_ipc_socket_dir | /var/run/postgresql | 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 master pgpool resigns occurs
trusted_servers | | upper server list to observe connection
delegate_IP | | delegate IP address of master pgpool
wd_hostname | | Host name or IP address of this watchdog
wd_port | 9000 | watchdog port number
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 | ifconfig eth0:0 inet $IP$ netmask 255.255.255.0 | virtual interface up command with full parameters
if_down_cmd | ifconfig eth0:0 down | virtual interface down command with full parameters
arping_path | /usr/sbin | path to arping command
arping_cmd | arping -U $IP$ -w 1 | send ARP REQUESTi to neighbour host
wd_heartbeat_port | 9694 | port number for receiving heartbeat signal
wd_heartbeat_keepalive | 2 | interval time of sending heartbeat siganl (sec)
wd_heartbeat_deadtime | 30 | deadtime interval for heartbeat siganl (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
wd_lifecheck_password | | password for watchdog user in lifecheck
wd_monitoring_interfaces_list | | interfaces to monitor by watchdog
memory_cache_enabled | 0 | 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. Mondatory if memqcache_method=memcached
memqcache_total_size | 3221225472 | Total memory size in bytes for storing memory cache. Mandatory if memqcache_met
memqcache_max_num_cache | 10000000 | 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 | 2097152 | Cache block size in bytes. 8192 by default
memqcache_cache_oiddir | /var/log/pgpool/oiddir | Tempory work directory to record table oids
memqcache_stats_start_time | Thu Jan 1 00:00:00 1970 +| Start time of query cache stats
| |
memqcache_no_cache_hits | 0 | Number of SELECTs not hitting query cache
memqcache_cache_hits | 0 | Number of SELECTs hitting query cache
white_memqcache_table_list | | tables to memqcache
black_memqcache_table_list | | tables not to memqcache
backend_hostname5 | pg-db-node6-service.imagescan.svc.26.tess.io | backend #5 hostname
backend_port5 | 5432 | backend #5 port number
backend_weight5 | 0.200000 | weight of backend #5
backend_data_directory5 | /var/lib/postgresql/data | data directory for backend #5
backend_status5 | up | status of backend #5
standby_delay5 | 0 | standby delay of backend #5
backend_flag5 | ALLOW_TO_FAILOVER | backend #5 flag
backend_hostname6 | pg-db-node7-service.imagescan.svc.26.tess.io | backend #6 hostname
backend_port6 | 5432 | backend #6 port number
backend_weight6 | 0.200000 | weight of backend #6
backend_data_directory6 | /var/lib/postgresql/data | data directory for backend #6
backend_status6 | up | status of backend #6
standby_delay6 | 0 | standby delay of backend #6
backend_flag6 | ALLOW_TO_FAILOVER | backend #6 flag
backend_hostname7 | pg-db-node8-service.imagescan.svc.27.tess.io | backend #7 hostname
backend_port7 | 5432 | backend #7 port number
backend_weight7 | 0.200000 | weight of backend #7
backend_data_directory7 | /var/lib/postgresql/data | data directory for backend #7
backend_status7 | up | status of backend #7
standby_delay7 | 0 | standby delay of backend #7
backend_flag7 | ALLOW_TO_FAILOVER | backend #7 flag
backend_hostname8 | pg-db-node9-service.imagescan.svc.27.tess.io | backend #8 hostname
backend_port8 | 5432 | backend #8 port number
backend_weight8 | 0.200000 | weight of backend #8
backend_data_directory8 | /var/lib/postgresql/data | data directory for backend #8
backend_status8 | up | status of backend #8
standby_delay8 | 0 | standby delay of backend #8
backend_flag8 | ALLOW_TO_FAILOVER | backend #8 flag
backend_hostname9 | pg-db-node10-service.imagescan.svc.27.tess.io | backend #9 hostname
backend_port9 | 5432 | backend #9 port number
backend_weight9 | 0.200000 | weight of backend #9
backend_data_directory9 | /var/lib/postgresql/data | data directory for backend #9
backend_status9 | up | status of backend #9
standby_delay9 | 0 | standby delay of backend #9
backend_flag9 | ALLOW_TO_FAILOVER | backend #9 flag
heartbeat_device0 | | name of NIC device #0 for sending hearbeat
heartbeat_destination0 | host0_ip1 | destination host for sending heartbeat using NIC device 0
heartbeat_destination_port0 | 9694 | destination port for sending heartbeat using NIC device 0
(159 rows)

[masonlu2014]

we are using load balance ip from kubernetes service. and we are using deployment to build our pgpool2 cluster, and deployment replica is 5

[pengbo0328]

You are setting:

backend_hostname5
backend_port5 
backend_weight5 
backend_data_directory5
backend_flag5
...
backend_hostname9
backend_port9
backend_weight9
backend_data_directory9
backend_flag9

If you have 5 PostgreSQL servers, you should specify the number from 0 to 4:

backend_hostname0
backend_port0
backend_weight0
backend_data_directory0
backend_flag0
...
backend_hostname4
backend_port4
backend_weight4
backend_data_directory4
backend_flag4

[masonlu2014]

cool, thanks for help, let me try that

[pengbo0328]

@masonlu2014 May I close this issue?

[masonlu2014]

Sure, pls, thanks for help 从 Windows 版邮件<https://go.microsoft.com/fwlink/?LinkId=550986>发送 发件人: Bo ***@***.***> 发送时间: 2022年6月29日 13:42 收件人: ***@***.***> 抄送: ***@***.***>; ***@***.***> 主题: Re: [pgpool/pgpool2_exporter] pgpool2 exporter metrics issue (Issue #16) @masonlu2014<https://github.com/masonlu2014> May I close this issue? ― Reply to this email directly, view it on GitHub<#16 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ACAEWI4RYEF3GEWP3CIVW5TVRPO5NANCNFSM5UK2OPQQ>. You are receiving this because you were mentioned.Message ID: ***@***.***>