[pgpool-general: 8995] Errors when starting and connecting pgpool2
Dieter Pillefatz
dieterpillefatz at gmail.com
Mon Jan 15 19:35:49 JST 2024
Hi,
I am testing pgpool2 and have a setup of 2 postgres nodes, using streaming
replication, and 1 pgpool2 node.
Postgres version 15.5
pgpool2 version 4.5.1
Both running on RH EL 8
pgpool2 is installed on a different node and the firewall ports are all
open.
However, the connection to pgpool2 via port 9999 fails from the first
postgres node fails with error:
psql -U postgres -h <hostname> -p 9999
Password for user postgres:
psql: error: connection to server at "<hostname_pgpool2>"
(<ip_pgpool2_node>), port 9999 failed: ERROR: unable to read message kind
DETAIL: kind does not match between main(52) slot[1] (45)
pgpool log:
2024-01-15 10:58:01.092: child pid 59665: LOG: pool_read_kind: error
message from 1 th backend:no pg_hba.conf entry for host "<ip>", user
"postgres", database "postgres", no encryption
2024-01-15 10:58:01.092: child pid 59665: ERROR: unable to read message
kind
2024-01-15 10:58:01.092: child pid 59665: DETAIL: kind does not match
between main(52) slot[1] (45)
2024-01-15 10:58:01.977: main pid 59631: LOG: find_primary_node:
make_persistent_db_connection_noerror failed on node 0
postgres log:
time=2024-01-15 10:58:15 CET, pid=2952802 db=postgres, usr=nobody,
client=<hostname_pgpool2> , app=[unknown], line=1 :: FATAL: password
authentication failed for user "nobody"
time=2024-01-15 10:58:15 CET, pid=2952802 db=postgres, usr=nobody,
client=< hostname_pgpool2 > , app=[unknown], line=2 :: DETAIL: Role
"nobody" does not exist.
Connection matched pg_hba.conf line 112: "host all all
<ip_pgpool2_node>/32 scram-sha-256"
I configured pgpool.conf as follows:
backend_clustering_mode = 'streaming_replication'
listen_addresses = '*'
port = 9999
unix_socket_directories = '/var/run/postgresql'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/var/run/postgresql'
backend_hostname0 = <hostname_postgres_node1>
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '<path1>'
backend_application_name0 = '<app_name1>'
backend_hostname1 = <hostname_postgres_node2>
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '<path2>'
backend_application_name1 = '<app_name2>'
pid_file_name = '/var/run/pgpool/pgpool.pid'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
And generated a .pgpoolkey file:
su – postgres
echo '<*postgres_password*>' > /var/lib/pgsql/.pgpoolkey
chmod 600 ~/.pgpoolkey
Then I generated an encrypted entry for scram-sha-256 in pool_passwd:
pg_enc -m -k /var/lib/pgsql/.pgpoolkey -u postgres -p
On the postgres database nodes the pg_hba.conf has appropiate entries for
the pgpool2 node:
host all postgres <ip_address_pgpool2_node>/32
scram-sha-256
host all postgres <ip_address_pgpool2_node>/32
scram-sha-256
Do you have any idea, why the connection between pgpool2 and postgres
database fails?
Why is it trying to authenticate with user nobody?
I also created a user nobody in the database, but the errors are still
there.
Your help would be appreciated.
Regards
Oliver
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20240115/8f8f04f5/attachment.htm>
More information about the pgpool-general
mailing list