[pgpool-general: 8699] Re: pgpool setup issues
Bo Peng
pengbo at sraoss.co.jp
Thu Mar 30 13:43:50 JST 2023
Hello,
> 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
You need to configure sr_check_user
because find_primary node uses sr_check_user to connect to PostgreSQL.
You are setting:
backend_flag0 = 'DISALLOW_TO_FAILOVER
backend_flag1 = 'DISALLOW_TO_FAILOVER
If you want to disable failover, you also need to set
failover_on_backend_error = off.
> 2) How do I test/prove load balancing?
Check the number of "select_cnt" in the result of "show pool_nodes".
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 | 11002 | up | up | 0.500000 | primary | primary | 3 | true | 0 | | | 2023
-03-30 13:35:18
1 | localhost | 11003 | up | up | 0.500000 | standby | standby | 2 | false | 0 | | | 2023
-03-30 13:35:18
(2 rows)
As you can see in the result above, "select" are balanced to each nodes.
Alternatively, you can enable "log_per_node_statement = on"
to record each queries with node id to logs.
Example:
2023-03-30 13:37:11.691: psql pid 20154: LOG: DB node id: 0 backend pid: 20259 statement: select 1;
2023-03-30 13:37:21.026: psql pid 20154: LOG: DB node id: 1 backend pid: 20276 statement: select 1;
--
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
https://www.sraoss.co.jp/
More information about the pgpool-general
mailing list