[pgpool-general: 9343] Re: pgpool 4.5.5 can't reach stand by server

[Tatsuo Ishii]

> Hi admins, I'm tryng to seting up pgpool-II as a load balancer. The
> environment are two VMs with Oracle Linux 9.5 and Postgresql 17.1 listenig
> both in port 5434.
> 
> I've configured streaming replication and is working fine. The user used
> for replication is replicador/some_pwd
> 
> Primary Server (PS) IP: 192.168.0.160
> Stand by Server(SS) IP: 192.168.0.162
> 
> Located in PS I can connect to de stand by server with no problem:
> [postgres at buda pgpool-II]$ psql -h 192.168.0.162 -p 5434 -U postgres
> Contraseña para usuario postgres:
> psql (17.2)
> Digite «help» para obtener ayuda.
> 
> postgres=#
> 
> pgpool-II ( 4.5.5) is running in PS and for some how can't reach stand by
> server, and logging says nothing useful
> 
> [postgres at buda pgpool-II]$ psql -p 9999 -U postgres
> psql (17.2)
> Digite «help» para obtener ayuda.
> 
> postgres=# show pool_version;
>      pool_version
> ----------------------
>  4.5.5 (hotooriboshi)
> (1 fila)
> 
> 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       | localhost     | 5434 | up     | up        | 0.000000  | primary
> | primary | 0          | true              | 0
>       |                   |                        | 2025-02-10 08:02:44
>  1       | 192.168.1.162 | 5434 | down   | down      | 1.000000  | standby
> | unknown | 0          | false             | 0
>       |                   |                        | 2025-02-10 08:02:44
> (2 filas)

The node 1 hostname is 192.168.1.162 but this do not match 
> backend_hostname1 = '192.168.0.162'

Which one is correct?

> Some relevant parameters  in pgpool.conf are:
> 
> backend_clustering_mode = 'streaming_replication'
> listen_addresses = '*'
> port = 9999
> unix_socket_directories = '/var/run/postgresql'
> 
> backend_hostname0 = 'localhost'  # hostname0 = SERVIDOR PRIMARIO
> backend_port0 = 5434               # Port number for backend 0
> backend_weight0 = 0               # Port number for backend 0
> backend_data_directory0 = '/var/lib/pgsql/17/data/primario'
> 
> #enable_pool_hba = off
> 
> backend_hostname1 = '192.168.0.162'
> backend_port1 = 5434
> backend_weight1 = 1
> backend_data_directory1 = '/var/lib/pgsql/17/data/replica'
> 
> log_destination = 'stderr'
> log_statement = on
> log_per_node_statement = on
> 
> load_balance_mode = on
> sr_check_period = 10
> sr_check_user = 'postgres'
> sr_check_password = 'some_pwd'
> sr_check_database = 'postgres'
> 
> I execute pgpool in this way until I can sucessfully reach my objetives
> 
> [postgres at buda pgpool-II]$ pgpool -n &
> [1] 3405
> [postgres at buda pgpool-II]$ 2025-02-10 08:49:23.715: main pid 3405: LOG:
>  reading status file: 0 th backend is set to up status
> 2025-02-10 08:49:23.715: main pid 3405: LOG:  reading status file: 1 th
> backend is set to down status
> 2025-02-10 08:49:23.716: main pid 3405: LOG:  redirecting log output to
> logging collector process
> 2025-02-10 08:49:23.716: main pid 3405: HINT:  Future log output will
> appear in directory "/var/log/pgpool_log".
> 
> and the log file the content is
> 
> 2025-02-10 08:02:44.684: main pid 2414: LOG:
>  health_check_stats_shared_memory_size: requested size: 12288
> 2025-02-10 08:02:44.684: main pid 2414: LOG:  memory cache initialized
> 2025-02-10 08:02:44.684: main pid 2414: DETAIL:  memcache blocks :64
> 2025-02-10 08:02:44.684: main pid 2414: LOG:  allocating (136981824) bytes
> of shared memory segment
> 2025-02-10 08:02:44.684: main pid 2414: LOG:  allocating shared memory
> segment of size: 136981824
> 2025-02-10 08:02:44.966: main pid 2414: LOG:
>  health_check_stats_shared_memory_size: requested size: 12288
> 2025-02-10 08:02:44.966: main pid 2414: LOG:
>  health_check_stats_shared_memory_size: requested size: 12288
> 2025-02-10 08:02:44.966: main pid 2414: LOG:  memory cache initialized
> 2025-02-10 08:02:44.966: main pid 2414: DETAIL:  memcache blocks :64
> 2025-02-10 08:02:44.968: main pid 2414: LOG:  pool_discard_oid_maps:
> discarded memqcache oid maps
> 2025-02-10 08:02:44.978: main pid 2414: LOG:  create socket files[0]:
> /var/run/postgresql/.s.PGSQL.9999
> 2025-02-10 08:02:44.978: main pid 2414: LOG:  listen address[0]: *
> 2025-02-10 08:02:44.979: main pid 2414: LOG:  Setting up socket for
> 0.0.0.0:9999
> 2025-02-10 08:02:44.979: main pid 2414: LOG:  Setting up socket for :::9999
> 2025-02-10 08:02:44.984: main pid 2414: LOG:  find_primary_node_repeatedly:
> waiting for finding a primary node
> 2025-02-10 08:02:44.999: main pid 2414: LOG:  find_primary_node: primary
> node is 0
> 2025-02-10 08:02:45.000: main pid 2414: LOG:  create socket files[0]:
> /var/run/postgresql/.s.PGSQL.9898
> 2025-02-10 08:02:45.000: main pid 2414: LOG:  listen address[0]: localhost
> 2025-02-10 08:02:45.000: main pid 2414: LOG:  Setting up socket for ::1:9898
> 2025-02-10 08:02:45.000: main pid 2414: LOG:  Setting up socket for
> 127.0.0.1:9898
> 2025-02-10 08:02:45.001: health_check pid 2452: LOG:  process started
> 2025-02-10 08:02:45.002: pcp_main pid 2450: LOG:  PCP process: 2450 started
> 2025-02-10 08:02:45.002: main pid 2414: LOG:  pgpool-II successfully
> started. version 4.5.5 (hotooriboshi)
> 2025-02-10 08:02:45.002: main pid 2414: LOG:  node status[0]: 1
> 2025-02-10 08:02:45.002: main pid 2414: LOG:  node status[1]: 0
> 2025-02-10 08:02:45.002: sr_check_worker pid 2451: LOG:  process started
> 2025-02-10 08:17:15.007: psql pid 2424: LOG:  statement: show version
>         ;
> 2025-02-10 08:17:15.009: psql pid 2424: LOG:  DB node id: 0 backend pid:
> 2711 statement: SELECT pg_catalog.version()
> 2
> 
> Is extraign there is not a word about trying connect to stand by server.
> 
> I really would apreciate any tips
> 
> Kind regards
> 
> Mauricio Fernández