[pgpool-general: 9345] Re: pgpool 4.5.5 can't reach stand by server
Mauricio Fernandez
mmauricio.fernandez at gmail.com
Tue Feb 11 10:06:09 JST 2025
Hi Tatsuo, thanks you for your interest
The node 1 hostname is 192.168.1.162 but this do not match
> backend_hostname1 = '192.168.0.162'
Which one is correct? The correct one is 192.168.0.162
[postgres at buda ~]$ psql -p 9999 -U postgres
psql (17.2)
Digite «help» para obtener ayuda.
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 21:44:21
1 | 192.168.0.162 | 5434 | down | up | 1.000000 | standby
| standby | 0 | false | 0
| | | 2025-02-10 21:44:21
(2 filas)
postgres=# SHOW POOL_HEALTH_CHECK_STATS;
node_id | hostname | port | status | role | last_status_change |
total_count | success_count | fail_count | skip_count | r
etry_count | average_retry_count | max_retry_count | max_duration |
min_duration | average_duration | last_health_check | last_suc
cessful_health_check | last_skip_health_check | last_failed_health_check
---------+---------------+------+--------+---------+---------------------+-------------+---------------+------------+------------+--
-----------+---------------------+-----------------+--------------+--------------+------------------+---------------------+---------
---------------------+------------------------+--------------------------
0 | localhost | 5434 | up | primary | 2025-02-10 21:44:21 |
69 | 69 | 0 | 0 | 0
| 0.000000 | 0 | 16 | 2
| 5.260870 | 2025-02-10 21:55:42 | 2025-02-
10 21:55:42 | |
1 | 192.168.0.162 | 5434 | down | standby | 2025-02-10 21:44:21 |
0 | 0 | 0 | 0 | 0
| -nan | 0 | 0 |
2147483647 | -nan | |
| |
(2 filas)
kind regards
Mauricio Fernández
El lun, 10 feb 2025 a las 21:26, Tatsuo Ishii (<ishii at postgresql.org>)
escribió:
> > 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20250210/90341019/attachment-0001.htm>
More information about the pgpool-general
mailing list