[pgpool-general: 9359] Re: pgpool 4.5.5 can't reach stand by server
Mauricio Fernandez
mmauricio.fernandez at gmail.com
Thu Feb 13 19:56:38 JST 2025
Hi Tatsuo..
Waiting for your answer, I was investigating a little and found what you
are saying.
I've configured /etc/pgpool-II/pcp.conf in the following way:
[postgres at buda pgpool-II]$ pg_md5 -p
password: posgres_password
ad9dfc895ce42200ba6e1127aacc7873
[postgres at buda pgpool-II]$cat pcp.conf
postgres:ad9dfc895ce42200ba6e1127aacc7873
Configured pcp, pcp_attach_node could do their work
[postgres at buda pgpool-II] pcp_attach_node -p 9898 -h localhost -n 1 -U
postgres -W
And now, pgpool every time start can see the two nodes
[postgres at buda pgpool_log]$ tail -f pgpool-Thu.log
2025-02-06 08:16:57.845: main pid 2835: LOG: Pgpool-II system is shutdown
2025-02-13 07:29:07.501: main pid 2281: LOG:
health_check_stats_shared_memory_size: requested size: 12288
2025-02-13 07:29:07.501: main pid 2281: LOG: memory cache initialized
2025-02-13 07:29:07.501: main pid 2281: LOG: allocating (136981824) bytes
of shared memory segment
2025-02-13 07:29:07.501: main pid 2281: LOG: allocating shared memory
segment of size: 136981824
2025-02-13 07:29:08.202: main pid 2281: LOG:
health_check_stats_shared_memory_size: requested size: 12288
2025-02-13 07:29:08.202: main pid 2281: LOG:
health_check_stats_shared_memory_size: requested size: 12288
2025-02-13 07:29:08.202: main pid 2281: LOG: memory cache initialized
2025-02-13 07:29:08.204: main pid 2281: LOG: pool_discard_oid_maps:
discarded memqcache oid maps
2025-02-13 07:29:08.213: main pid 2281: LOG: create socket files[0]:
/var/run/postgresql/.s.PGSQL.9999
2025-02-13 07:29:08.213: main pid 2281: LOG: listen address[0]: *
2025-02-13 07:29:08.213: main pid 2281: LOG: Setting up socket for
0.0.0.0:9999
2025-02-13 07:29:08.213: main pid 2281: LOG: Setting up socket for :::9999
2025-02-13 07:29:08.221: main pid 2281: LOG: find_primary_node_repeatedly:
waiting for finding a primary node
2025-02-13 07:29:08.284: main pid 2281: LOG: find_primary_node: primary
node is 0
2025-02-13 07:29:08.284: main pid 2281: LOG: find_primary_node: standby
node is 1
2025-02-13 07:29:08.284: main pid 2281: LOG: create socket files[0]:
/var/run/postgresql/.s.PGSQL.9898
2025-02-13 07:29:08.284: main pid 2281: LOG: listen address[0]: *
2025-02-13 07:29:08.285: main pid 2281: LOG: Setting up socket for
0.0.0.0:9898
2025-02-13 07:29:08.285: main pid 2281: LOG: Setting up socket for :::9898
2025-02-13 07:29:08.286: pcp_main pid 2318: LOG: PCP process: 2318 started
2025-02-13 07:29:08.286: sr_check_worker pid 2319: LOG: process started
2025-02-13 07:29:08.286: health_check pid 2321: LOG: process started
2025-02-13 07:29:08.287: health_check pid 2320: LOG: process started
2025-02-13 07:29:08.288: main pid 2281: LOG: pgpool-II successfully
started. version 4.5.5 (hotooriboshi)
*2025-02-13 07:29:08.288: main pid 2281: LOG: node status[0]: 12025-02-13
07:29:08.288: main pid 2281: LOG: node status[1]: 2*
But the problem now is I can't connect to to pgppool
[postgres at buda ~]$ psql -p 9999 -U postgres
psql: error: failed the conection to the server in the socket
«/run/postgresql/.s.PGSQL.9999»: ERROR: invalid authentication packet from
backend
DETALLE: failed to get the authentication packet length
SUGERENCIA: This is likely caused by the inconsistency of auth method
among DB nodes.Please check the previous error messages (hint: length
field) from
pool_read_message_length and recheck the pg_hba.conf settings.
I presume there ir a conflict between md5 and scram-sha-256
The file pg_hba.conf look like this:
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all
scram-sha-256
# IPv4 local connections:
host all all 192.168.1.0/24
scram-sha-256
# IPv6 local connections:
host all all ::1/128 trust
host all all 192.168.1.0/24 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host replication replicador 192.168.1.0/24 scram-sha-256
I'm not using the file pool_passwd because XXX_check_password are set
in /etc/pgpool-II/pgpool.conf
The encrypted password in postgresql is
[postgres at buda ~]$ psql -p 5434 -U postgres
Contraseña para usuario postgres:
psql (17.2)
Digite «help» para obtener ayuda.
postgres=# select passwd from pg_shadow where usename = 'postgres';
passwd
------------------------------------------------------------------------------------------------------------------------------------
---
SCRAM-SHA-256$4096:w+Mtz/vy4Ily+fhUXjMLKw==$JtG156jWegalB3lYU2USg4S/IIw9xEOk7S7HLqbE4ec=:thnRnO4N0sb5Boh5Mxz4wRAMzfjvCJ+cRV8clii+oP
g=
(1 fila)
postgres=#
When I manually generate a password encrypted AES256, the result doesn't
resemble de password in the database
[postgres at buda ~]$ pg_enc -p -k /var/lib/pgsql/.pgpoolkey
db password:
trying to read key from file /var/lib/pgsql/.pgpoolkey
SW1V5WTuP1xf1xFenoLDhA==
pool_passwd string: AESSW1V5WTuP1xf1xFenoLDhA==
Anyway, the summary is :
- pgpool is watching two nodes
- I can't communicate with pgpool.
Thank you very much for the support
Kind Regards
Mauricio Fernández
El mié, 12 feb 2025 a las 22:42, Tatsuo Ishii (<ishii at postgresql.org>)
escribió:
> Hello Mauricio,
>
> > Hola Tatsuo
> >
> > I've tried the command
> >
> > [postgres at buda pgpool-II]$ pcp_attach_node -p 9898 -h localhost 1
> > FATAL: authentication failed for user "postgres"
> > DETAIL: username and/or password does not match
>
> Have you created pcp_conf? pcp commands require pcp_conf for their
> authentication (separate from pgpool.conf or pool_passwd).
>
> https://www.pgpool.net/docs/latest/en/html/configuring-pcp-conf.html
>
> > May be this authentication error is the source problem at all..
> >
> > Right now, the pgpool.conf setting have the following:
> >
> > sr_check_period = 10
> > sr_check_user = 'postgres'
> > sr_check_password = 'the_postgres_passw'
> >
> > health_check_user = 'postgres'
> > health_check_password = ' the_postgres_passw'
> > health_check_database = 'postgres'
> >
> > Initially I had let the passwords parameters empty '' and set the
> passwords
> > through
> >
> > pg_enc -m -f /etc/pgpool-II/pgpool.conf -u postgres -k
> > /var/lib/pgsql/.pgpoolkey
> >
> > [postgres at buda pgpool-II]$ cat pool_passwd
> > postgres:AESSW1V5WTuP1xf1xFenoLDhA==
> >
> > Obviously didn't work...
> >
> > I'm assuming one of the check users (sr or health ) is used by pgpool.
>
> This should work. What kind of errors do you see in pgpool.log?
>
> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20250213/a3c3b160/attachment.htm>
More information about the pgpool-general
mailing list