<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi everyone;</p>
<p><br>
</p>
<p>I am trying to setup a simple pgpool test.</p>
<p><br>
</p>
<p>I created 3 VM's and installed PostgreSQL14 on 2 of them, and
then set them us as a primary/standby via streaming replication.</p>
<p>I disabled firewalld (Fedora 37 servers) on all 3 VM's</p>
<p>I install the following on the pgpool node via dnf :<br>
pgpool-II-4.4.2-1.f37.x86_64<br>
pgpool-II-pcp-4.4.2-1.f37.x86_64</p>
<p><br>
</p>
<p> I made a copy of the /etc/pgpool.conf.sample file to
/etc/pgpool.conf and made the following edits:<br>
</p>
<p><font color="#613583">backend_clustering_mode =
'streaming_replication'<br>
#------------------------------------------------------------------------------<br>
# CONNECTIONS<br>
#------------------------------------------------------------------------------<br>
<br>
# - pgpool Connection Settings - <br>
<br>
listen_addresses = '*' <br>
# what host name(s) or IP
address(es) to listen on;<br>
# comma-separated list of
addresses;<br>
# defaults to 'localhost';
use '*' for all<br>
# (change requires restart)<br>
port = 5432<br>
# Port number<br>
# (change requires restart)<br>
unix_socket_directories = '/var/run/postgresql/'<br>
<br>
# - Backend Connection Settings -<br>
<br>
backend_hostname0 = '192.168.67.136'<br>
# Host name or IP address to
connect to for backend 0<br>
backend_port0 = 5432<br>
# Port number for backend 0<br>
backend_weight0 = 1<br>
# Weight for backend 0 (only
in load balancing mode)<br>
backend_data_directory0 = '/var/lib/pgsql/14/data'<br>
# Data directory for backend
0<br>
backend_flag0 = 'DISALLOW_TO_FAILOVER'<br>
# Controls various backend
behavior<br>
# ALLOW_TO_FAILOVER,
DISALLOW_TO_FAILOVER<br>
# or ALWAYS_PRIMARY<br>
backend_application_name0 = 'server0'<br>
# walsender's
application_name, used for "show pool_nodes" command<br>
backend_hostname1 = '192.168.67.137'<br>
backend_port1 = 5432<br>
backend_weight1 = 1<br>
backend_data_directory1 = '/var/lib/pgsql/14/data1'<br>
backend_flag1 = 'DISALLOW_TO_FAILOVER'<br>
backend_application_name1 = 'server1<br>
<br>
pool_passwd = ''<br>
<br>
#------------------------------------------------------------------------------<br>
# POOLS<br>
#------------------------------------------------------------------------------<br>
<br>
# - Concurrent session and pool size -<br>
#process_management_mode = static<br>
#process_management_strategy = gentle<br>
num_init_children = 32<br>
min_spare_children = 5<br>
max_spare_children = 10<br>
max_pool = 4</font></p>
<p><br>
</p>
<p>I also setup pg_hba.conf entries on the 2 PostgreSQL nodes
(Primary and standby) so the pgpool VM can connect without a
password.</p>
<p><br>
</p>
<p>When I start pgpool I get this:</p>
<p><font color="#613583">$ pgpool -D -n <br>
2023-03-29 22:00:59.160: main pid 2474257: LOG: Backend status
file /tmp/pgpool_status discarded<br>
2023-03-29 22:00:59.160: main pid 2474257: LOG:
health_check_stats_shared_memory_size: requested size: 12288<br>
2023-03-29 22:00:59.160: main pid 2474257: LOG: memory cache
initialized<br>
2023-03-29 22:00:59.160: main pid 2474257: DETAIL: memcache
blocks :64<br>
2023-03-29 22:00:59.160: main pid 2474257: LOG: allocating
(136981824) bytes of shared memory segment<br>
2023-03-29 22:00:59.160: main pid 2474257: LOG: allocating
shared memory segment of size: 136981824 <br>
2023-03-29 22:00:59.212: main pid 2474257: LOG:
health_check_stats_shared_memory_size: requested size: 12288<br>
2023-03-29 22:00:59.212: main pid 2474257: LOG:
health_check_stats_shared_memory_size: requested size: 12288<br>
2023-03-29 22:00:59.213: main pid 2474257: LOG: memory cache
initialized<br>
2023-03-29 22:00:59.213: main pid 2474257: DETAIL: memcache
blocks :64<br>
2023-03-29 22:00:59.214: main pid 2474257: LOG:
pool_discard_oid_maps: discarded memqcache oid maps<br>
2023-03-29 22:00:59.217: main pid 2474257: LOG:
unix_socket_directories[0]: /var/run/postgresql//.s.PGSQL.5432<br>
2023-03-29 22:00:59.218: main pid 2474257: LOG: listen
address[0]: *<br>
2023-03-29 22:00:59.218: main pid 2474257: LOG: Setting up
socket for 0.0.0.0:5432<br>
2023-03-29 22:00:59.218: main pid 2474257: LOG: Setting up
socket for :::5432<br>
2023-03-29 22:00:59.226: main pid 2474257: LOG:
find_primary_node_repeatedly: waiting for finding a primary node<br>
2023-03-29 22:00:59.230: main pid 2474257: LOG:
find_primary_node: make_persistent_db_connection_noerror failed
on node 0<br>
2023-03-29 22:00:59.233: main pid 2474257: LOG:
find_primary_node: make_persistent_db_connection_noerror failed
on node 1<br>
2023-03-29 22:01:00.246: main pid 2474257: LOG:
find_primary_node: make_persistent_db_connection_noerror failed
on node 0<br>
2023-03-29 22:01:00.252: main pid 2474257: LOG:
find_primary_node: make_persistent_db_connection_noerror failed
on node 1<br>
2023-03-29 22:01:01.256: main pid 2474257: LOG:
find_primary_node: make_persistent_db_connection_noerror failed
on node 0<br>
2023-03-29 22:01:01.259: main pid 2474257: LOG:
find_primary_node: make_persistent_db_connection_noerror failed
on node 1<br>
2023-03-29 22:01:02.270: main pid 2474257: LOG:
find_primary_node: make_persistent_db_connection_noerror failed
on node 0<br>
2023-03-29 22:01:02.276: main pid 2474257: LOG:
find_primary_node: make_persistent_db_connection_noerror failed
on node 1<br>
2023-03-29 22:01:03.285: main pid 2474257: LOG:
find_primary_node: make_persistent_db_connection_noerror failed
on node 0<br>
2023-03-29 22:01:03.293: main pid 2474257: LOG:
find_primary_node: make_persistent_db_connection_noerror failed
on node 1</font><br>
</p>
<p><br>
</p>
<p>However I can connect to PostgreSQL when I am connected via ssh
to the pgpool VM:</p>
<p><font color="#613583">$ psql <br>
psql (15.2, server 14.7)<br>
Type "help" for help.<br>
<br>
postgres=# \d<br>
List of relations<br>
Schema | Name | Type | Owner <br>
--------+------+-------+----------<br>
public | t2 | table | postgres<br>
public | t3 | table | postgres<br>
(2 rows)<br>
<br>
postgres=# insert into t2 values (1);<br>
INSERT 0 1</font><br>
</p>
<p>Questions:</p>
<p><br>
</p>
<p>1) I dont understand the "find_primary_node:
make_persistent_db_connection_noerror failed on node 1" errors
above</p>
<p>2) How do I test/prove load balancing?</p>
<p><br>
</p>
<p>Thanks in advance<br>
</p>
<p><br>
<br>
</p>
<p><br>
</p>
<p><br>
</p>
<p><br>
</p>
<p><br>
</p>
</body>
</html>