[TST][paqcxast01].root:~ # ip a 1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever 2: ens192: mtu 1500 qdisc mq state UP group default qlen 1000 link/ether 00:50:56:8f:f4:1d brd ff:ff:ff:ff:ff:ff altname enp11s0 inet 10.23.18.111/24 brd 10.23.18.255 scope global noprefixroute ens192 valid_lft forever preferred_lft forever inet 10.23.18.118/24 scope global secondary ens192:0 valid_lft forever preferred_lft forever [TST][paqcxast01].root:~ # [TST][paqcxast01].root:~ # [TST][paqcxast01].root:~ # [TST][paqcxast01].root:~ # date Thu Oct 26 11:49:13 CEST 2023 [TST][paqcxast01].root:~ # su postgres -c 'pcp_watchdog_info -h localhost -p 9898 -U pgpool -w' 3 3 YES paqcxast01.aaa.es:9999 Linux paqcxast01.aaa.es paqcxast01.aaa.es paqcxast01.aaa.es:9999 Linux paqcxast01.aaa.es paqcxast01.aaa.es 9999 9000 4 LEADER 0 MEMBER paqcxast02.aaa.es:9999 Linux paqcxast02.aaa.es paqcxast02.aaa.es 9999 9000 7 STANDBY 0 MEMBER paqcxast04.aaa.es:9999 Linux paqcxast04.aaa.es paqcxast04.aaa.es 9999 9000 7 STANDBY 0 MEMBER [TST][paqcxast01].root:~ # su postgres -c 'psql -h localhost -p 9999 -U usr_pg_pool -d postgres -c "show pool_nodes;"' psql: /usr/pgsql-15/lib/libpq.so.5: no version information available (required by psql) psql: /usr/pgsql-15/lib/libpq.so.5: no version information available (required by psql) psql: /usr/pgsql-15/lib/libpq.so.5: no version information available (required by psql) could not change directory to "/root": Permission denied 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 | paqcxast01.aaa.es | 5432 | up | up | 0.500000 | primary | primary | 423 | true | 0 | | | 2023-10-26 11:39:1 2 1 | paqcxast02.aaa.es | 5432 | up | up | 0.500000 | standby | standby | 144 | false | 0 | | | 2023-10-26 11:39:1 2 (2 rows) [TST][paqcxast01].root:~ # su postgres -c 'psql -h localhost -U usr_pg_pool -d postgres -c "select * from pg_stat_replication;"' psql: /usr/pgsql-15/lib/libpq.so.5: no version information available (required by psql) psql: /usr/pgsql-15/lib/libpq.so.5: no version information available (required by psql) psql: /usr/pgsql-15/lib/libpq.so.5: no version information available (required by psql) could not change directory to "/root": Permission denied pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_ls n | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time -------+----------+-----------------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+------------+---------- --+-----------+-----------+------------+---------------+------------+------------------------------- 20301 | 221118 | usr_replication | walreceiver | 10.151.18.82 | | 56948 | 2023-10-26 09:36:01.110523+00 | | streaming | 1/A000C488 | 1/A000C488 | 1/A000C488 | 1/A000C48 8 | | | | 0 | async | 2023-10-26 09:49:14.440119+00 (1 row) [TST][paqcxast01].root:~ # su postgres -c 'psql -h localhost -U usr_pg_pool -d postgres -c "select * from pg_replication_slots;"' psql: /usr/pgsql-15/lib/libpq.so.5: no version information available (required by psql) psql: /usr/pgsql-15/lib/libpq.so.5: no version information available (required by psql) psql: /usr/pgsql-15/lib/libpq.so.5: no version information available (required by psql) could not change directory to "/root": Permission denied slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size -----------+--------+-----------+--------+----------+-----------+--------+------------+--------+--------------+-------------+---------------------+------------+--------------- replica | | physical | | | f | t | 20301 | 351034 | | 1/A000C488 | | reserved | (1 row) [TST][paqcxast01].root:~ # su postgres -c 'psql -h localhost -p 9999 -U usr_pg_pool -d postgres -c "PGPOOL SHOW ALL;"' psql: /usr/pgsql-15/lib/libpq.so.5: no version information available (required by psql) psql: /usr/pgsql-15/lib/libpq.so.5: no version information available (required by psql) psql: /usr/pgsql-15/lib/libpq.so.5: no version information available (required by psql) could not change directory to "/root": Permission denied item | value | description --------------------------------------------+-------------------------------------------------------------------------+-------------------------------------------------------------------------------------------- ------------------------------ backend_hostname0 | paqcxast01.aaa.es | hostname or IP address of PostgreSQL backend. backend_port0 | 5432 | port number of PostgreSQL backend. backend_weight0 | 1 | load balance weight of backend. backend_data_directory0 | /var/lib/pgsql/data | data directory of the backend. backend_application_name0 | paqcxast01 | application_name of the backend. backend_flag0 | ALLOW_TO_FAILOVER | Controls various backend behavior. backend_hostname1 | paqcxast02.aaa.es | hostname or IP address of PostgreSQL backend. backend_port1 | 5432 | port number of PostgreSQL backend. backend_weight1 | 1 | load balance weight of backend. backend_data_directory1 | /var/lib/pgsql/data | data directory of the backend. backend_application_name1 | paqcxast02 | application_name of the backend. backend_flag1 | ALLOW_TO_FAILOVER | Controls various backend behavior. hostname0 | paqcxast01.aaa.es | Hostname of pgpool node for watchdog connection. pgpool_port0 | 9999 | tcp/ip pgpool port number of other pgpool node for watchdog connection. wd_port0 | 9000 | tcp/ip watchdog port number of other pgpool node for watchdog connection.. hostname1 | paqcxast02.aaa.es | Hostname of pgpool node for watchdog connection. pgpool_port1 | 9999 | tcp/ip pgpool port number of other pgpool node for watchdog connection. wd_port1 | 9000 | tcp/ip watchdog port number of other pgpool node for watchdog connection.. hostname2 | paqcxast04.aaa.es | Hostname of pgpool node for watchdog connection. pgpool_port2 | 9999 | tcp/ip pgpool port number of other pgpool node for watchdog connection. wd_port2 | 9000 | tcp/ip watchdog port number of other pgpool node for watchdog connection.. heartbeat_device0 | | Name of NIC device for sending heartbeat. heartbeat_hostname0 | paqcxast01.aaa.es | Hostname for sending heartbeat signal. heartbeat_port0 | 9694 | Port for sending heartbeat. heartbeat_device1 | | Name of NIC device for sending heartbeat. heartbeat_hostname1 | paqcxast02.aaa.es | Hostname for sending heartbeat signal. heartbeat_port1 | 9694 | Port for sending heartbeat. heartbeat_device2 | | Name of NIC device for sending heartbeat. heartbeat_hostname2 | paqcxast04.aaa.es | Hostname for sending heartbeat signal. heartbeat_port2 | 9694 | Port for sending heartbeat. health_check_period | 5 | Time interval in seconds between the health checks. health_check_timeout | 20 | Backend node health check timeout value in seconds. health_check_user | usr_pg_pool | User name for PostgreSQL backend health check. health_check_password | ***** | Password for PostgreSQL backend health check database user. health_check_database | | The database name to be used to perform PostgreSQL backend health check. health_check_max_retries | 0 | The maximum number of times to retry a failed health check before giving up and initiating failover. health_check_retry_delay | 2 | The amount of time in seconds to wait between failed health check retries. connect_timeout | 10000 | Timeout in milliseconds before giving up connecting to backend. health_check_period0 | 5 | Time interval in seconds between the health checks. health_check_timeout0 | 20 | Backend node health check timeout value in seconds. health_check_user0 | usr_pg_pool | User name for PostgreSQL backend health check. health_check_password0 | ***** | Password for PostgreSQL backend health check database user. health_check_database0 | | The database name to be used to perform PostgreSQL backend health check. health_check_max_retries0 | 0 | The maximum number of times to retry a failed health check before giving up and initiating failover. health_check_retry_delay0 | 2 | The amount of time in seconds to wait between failed health check retries. connect_timeout0 | 10000 | Timeout in milliseconds before giving up connecting to backend. health_check_period1 | 5 | Time interval in seconds between the health checks. health_check_timeout1 | 20 | Backend node health check timeout value in seconds. health_check_user1 | usr_pg_pool | User name for PostgreSQL backend health check. health_check_password1 | ***** | Password for PostgreSQL backend health check database user. health_check_database1 | | The database name to be used to perform PostgreSQL backend health check. health_check_max_retries1 | 0 | The maximum number of times to retry a failed health check before giving up and initiating failover. health_check_retry_delay1 | 2 | The amount of time in seconds to wait between failed health check retries. connect_timeout1 | 10000 | Timeout in milliseconds before giving up connecting to backend. allow_multiple_failover_requests_from_node | off | A Pgpool-II node can send multiple failover requests to build consensus. dml_adaptive_object_relationship_list | | list of relationships between objects. failover_if_affected_tuples_mismatch | off | Starts degeneration, If there's a data mismatch between primary and secondary. primary_routing_query_pattern_list | | list of query patterns that should be sent to primary node. app_name_redirect_preference_list | | redirect by application name. memqcache_auto_cache_invalidation | on | Automatically deletes the cache related to the updated tables. cache_unsafe_memqcache_table_list | | list of tables should not be cached. database_redirect_preference_list | | redirect by database name. enable_consensus_with_half_votes | off | apply majority rule for consensus and quorum computation at 50% of votes in a cluster with an even number of nodes. wd_no_show_node_removal_timeout | 0 | Timeout in seconds to revoke the cluster membership of NO-SHOW watchdog nodes. cache_safe_memqcache_table_list | | list of tables to be cached. allow_clear_text_frontend_auth | off | allow to use clear text password authentication with clients, when pool_passwd does not con tain the user password. clear_memqcache_on_escalation | on | Clears the query cache in the shared memory when pgpool-II escalates to leader watchdog nod e. client_idle_limit_in_recovery | 0 | Time limit is seconds for the child connection, before it is terminated during the 2nd stag e recovery. disable_load_balance_on_write | transaction | Load balance behavior when write query is received. wd_monitoring_interfaces_list | | List of network device names, to be monitored by the watchdog process for the network link state. statement_level_load_balance | off | Enables statement level load balancing failover_on_backend_shutdown | on | Triggers fail over when backend is shutdown. wd_lost_node_removal_timeout | 0 | Timeout in seconds to revoke the cluster membership of LOST watchdog nodes. replication_stop_on_mismatch | off | Starts degeneration and stops replication, If there's a data mismatch between primary and s econdary. process_management_strategy | gentle | child process management strategy. search_primary_node_timeout | 5min | Max time in seconds to search for primary node after failover. failover_when_quorum_exists | on | Do failover only when cluster has the quorum. recovery_2nd_stage_command | | Command to execute in second stage recovery. ignore_leading_white_space | on | Ignores leading white spaces of each query string. memqcache_cache_block_size | 1MB | Cache block size in bytes. prefer_lower_delay_standby | off | If the load balance node is delayed over delay_threshold on SR, pgpool find another standby node which is lower delayed. failover_require_consensus | on | Only do failover when majority aggrees. recovery_1st_stage_command | recovery_1st_stage | Command to execute in first stage recovery. failover_on_backend_error | on | Triggers fail over when reading/writing to backend socket fails. listen_backlog_multiplier | 2 | length of connection queue from frontend to pgpool-II ssl_prefer_server_ciphers | off | Use server's SSL cipher preferences, rather than the client's wd_remove_shutdown_nodes | off | Revoke the cluster membership of properly shutdown watchdog nodes. memqcache_memcached_port | 11211 | Port number of Memcached server. wd_de_escalation_command | | Command to execute when watchdog node resigns from the cluster leader node. memqcache_memcached_host | localhost | Hostname or IP address of memcached. log_truncate_on_rotation | off | If on, an existing log file gets truncated on time based log rotation. memqcache_max_num_cache | 1000000 | Total number of cache entries. backend_clustering_mode | streaming_replication | backend clustering mode. process_management_mode | static | child process management mode. unix_socket_permissions | 0777 | The access permissions of the Unix domain sockets. delay_threshold_by_time | 0 | standby delay threshold by time. unix_socket_directories | /run/pgpool | The directories to create the UNIX domain sockets for accepting pgpool-II client connection s. read_only_function_list | | list of functions that does not writes to database. trusted_server_command | ping -q -c3 %h | Command to excute when communicate with trusted server. ssl_passphrase_command | | Path to the Diffie-Hellman parameters contained file authentication_timeout | 1min | Time out value in seconds for client authentication. log_per_node_statement | off | Logs per node detailed SQL statements. follow_primary_command | | Command to execute in streaming replication mode after a primary node failover. auto_failback_interval | 1min | min interval of executing auto_failback in seconds wd_heartbeat_keepalive | 5s | Time interval in seconds between sending the heartbeat signal. enable_shared_relcache | on | relation cache stored in memory cache. child_max_connections | 0 | A pgpool-II child process will be terminated after this many connections from clients. wd_lifecheck_password | ***** | Password for watchdog user in lifecheck. wd_escalation_command | /etc/pgpool-II/escalation.sh | Command to execute when watchdog node becomes cluster leader node. wd_heartbeat_deadtime | 30s | Deadtime interval in seconds for heartbeat signal. relcache_query_target | primary | Target node to send relache queries. reserved_connections | 0 | Number of reserved connections. pcp_listen_addresses | * | hostname(s) or IP address(es) on which pcp will listen on. memqcache_total_size | 64MB | Total memory size in bytes for storing memory cache. detach_false_primary | off | Automatically detaches false primary node. connection_life_time | 2min | Cached connections expiration time in seconds. check_unlogged_table | on | Enables unlogged table check. memory_cache_enabled | off | Enables the memory cache functionality. write_function_list | | list of functions that writes to database. log_client_messages | off | Logs any client messages in the pgpool logs. wd_lifecheck_dbname | template1 | Database name to be used for by watchdog lifecheck. log_error_verbosity | verbose | How much details about error should be emitted. client_min_messages | notice | Which messages should be sent to client. wd_lifecheck_method | heartbeat | method for watchdog lifecheck. memqcache_maxcache | 400kB | Maximum SELECT result size in bytes. ssl_dh_params_file | | Path to the Diffie-Hellman parameters contained file allow_sql_comments | off | Ignore SQL comments, while judging if load balance or query cache is possible. min_spare_children | 5 | Minimum number of spare child processes. max_spare_children | 10 | Maximum number of spare child processes. wd_lifecheck_query | SELECT 1 | SQL query to be used by watchdog lifecheck. log_disconnections | on | Logs end of a session. client_idle_limit | 0 | idle time in seconds to disconnects a client. load_balance_mode | on | Enables load balancing of queries. health_check_test | off | If on, enable health check testing. logging_collector | on | Enable capturing of stderr into log files. num_init_children | 100 | Maximim number of child processs to handle client connections. log_standby_delay | if_over_threshold | When to log standby delay. unix_socket_group | | The owning user of the sockets that always starts the server. wd_ipc_socket_dir | /tmp | The directory to create the UNIX domain socket for accepting pgpool-II watchdog IPC connect ions. wd_lifecheck_user | nobody | User name to be used for by watchdog lifecheck. sr_check_password | ***** | The password for user to perform streaming replication delay check. sr_check_database | postgres | The database name to perform streaming replication delay check. log_rotation_size | 10MB | Automatic rotation of logfiles will happen after that much (kilobytes) log output. recovery_password | ***** | Password for online recovery. failback_command | | Command to execute when backend node is attached. serialize_accept | off | whether to serialize accept() call to avoid thundering herd problem failover_command | /etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S | Command to execute when backend node is detached. log_min_messages | warning | Which messages should be emitted to server log. recovery_timeout | 90s | Maximum time in seconds to wait for the recovering PostgreSQL node. replicate_select | off | Replicate SELECT statements when load balancing is disabled. memqcache_method | shmem | Cache store method. either shmem(shared memory) or Memcached. shmem by default. log_rotation_age | 1d | Automatic rotation of logfiles will happen after that (minutes) time. memqcache_oiddir | /var/log/pgpool/oiddir | Temporary directory to record table oids. check_temp_table | catalog | Enables temporary table check. reset_query_list | ABORT; DISCARD ALL | list of commands sent to reset the backend connection when user session exits. listen_addresses | * | hostname(s) or IP address(es) on which pgpool will listen on. replication_mode | off | Enables replication mode. connection_cache | on | Caches connections to backends. memqcache_expire | 0 | Memory cache entry life time specified in seconds. child_life_time | 2min | pgpool-II child process life time in seconds. trusted_servers | | List of servers to verify connectivity. log_connections | on | Logs each successful connection. sr_check_period | 5s | Time interval in seconds between the streaming replication delay checks. syslog_facility | LOCAL0 | syslog local facility. delay_threshold | 0 | standby delay threshold in bytes. relcache_expire | 0 | Relation cache expiration time in seconds. log_destination | stderr | destination of pgpool-II log ssl_ca_cert_dir | | Directory containing CA root certificate(s). log_line_prefix | %t: pid %p: | printf-style string to output at beginning of each log line. lobj_lock_table | | Table name used for large object replication control. enable_pool_hba | on | Use pool_hba.conf for client authentication. ssl_ecdh_curve | prime256v1 | The curve to use in ECDH key exchange. pcp_socket_dir | /run/pgpool | The directory to create the UNIX domain socket for accepting pgpool-II PCP connections. log_file_mode | 384 | creation mode for log files. wd_life_point | 3 | Maximum number of retries before failing the life check. pid_file_name | /run/pgpool/pgpool.pid | Path to store pgpool-II pid file. recovery_user | postgres | User name for online recovery. log_statement | off | Logs all statements in the pgpool logs. sr_check_user | usr_pg_pool | The User name to perform streaming replication delay check. log_directory | /var/log/pgpool-II | directory where log files are written. auto_failback | off | Enables nodes automatically reattach, when detached node continue streaming replication. relcache_size | 256 | Number of relation cache entry. ssl_crl_file | | SSL certificate revocation list file use_watchdog | on | Enables the pgpool-II watchdog. syslog_ident | pgpool | syslog program ident string. log_filename | pgpool-%Y-%m-%d_%H%M%S.log | log file name pattern. log_hostname | off | Logs the host name in the connection logs. wd_interval | 10s | Time interval in seconds between life check. insert_lock | on | Automatically locks table with INSERT to keep SERIAL data consistency pool_passwd | ***** | File name of pool_passwd for md5 authentication. ssl_ca_cert | | Single PEM format file containing CA root certificate(s). delegate_ip | 10.23.18.118 | Delegate IP address to be used when pgpool node become a watchdog cluster leader. ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL | Allowed SSL ciphers. if_cmd_path | /usr/sbin/ | Path to interface command. if_down_cmd | /usr/bin/sudo /usr/sbin/ip addr del $_IP_$/24 dev ens192 | Complete command to bring down virtual interface. arping_path | /usr/sbin | path to arping command. wd_priority | 7 | Watchdog node priority for leader election. wd_authkey | | Authentication key to be used in watchdog communication. arping_cmd | /usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I ens192 | arping command. if_up_cmd | /usr/bin/sudo /usr/sbin/ip addr add $_IP_$/24 dev ens192 label ens192:0 | Complete command to bring UP virtual interface. ping_path | /bin | path to ping command. ssl_cert | | SSL public certificate file. pcp_port | 9898 | tcp/IP port number on which pgpool PCP process will listen on. max_pool | 2 | Maximum number of connection pools per child process. ssl_key | | SSL private key file. logdir | /var/log/pgpool-II/pgpool-status | PgPool status file logging directory. port | 9999 | tcp/IP port number on which pgpool will listen on. ssl | off | Enables SSL support for frontend and backend connections (208 rows) [TST][paqcxast01].root:~ # ^C [TST][paqcxast01].root:~ # poweroff -ff Powering off.