Thread

1. PgbackRest : Stanza creation fails on DB Server and Repo Server

   KK CHN <kkchn.in@gmail.com> — 2024-08-28T14:09:18Z

   I am trying  pgbackrest config  on a Production Server and a Repo server (
   RHEL 9.4  EPAS 16 , pgbackrest 2.52.1
   
   I have configured   pbbackrest.conf on both machines  as per the official
   docs.
   
   From b*oth machines password less auth works for the db user *(enterprisedb
   ) and repouser(postgres)
   
   .
   
   When I create the stanza on both the  DB server and Repo server it fails
   with
   
   connection to server socket failed  no password supplied..
   
   ################################################################################
   Here my configs on both primary and repo server.
   
   DB Server.
   [root@db1 ~]# cat /etc/pgbackrest/pgbackrest.conf
   [Repo]
   pg1-path=/data/edb/as16/data
   pg1-port=5444
   pg1-user=enterprisedb
   pg-version-force=16
   pg1-database=edb
   
   [global]
   repo1-host=10.255.0.40
   repo1-host-user=postgres
   archive-async=y
   spool-path=/var/spool/pgbackrest
   log-level-console=info
   log-level-file=debug
   delta=y
   
   [global:archive-get]
   process-max=2
   
   [global:archive-push]
   process-max=4
   [root@db1 ~]#
   
   
   
   
   
   Reposerver
   [root@dbtest ~]# cat /etc/pgbackrest/pgbackrest.conf
   [Repo]
   pg1-host=10.15.0.202
   pg1-host-user=enterprisedb
   pg1-path=/data/edb/as16/data
   pg-version-force=16
   
   [global]
   repo1-path=/data/DB_BKUPS
   repo1-block=y
   repo1-bundle=y
   repo1-retention-full=2
   repo1-retention-diff=2
   repo1-cipher-type=aes-256-cbc
   repo1-cipher-pass=acbd
   process-max=5
   log-level-console=info
   log-level-file=debug
   start-fast=y
   delta=y
   
   [global:archive-push]
   compress-level=3
   [root@dbtest ~]#
   
   
   
   *On DB Server stanza creation fails with  *
          valid_lft forever preferred_lft forever
   [root@db1 ~]# sudo -u enterprisedb pgbackrest --stanza=Repo
   --log-level-console=info stanza-create
   2024-08-28 19:30:31.518 P00   INFO: stanza-create command begin 2.52.1:
   --exec-id=4062179-ecf39176 --log-level-console=info --log-level-file=debug
   --pg1-database=edb --pg1-path=/data/edb/as16/data --pg1-port=5444
   --pg1-user=enterprisedb --pg-version-force=16 --repo1-host=10.255.0.40
   --repo1-host-user=postgres --stanza=Repo
   *WARN: unable to check pg1: [DbConnectError] unable to connect to
   'dbname='edb' port=5444 user='enterprisedb'': connection to server on
   socket "/tmp/.s.PGSQL.5444" failed: fe_sendauth: no password supplied*
   ERROR: [056]: unable to find primary cluster - cannot proceed
          HINT: are all available clusters in recovery?
   2024-08-28 19:30:31.523 P00   INFO: stanza-create command end: aborted with
   exception [056]
   [root@db1 ~]#
   
   
   
   
   
   
   
   
   *On Repo server:  stanza creation fails with follows. *
   
   
   [root@dbtest ~]# sudo -u postgres pgbackrest --stanza=Repo
   --log-level-console=info stanza-create
   2024-08-28 19:21:10.958 P00   INFO: stanza-create command begin 2.52.1:
   --exec-id=350565-6e032daa --log-level-console=info --log-level-file=debug
   --pg1-host=10.15.0.202 --pg1-host-user=enterprisedb
   --pg1-path=/data/edb/as16/data --pg-version-force=16
   --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc
   --repo1-path=/data/DB_BKUPS --stanza=Repo
   
   *WARN: unable to check pg1: [DbConnectError] raised from remote-0 ssh
   protocol on '10.15.0.202': unable to connect to 'dbname='edb' port=5444
   user='enterprisedb'': connection to server on socket "/tmp/.s.PGSQL.5444"
   failed: fe_sendauth: no password suppliedERROR: [056]: unable to find
   primary cluster - cannot proceed*
          HINT: are all available clusters in recovery?
   2024-08-28 19:21:12.462 P00   INFO: stanza-create command end: aborted with
   exception [056]
   [root@dbtest ~]#
   
   
   
   
   My DB Server pg_hba.conf  as follows
   
   
   # "local" is for Unix domain socket connections only
   local   all             all                                     md5
   # IPv4 local connections:
   host    all             all             127.0.0.1/32            md5
   host    all             all             10.0.0.0/8              md5
   
   
   
   # IPv6 local connections:
   host    all             all             ::1/128                 md5
   # Allow replication connections from localhost, by a user with the
   # replication privilege.
   local   replication     all                                     md5
   host    replication     all             127.0.0.1/32            md5
   host    replication     all             ::1/128                 md5
   host    replication     all             10.0.0.0/8              md5
   [root@db1 ~]#
   
   
   
   
   and I have   .pgpass in DB server as
   
   [root@db1 ~]# cat /var/lib/edb/.pgpass
   *:*:replication:enterprisedb:my_secret_password
   [root@db1 ~]# ls -al /var/lib/edb/.pgpass
   -rw-------. 1 enterprisedb enterprisedb 38 Aug 28 19:01 /var/lib/edb/.pgpass
   [root@db1 ~]#
   
   WHy it complains about  no password supplied..
   
   
   Any help is much appreciated.
   
   Krishane
   

2. Re: PgbackRest : Stanza creation fails on DB Server and Repo Server

   David G. Johnston <david.g.johnston@gmail.com> — 2024-08-28T14:30:23Z

   On Wednesday, August 28, 2024, KK CHN <kkchn.in@gmail.com> wrote:
   
   >
   > and I have   .pgpass in DB server as
   >
   
   You assumed this mattered but I see no mention that pgBackRest consults
   this file.
   
   It seems to require the local entry in pg_hba.conf to use peer
   authentication.
   
   David J.
   

3. Re: PgbackRest : Stanza creation fails on DB Server and Repo Server

   Amitabh Kant <amitabhkant@gmail.com> — 2024-08-28T14:54:41Z

   On Wed, Aug 28, 2024 at 8:00 PM David G. Johnston <
   david.g.johnston@gmail.com> wrote:
   
   > On Wednesday, August 28, 2024, KK CHN <kkchn.in@gmail.com> wrote:
   >
   >>
   >> and I have   .pgpass in DB server as
   >>
   >
   > You assumed this mattered but I see no mention that pgBackRest consults
   > this file.
   >
   > It seems to require the local entry in pg_hba.conf to use peer
   > authentication.
   >
   > David J.
   >
   >
   Section 21.4 on this page (
   https://pgbackrest.org/user-guide.html#introduction )  does seem to mention
   the use of .pgpass file. I have no idea about the actual problem though.
   
   Amitabh
   

4. Re: PgbackRest : Stanza creation fails on DB Server and Repo Server

   David G. Johnston <david.g.johnston@gmail.com> — 2024-08-28T14:58:04Z

   On Wednesday, August 28, 2024, Amitabh Kant <amitabhkant@gmail.com> wrote:
   
   > On Wed, Aug 28, 2024 at 8:00 PM David G. Johnston <
   > david.g.johnston@gmail.com> wrote:
   >
   >> On Wednesday, August 28, 2024, KK CHN <kkchn.in@gmail.com> wrote:
   >>
   >>>
   >>> and I have   .pgpass in DB server as
   >>>
   >>
   >> You assumed this mattered but I see no mention that pgBackRest consults
   >> this file.
   >>
   >> It seems to require the local entry in pg_hba.conf to use peer
   >> authentication.
   >>
   >> David J.
   >>
   >>
   > Section 21.4 on this page ( https://pgbackrest.org/user-
   > guide.html#introduction )  does seem to mention the use of .pgpass file.
   > I have no idea about the actual problem though.
   >
   
   Yes, postgres itself uses .pgpass so when you configure streaming
   replication between two servers, something that is doable regardless of
   using pgBackRest, the server-to-server connection can utilize .pgpass.
   
   David J.
   

5. Re: PgbackRest : Stanza creation fails on DB Server and Repo Server

   KK CHN <kkchn.in@gmail.com> — 2024-08-29T04:57:39Z

   Thank you all for the great help ..
   
   I couldn't get a chance to    Restart the DB Cluster after making the
   changes highlighted.  Correct me if I am wrong (  production server, down
   time requested.  ) after editing the     pg_hba.conf  on   DB server as
   follows
   local   all             all                                     trust
   # IPv4 local connections:
   host    all             all             127.0.0.1/32            md5
   host    all             all             10.0.0.0/8              md5
   
   
   
   # IPv6 local connections:
   host    all             all             ::1/128                 md5
   # Allow replication connections from localhost, by a user with the
   # replication privilege.
   local   replication     all                                      peer
   #local   replication     all                                     md5
   host    replication     all             127.0.0.1/32            md5
   host    replication     all             ::1/128                 md5
   host    replication     all             10.0.0.0/8              md5
   [root@db1 edb]#
   
   
   
   
   But a work around  seems worked as follows :
   
   [root@db1 edb]# cat .pgpass
   *:*:replication:enterprisedb:my_password
   
   changed to
   
   [root@db1 edb]# cat .pgpass
   *:*:*:enterprisedb:password
   
   
   For those struggling with this issue    this may help ...
   
   on the DB server's   enterprisedb user's  home directory ( Here it is
   /var/lib/edb/  )
   
   [root@db1 edb]# cat .pgpass
   *:*:*:enterprisedb:password
   [root@db1 edb]# ls -al .pgpass
   -rw-------. 1 enterprisedb enterprisedb 28 Aug 29 09:26 .pgpass
   [root@db1 edb]#
   
   
   [root@db1 edb]# ls -al .pgpass
   -rw-------. 1 enterprisedb enterprisedb 28 Aug 29 09:26 .pgpass
   [root@db1 edb]#
   
   
   On Wed, Aug 28, 2024 at 8:28 PM David G. Johnston <
   david.g.johnston@gmail.com> wrote:
   
   > On Wednesday, August 28, 2024, Amitabh Kant <amitabhkant@gmail.com> wrote:
   >
   >> On Wed, Aug 28, 2024 at 8:00 PM David G. Johnston <
   >> david.g.johnston@gmail.com> wrote:
   >>
   >>> On Wednesday, August 28, 2024, KK CHN <kkchn.in@gmail.com> wrote:
   >>>
   >>>>
   >>>> and I have   .pgpass in DB server as
   >>>>
   >>>
   >>> You assumed this mattered but I see no mention that pgBackRest consults
   >>> this file.
   >>>
   >>> It seems to require the local entry in pg_hba.conf to use peer
   >>> authentication.
   >>>
   >>> David J.
   >>>
   >>>
   >> Section 21.4 on this page (
   >> https://pgbackrest.org/user-guide.html#introduction )  does seem to
   >> mention the use of .pgpass file. I have no idea about the actual problem
   >> though.
   >>
   >
   > Yes, postgres itself uses .pgpass so when you configure streaming
   > replication between two servers, something that is doable regardless of
   > using pgBackRest, the server-to-server connection can utilize .pgpass.
   >
   > David J.
   >