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

[Mauricio Fernandez]

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)

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20250210/bd72c2d4/attachment.htm>