[pgpool-general: 9365] Re: pgpool 4.5.5 can't reach stand by server

[Mauricio Fernandez]

Hi Tatsuo

At least it works.

I've changed backend_hostname0 to its IP address, then again I've attached
manually the secondary server and I could connect to pgpool

*[postgres at buda pgpool-II]$ pcp_attach_node -p 9898 -h 192.168.1.160 -n 1
-U maufer -W*
Password:
pcp_attach_node -- Command Successful
[postgres at buda pgpool-II]$

*[postgres at buda pgpool-II]$ psql -p 9999*Contraseña para usuario postgres:
psql (17.2)
Digite «help» para obtener ayuda.

postgres=#

This is the first part, I pretend to test pgpool as a load balancer and
connection pooler, after that I would try fail / switch over

Thank you very much for the support

Kind regards

Mauricio

El jue, 13 feb 2025 a las 20:10, Tatsuo Ishii (<ishii at postgresql.org>)
escribió:

> Hi Mauricio,
>
> > Hi Tatsuo
> >
> > I'm thinking I'm really near to success. I've decided to use
> pool_password
> > and it works, because pgpool watches the two nodes and I guess
> > health checking is working because there is nothing in the log about it.
> >
> > The configurations files look like this:
> > *[postgres at buda pgpool-II]$ vi pgpool.conf*
> >
> #------------------------------------------------------------------------------
> > # STREAMING REPLICATION MODE
> >
> #------------------------------------------------------------------------------
> > # - Streaming -
> > sr_check_period = 10
> > sr_check_user = 'postgres'
> > sr_check_password = ''
> > sr_check_database = 'postgres'
> >
> > # - Authentication -
> > enable_pool_hba = on
> > pool_passwd = 'pool_passwd'
> >
> >
> #------------------------------------------------------------------------------
> > # HEALTH CHECK GLOBAL PARAMETERS
> >
> #------------------------------------------------------------------------------
> > health_check_period = 10
> > health_check_timeout = 20
> > health_check_user = 'postgres'
> > health_check_password = ''
> > health_check_database = 'postgres'
> >
> >
> > *[postgres at buda pgpool-II]$ cat pool_hba.conf*# "local" is for Unix
> domain
> > socket connections only
> > local   all         all                               scram-sha-256
> > # IPv4 local connections:
> > host    all         all         127.0.0.1/32          trust
> > host    all         all         ::1/128               trust
> > host    all         all         192.168.1.0/24        scram-sha-256
> >
> > *[postgres at buda pgpool-II]$ cat pool_passwd *
> > replicador:AESKXF6Ksr76jXd82/nyf7HPw==
> > postgres:AESSW1V5WTuP1xf1xFenoLDhA==
> >
> > The pool_passwd content was generated executing: pg_enc -m -f
> > /etc/pgpool-II/pgpool.conf -k /var/lib/pgsql/.pgpoolkey -i
> > /etc/pgpool-II/usr.txt and I have defined environment
> > variable PGPOOLKEYFILE=/var/lib/pgsql/.pgpoolkey
> >
> > Postgres primary and secondary servers are working with scram-sha-256
> auth
> > method.
> >
> > Know, when I'm trying to connect to pgpool at least ask for password (pcp
> > user postgres and postgres database user have the same password)..
> >
> > *[postgres at buda ~]$ psql -p 9999*
> > Contraseña para usuario *postgres*:
> > psql: error: falló la conexión al servidor en el 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 *postgres *is the user declared in pcp.conf wich password is
> md5
>
> The password in pcp.conf is only used for using pcp commands. They are
> irrelevant to PostgreSQL authentication.
>
> > *[postgres at buda pgpool-II]$ cat pcp.conf*
> > # USERID:MD5PASSWD
> > postgres:ad9dfc895ce42200ba6e1127aacc7873
> >
> > I don't know who is rejecting the authentication, pgpool or postgresql.
>
> From the log it must be postgresql.
>
> > Both primary and secondary have the same pg_hba.conf
> > local   all             all
> > scram-sha-256
> > host    all             all             192.168.1.0/24
> >  scram-sha-256
> > # 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
>
> > BTW you seem to set backend_hostname0 = 'localhost', which is not
> > recommended. You'd better to set backend_hostname0 = '192.169.0.160'
> > instead.
>
> Do you still set backend_hostname0 = 'localhost'? Then it could be the
> cause of the problem. When backend0 receives the connection request
> from pgpool, the client IP is 'localhost' from PostgreSQL's point of
> view. So PostgreSQL looks into pg_hba.conf and fails to find matching
> entry for IP = 127.0.0.1, database = postgres, user = postgres.
>
> For standby, there's a matching entry in pg_hba.conf:
>
> > host    all             all             192.168.1.0/24
>
> 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/20250214/92a2b8c1/attachment.htm>