[pgpool-general: 8697] pgpool setup issues

[Sbob]

Hi everyone;


I am trying to setup a simple pgpool test.


I created 3 VM's and installed PostgreSQL14 on 2 of them, and then set 
them us as a primary/standby via streaming replication.

I disabled firewalld (Fedora 37 servers)  on all 3 VM's

I install the following on the pgpool node via dnf :
pgpool-II-4.4.2-1.f37.x86_64
pgpool-II-pcp-4.4.2-1.f37.x86_64


  I made a copy of the /etc/pgpool.conf.sample file to /etc/pgpool.conf 
and made the following edits:

backend_clustering_mode = 'streaming_replication'
#------------------------------------------------------------------------------
# CONNECTIONS
#------------------------------------------------------------------------------

# - pgpool Connection Settings -

listen_addresses = '*'
                                    # what host name(s) or IP 
address(es) to listen on;
                                    # comma-separated list of addresses;
                                    # defaults to 'localhost'; use '*' 
for all
                                    # (change requires restart)
port = 5432
                                    # Port number
                                    # (change requires restart)
unix_socket_directories = '/var/run/postgresql/'

# - Backend Connection Settings -

backend_hostname0 = '192.168.67.136'
                                    # Host name or IP address to connect 
to for backend 0
backend_port0 = 5432
                                    # Port number for backend 0
backend_weight0 = 1
                                    # Weight for backend 0 (only in load 
balancing mode)
backend_data_directory0 = '/var/lib/pgsql/14/data'
                                    # Data directory for backend 0
backend_flag0 = 'DISALLOW_TO_FAILOVER'
                                    # Controls various backend behavior
                                    # ALLOW_TO_FAILOVER, 
DISALLOW_TO_FAILOVER
                                    # or ALWAYS_PRIMARY
backend_application_name0 = 'server0'
                                    # walsender's application_name, used 
for "show pool_nodes" command
backend_hostname1 = '192.168.67.137'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/14/data1'
backend_flag1 = 'DISALLOW_TO_FAILOVER'
backend_application_name1 = 'server1

pool_passwd = ''

#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------

# - Concurrent session and pool size -
#process_management_mode = static
#process_management_strategy = gentle
num_init_children = 32
min_spare_children = 5
max_spare_children = 10
max_pool = 4


I also setup pg_hba.conf entries on the 2 PostgreSQL nodes (Primary and 
standby) so the pgpool VM can connect without a password.


When I start pgpool I get this:

$ pgpool  -D -n
2023-03-29 22:00:59.160: main pid 2474257: LOG:  Backend status file 
/tmp/pgpool_status discarded
2023-03-29 22:00:59.160: main pid 2474257: LOG: 
health_check_stats_shared_memory_size: requested size: 12288
2023-03-29 22:00:59.160: main pid 2474257: LOG:  memory cache initialized
2023-03-29 22:00:59.160: main pid 2474257: DETAIL:  memcache blocks :64
2023-03-29 22:00:59.160: main pid 2474257: LOG:  allocating (136981824) 
bytes of shared memory segment
2023-03-29 22:00:59.160: main pid 2474257: LOG:  allocating shared 
memory segment of size: 136981824
2023-03-29 22:00:59.212: main pid 2474257: LOG: 
health_check_stats_shared_memory_size: requested size: 12288
2023-03-29 22:00:59.212: main pid 2474257: LOG: 
health_check_stats_shared_memory_size: requested size: 12288
2023-03-29 22:00:59.213: main pid 2474257: LOG:  memory cache initialized
2023-03-29 22:00:59.213: main pid 2474257: DETAIL:  memcache blocks :64
2023-03-29 22:00:59.214: main pid 2474257: LOG: pool_discard_oid_maps: 
discarded memqcache oid maps
2023-03-29 22:00:59.217: main pid 2474257: LOG: 
unix_socket_directories[0]: /var/run/postgresql//.s.PGSQL.5432
2023-03-29 22:00:59.218: main pid 2474257: LOG:  listen address[0]: *
2023-03-29 22:00:59.218: main pid 2474257: LOG:  Setting up socket for 
0.0.0.0:5432
2023-03-29 22:00:59.218: main pid 2474257: LOG:  Setting up socket for 
:::5432
2023-03-29 22:00:59.226: main pid 2474257: LOG: 
find_primary_node_repeatedly: waiting for finding a primary node
2023-03-29 22:00:59.230: main pid 2474257: LOG: find_primary_node: 
make_persistent_db_connection_noerror failed on node 0
2023-03-29 22:00:59.233: main pid 2474257: LOG: find_primary_node: 
make_persistent_db_connection_noerror failed on node 1
2023-03-29 22:01:00.246: main pid 2474257: LOG: find_primary_node: 
make_persistent_db_connection_noerror failed on node 0
2023-03-29 22:01:00.252: main pid 2474257: LOG: find_primary_node: 
make_persistent_db_connection_noerror failed on node 1
2023-03-29 22:01:01.256: main pid 2474257: LOG: find_primary_node: 
make_persistent_db_connection_noerror failed on node 0
2023-03-29 22:01:01.259: main pid 2474257: LOG: find_primary_node: 
make_persistent_db_connection_noerror failed on node 1
2023-03-29 22:01:02.270: main pid 2474257: LOG: find_primary_node: 
make_persistent_db_connection_noerror failed on node 0
2023-03-29 22:01:02.276: main pid 2474257: LOG: find_primary_node: 
make_persistent_db_connection_noerror failed on node 1
2023-03-29 22:01:03.285: main pid 2474257: LOG: find_primary_node: 
make_persistent_db_connection_noerror failed on node 0
2023-03-29 22:01:03.293: main pid 2474257: LOG: find_primary_node: 
make_persistent_db_connection_noerror failed on node 1


However I can connect to PostgreSQL when I am connected via ssh to the 
pgpool VM:

$ psql
psql (15.2, server 14.7)
Type "help" for help.

postgres=# \d
         List of relations
  Schema | Name | Type  |  Owner
--------+------+-------+----------
  public | t2   | table | postgres
  public | t3   | table | postgres
(2 rows)

postgres=# insert into t2 values (1);
INSERT 0 1

Questions:


1) I dont understand the "find_primary_node: 
make_persistent_db_connection_noerror failed on node 1" errors above

2) How do I test/prove load balancing?


Thanks in advance






-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20230329/262157e2/attachment-0001.htm>