[pgpool-general: 9424] Re: pcp_commands failing

[Sbob]

I have found that the find_primary_node times out after 5min and then 
the pcp commands work


On 4/23/25 10:45 AM, Sbob wrote:
> All;
>
> I have setup pgpool as a read only locd balancer, I have 4 postgreSQL 
> nodes replicating via streaming replication, but I only configured the 
> 3 standby nodes in the pgpool.conf file. I also configured my pcp.conf 
> file like this:
>
> $ pg_md5 postgres
> e8a48653851e28c69d0506508fb27fc5
>
> then I added the username:hash line in my pcp.conf file
>
>
> $ tail /etc/pgpool-II/pcp.conf
> # Be aware that there will be no spaces or tabs at the
> # beginning of the line! although the above example looks
> # like so.
> #
> # Lines beginning with '#' (pound) are comments and will
> # be ignored. Again, no spaces or tabs allowed before '#'.
>
> # USERID:MD5PASSWD
> postgres:e8a48653851e28c69d0506508fb27fc5
>
>
> once I start pgpool I can run a show pool_nodes:
>
> $ psql -h localhost
> psql (14.17)
> Type "help" for help.
>
> postgres=# show pool_nodes;
>  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.122.6   | 5432 | up     | up        | 0.333333 | 
> standby | standby | 0          | false             | 0                 
> |                   |                        | 2025-04-23 10:07:06
>  1       | 192.168.122.81  | 5432 | up     | up        | 0.333333 | 
> standby | standby | 0          | false             | 0                 
> |                   |                        | 2025-04-23 10:07:06
>  2       | 192.168.122.217 | 5432 | up     | up        | 0.333333 | 
> standby | standby | 0          | true              | 0                 
> |                   |                        | 2025-04-23 10:07:06
> (3 rows)
>
>
> Next I run a pcp_node_count:
>
> $ pcp_node_count -U postgres -h localhost -W
> Password:
> ERROR: connection to host "localhost" failed with error "Connection 
> refused"
>
>
>
> or a pcp_pool_status without the -h localhost:
>
> $ pcp_pool_status -U postgres  -W
> Password:
> ERROR: connection to socket "/tmp/.s.PGSQL.9898" failed with error "No 
> such file or directory"
>
> it seems odd that its trying to connect to a socket in /tmp even 
> though I have :
> pcp_socket_dir = '/run/pcp'
>
>
> the /run/pcp directory is owned by postgres
>
>
> I get the same behavior if I start pfpool with :
>
> -F /etc/pgpool-II/pcp.conf
>
>
> I am running everything as the postgres user and the postgres user 
> owns the pcp.conf file:
> total 164
> -rw-r--r--. 1 root     root      1543 Mar  3 04:26 
> aws_eip_if_cmd.sh.sample
> -rw-r--r--. 1 root     root      2554 Mar  3 04:26 
> aws_rtb_if_cmd.sh.sample
> -rw-r--r--. 1 root     root      1044 Mar  3 04:26 escalation.sh.sample
> -rw-r--r--. 1 root     root      2793 Mar  3 04:26 failover.sh.sample
> -rw-r--r--. 1 root     root      4644 Mar  3 04:26 
> follow_primary.sh.sample
> -rw-r--r--. 1 postgres postgres   941 Apr 23 09:59 pcp.conf
> -rw-r--r--. 1 root     root       858 Mar  3 04:26 pcp.conf.sample
> -rw-r--r--. 1 root     root     53278 Apr 23 09:57 pgpool.conf
> -rw-r--r--. 1 root     root     53410 Mar  3 04:26 pgpool.conf.sample
> -rw-r--r--. 1 root     root      1183 Mar  3 04:26 
> pgpool_remote_start.sample
> -rw-r--r--. 1 root     root      3476 Mar  3 04:26 pool_hba.conf.sample
> -rw-r--r--. 1 root     root      3187 Mar  3 04:26 
> recovery_1st_stage.sample
> -rw-r--r--. 1 root     root      3647 Mar  3 04:26 
> replication_mode_recovery_1st_stage.sample
> -rw-r--r--. 1 root     root      1851 Mar  3 04:26 
> replication_mode_recovery_2nd_stage.sample
>
>
> here are my non default pgpool.conf settings:
>
> backend_clustering_mode = 'streaming_replication'
> listen_addresses = '*'
> port = 5432
> unix_socket_directories = '/run/pgpool-II'
> pcp_listen_addresses = '*'
> pcp_socket_dir = '/run/pcp'
>
> backend_hostname0 = '192.168.122.6'
> backend_port0 = 5432
> backend_weight0 = 1
> backend_data_directory0 = '/var/lib/pgsql/14/data'
> backend_flag0 = 'DISALLOW_TO_FAILOVER'
> backend_application_name0 = 'db2'
>
> backend_hostname1 = '192.168.122.81'
> backend_port1 = 5432
> backend_weight1 = 1
> backend_data_directory1 = '/var/lib/pgsql/14/data'
> backend_flag1 = 'DISALLOW_TO_FAILOVER'
> backend_application_name1 = 'db3'
>
> backend_hostname2 = '192.168.122.217'
> backend_port2 = 5432
> backend_weight2 = 1
> backend_data_directory2 = '/var/lib/pgsql/14/data'
> backend_flag2 = 'DISALLOW_TO_FAILOVER'
> backend_application_name2 = 'db4'
>
> enable_pool_hba = off
> pool_passwd = ''
> allow_clear_text_frontend_auth = on
> num_init_children = 64
> max_pool = 15
> log_destination = 'stderr'
> log_line_prefix = '%m: %a pid %p: '
> log_connections = off
> log_disconnections = off
> #log_pcp_processes = on
> log_hostname = off
> log_statement = off
> logging_collector = on
> log_directory = '/var/lib/pgsql/pgpool/log'
> log_filename = 'pgpool-%d.log'
> log_truncate_on_rotation = on
> log_rotation_size = 0
> pid_file_name = '/var/lib/pgsql/pgpool/pid/pgpool.pid'
> logdir = '/var/lib/pgsql/pgpool/log'
>
> load_balance_mode = on
> ignore_leading_white_space = on
> statement_level_load_balance = on
> sr_check_period = 10
> sr_check_user = 'postgres'
> sr_check_database = 'postgres'
> health_check_period = 5
> health_check_timeout = 20
> health_check_user = 'postgres'
> health_check_password = ''
> failover_command = '/var/lib/pgsql/pgpool/bin/failover.sh %h %H %N > 
> /var/lib/pgsql/pgpool/log/failover.log 2>&1'
> failover_on_backend_shutdown = on
>
>
>
>
> Thanks in advance
>
>
>
>
>