<div dir="ltr">Hi Tatsuo, thanks you for your interest<div><br></div><div>The node 1 hostname is 192.168.1.162 but this do not match<br>> backend_hostname1 = '192.168.0.162'<br><br>Which one is correct? The correct one is 192.168.0.162</div><div><br></div>[postgres@buda ~]$ psql -p 9999 -U postgres<br>psql (17.2)<br>Digite «help» para obtener ayuda.<br><br>postgres=# show pool_nodes;<br> node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_<br>delay | replication_state | replication_sync_state | last_status_change <br>---------+---------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------<br>------+-------------------+------------------------+---------------------<br> 0 | localhost | 5434 | up | up | 0.000000 | primary | primary | 0 | true | 0 <br> | | | 2025-02-10 21:44:21<br> 1 | 192.168.0.162 | 5434 | down | up | 1.000000 | standby | standby | 0 | false | 0 <br> | | | 2025-02-10 21:44:21<br>(2 filas)<br><div><br></div>postgres=# SHOW POOL_HEALTH_CHECK_STATS;<br> node_id | hostname | port | status | role | last_status_change | total_count | success_count | fail_count | skip_count | r<br>etry_count | average_retry_count | max_retry_count | max_duration | min_duration | average_duration | last_health_check | last_suc<br>cessful_health_check | last_skip_health_check | last_failed_health_check <br>---------+---------------+------+--------+---------+---------------------+-------------+---------------+------------+------------+--<br>-----------+---------------------+-----------------+--------------+--------------+------------------+---------------------+---------<br>---------------------+------------------------+--------------------------<br> 0 | localhost | 5434 | up | primary | 2025-02-10 21:44:21 | 69 | 69 | 0 | 0 | 0<br> | 0.000000 | 0 | 16 | 2 | 5.260870 | 2025-02-10 21:55:42 | 2025-02-<br>10 21:55:42 | | <br> 1 | 192.168.0.162 | 5434 | down | standby | 2025-02-10 21:44:21 | 0 | 0 | 0 | 0 | 0<br> | -nan | 0 | 0 | 2147483647 | -nan | | <br> | | <br>(2 filas)<br><br><div>kind regards</div><div><br></div><div>Mauricio Fernández </div></div><br><div class="gmail_quote gmail_quote_container"><div dir="ltr" class="gmail_attr">El lun, 10 feb 2025 a las 21:26, Tatsuo Ishii (<<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>>) escribió:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">> Hi admins, I'm tryng to seting up pgpool-II as a load balancer. The<br>
> environment are two VMs with Oracle Linux 9.5 and Postgresql 17.1 listenig<br>
> both in port 5434.<br>
> <br>
> I've configured streaming replication and is working fine. The user used<br>
> for replication is replicador/some_pwd<br>
> <br>
> Primary Server (PS) IP: 192.168.0.160<br>
> Stand by Server(SS) IP: 192.168.0.162<br>
> <br>
> Located in PS I can connect to de stand by server with no problem:<br>
> [postgres@buda pgpool-II]$ psql -h 192.168.0.162 -p 5434 -U postgres<br>
> Contraseña para usuario postgres:<br>
> psql (17.2)<br>
> Digite «help» para obtener ayuda.<br>
> <br>
> postgres=#<br>
> <br>
> pgpool-II ( 4.5.5) is running in PS and for some how can't reach stand by<br>
> server, and logging says nothing useful<br>
> <br>
> [postgres@buda pgpool-II]$ psql -p 9999 -U postgres<br>
> psql (17.2)<br>
> Digite «help» para obtener ayuda.<br>
> <br>
> postgres=# show pool_version;<br>
> pool_version<br>
> ----------------------<br>
> 4.5.5 (hotooriboshi)<br>
> (1 fila)<br>
> <br>
> postgres=# show pool_nodes;<br>
> node_id | hostname | port | status | pg_status | lb_weight | role<br>
> | pg_role | select_cnt | load_balance_node | replication_<br>
> delay | replication_state | replication_sync_state | last_status_change<br>
> ---------+---------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------<br>
> ------+-------------------+------------------------+---------------------<br>
> 0 | localhost | 5434 | up | up | 0.000000 | primary<br>
> | primary | 0 | true | 0<br>
> | | | 2025-02-10 08:02:44<br>
> 1 | 192.168.1.162 | 5434 | down | down | 1.000000 | standby<br>
> | unknown | 0 | false | 0<br>
> | | | 2025-02-10 08:02:44<br>
> (2 filas)<br>
<br>
The node 1 hostname is 192.168.1.162 but this do not match <br>
> backend_hostname1 = '192.168.0.162'<br>
<br>
Which one is correct?<br>
<br>
> Some relevant parameters in pgpool.conf are:<br>
> <br>
> backend_clustering_mode = 'streaming_replication'<br>
> listen_addresses = '*'<br>
> port = 9999<br>
> unix_socket_directories = '/var/run/postgresql'<br>
> <br>
> backend_hostname0 = 'localhost' # hostname0 = SERVIDOR PRIMARIO<br>
> backend_port0 = 5434 # Port number for backend 0<br>
> backend_weight0 = 0 # Port number for backend 0<br>
> backend_data_directory0 = '/var/lib/pgsql/17/data/primario'<br>
> <br>
> #enable_pool_hba = off<br>
> <br>
> backend_hostname1 = '192.168.0.162'<br>
> backend_port1 = 5434<br>
> backend_weight1 = 1<br>
> backend_data_directory1 = '/var/lib/pgsql/17/data/replica'<br>
> <br>
> log_destination = 'stderr'<br>
> log_statement = on<br>
> log_per_node_statement = on<br>
> <br>
> load_balance_mode = on<br>
> sr_check_period = 10<br>
> sr_check_user = 'postgres'<br>
> sr_check_password = 'some_pwd'<br>
> sr_check_database = 'postgres'<br>
> <br>
> I execute pgpool in this way until I can sucessfully reach my objetives<br>
> <br>
> [postgres@buda pgpool-II]$ pgpool -n &<br>
> [1] 3405<br>
> [postgres@buda pgpool-II]$ 2025-02-10 08:49:23.715: main pid 3405: LOG:<br>
> reading status file: 0 th backend is set to up status<br>
> 2025-02-10 08:49:23.715: main pid 3405: LOG: reading status file: 1 th<br>
> backend is set to down status<br>
> 2025-02-10 08:49:23.716: main pid 3405: LOG: redirecting log output to<br>
> logging collector process<br>
> 2025-02-10 08:49:23.716: main pid 3405: HINT: Future log output will<br>
> appear in directory "/var/log/pgpool_log".<br>
> <br>
> and the log file the content is<br>
> <br>
> 2025-02-10 08:02:44.684: main pid 2414: LOG:<br>
> health_check_stats_shared_memory_size: requested size: 12288<br>
> 2025-02-10 08:02:44.684: main pid 2414: LOG: memory cache initialized<br>
> 2025-02-10 08:02:44.684: main pid 2414: DETAIL: memcache blocks :64<br>
> 2025-02-10 08:02:44.684: main pid 2414: LOG: allocating (136981824) bytes<br>
> of shared memory segment<br>
> 2025-02-10 08:02:44.684: main pid 2414: LOG: allocating shared memory<br>
> segment of size: 136981824<br>
> 2025-02-10 08:02:44.966: main pid 2414: LOG:<br>
> health_check_stats_shared_memory_size: requested size: 12288<br>
> 2025-02-10 08:02:44.966: main pid 2414: LOG:<br>
> health_check_stats_shared_memory_size: requested size: 12288<br>
> 2025-02-10 08:02:44.966: main pid 2414: LOG: memory cache initialized<br>
> 2025-02-10 08:02:44.966: main pid 2414: DETAIL: memcache blocks :64<br>
> 2025-02-10 08:02:44.968: main pid 2414: LOG: pool_discard_oid_maps:<br>
> discarded memqcache oid maps<br>
> 2025-02-10 08:02:44.978: main pid 2414: LOG: create socket files[0]:<br>
> /var/run/postgresql/.s.PGSQL.9999<br>
> 2025-02-10 08:02:44.978: main pid 2414: LOG: listen address[0]: *<br>
> 2025-02-10 08:02:44.979: main pid 2414: LOG: Setting up socket for<br>
> <a href="http://0.0.0.0:9999" rel="noreferrer" target="_blank">0.0.0.0:9999</a><br>
> 2025-02-10 08:02:44.979: main pid 2414: LOG: Setting up socket for :::9999<br>
> 2025-02-10 08:02:44.984: main pid 2414: LOG: find_primary_node_repeatedly:<br>
> waiting for finding a primary node<br>
> 2025-02-10 08:02:44.999: main pid 2414: LOG: find_primary_node: primary<br>
> node is 0<br>
> 2025-02-10 08:02:45.000: main pid 2414: LOG: create socket files[0]:<br>
> /var/run/postgresql/.s.PGSQL.9898<br>
> 2025-02-10 08:02:45.000: main pid 2414: LOG: listen address[0]: localhost<br>
> 2025-02-10 08:02:45.000: main pid 2414: LOG: Setting up socket for ::1:9898<br>
> 2025-02-10 08:02:45.000: main pid 2414: LOG: Setting up socket for<br>
> <a href="http://127.0.0.1:9898" rel="noreferrer" target="_blank">127.0.0.1:9898</a><br>
> 2025-02-10 08:02:45.001: health_check pid 2452: LOG: process started<br>
> 2025-02-10 08:02:45.002: pcp_main pid 2450: LOG: PCP process: 2450 started<br>
> 2025-02-10 08:02:45.002: main pid 2414: LOG: pgpool-II successfully<br>
> started. version 4.5.5 (hotooriboshi)<br>
> 2025-02-10 08:02:45.002: main pid 2414: LOG: node status[0]: 1<br>
> 2025-02-10 08:02:45.002: main pid 2414: LOG: node status[1]: 0<br>
> 2025-02-10 08:02:45.002: sr_check_worker pid 2451: LOG: process started<br>
> 2025-02-10 08:17:15.007: psql pid 2424: LOG: statement: show version<br>
> ;<br>
> 2025-02-10 08:17:15.009: psql pid 2424: LOG: DB node id: 0 backend pid:<br>
> 2711 statement: SELECT pg_catalog.version()<br>
> 2<br>
> <br>
> Is extraign there is not a word about trying connect to stand by server.<br>
> <br>
> I really would apreciate any tips<br>
> <br>
> Kind regards<br>
> <br>
> Mauricio Fernández<br>
</blockquote></div>