Thread: PgbackRest : Stanza creation fails on DB Server and Repo Server
I am trying pgbackrest config on a Production Server and a Repo server ( RHEL 9.4 EPAS 16 , pgbackrest 2.52.1
# "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 ~]#
I have configured pbbackrest.conf on both machines as per the official docs.
From both 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 ~]#
[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 ~]#
[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 ~]#
[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 supplied
ERROR: [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 ~]#
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 supplied
ERROR: [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 ~]#
*:*: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
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.
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 asYou 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
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 asYou 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.
Thank you all for the great help ..
on the DB server's enterprisedb user's home directory ( Here it is /var/lib/edb/ )
[root@db1 edb]# ls -al .pgpass
-rw-------. 1 enterprisedb enterprisedb 28 Aug 29 09:26 .pgpass
[root@db1 edb]#
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]#
# 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
*:*:replication:enterprisedb:my_password
changed to
[root@db1 edb]# cat .pgpass
*:*:*:enterprisedb:password
*:*:*: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]#
*:*:*: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 asYou 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.