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