Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUUxG-003Tnf-EI for pgsql-general@arkaria.postgresql.org; Thu, 18 Jul 2024 17:30:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sUUxD-001sOZ-E1 for pgsql-general@arkaria.postgresql.org; Thu, 18 Jul 2024 17:30:07 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUUxC-001sOR-U8 for pgsql-general@lists.postgresql.org; Thu, 18 Jul 2024 17:30:07 +0000 Received: from mail-yw1-x1132.google.com ([2607:f8b0:4864:20::1132]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sUUx6-000EoM-7q for pgsql-general@postgresql.org; Thu, 18 Jul 2024 17:30:06 +0000 Received: by mail-yw1-x1132.google.com with SMTP id 00721157ae682-65f9e25fffaso10420327b3.3 for ; Thu, 18 Jul 2024 10:30:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1721323798; x=1721928598; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=Zl96ahx3oWEb2k8RMlXvQ8WZHfqO0XqMjPPlyD49lnA=; b=H/lf/FuuGxOGy6R9Mt3loLT6IZB+c2mgBErddQ9ra6UfLDjqxDcYOWxUacSVdJpWo0 tmzWnM0X7CHlih+imoRUFAYkiYqIrLOTvZJyUWP9kG7A8mVjRKrIQ/1lyYz4AZquGo1l fk74D9tt4Uwg2vATSgpGmmIkBDReXOCxpxA1CzTUPFpzLm67ilZ5OK3r8rANiA10RW2E GFL/obNUjqNVYg0JZzj1IMgwVr+Do05QA6OpiuQSv4ucTy6MG0sob1diLku7Z/0K96/o 3ld/qNr+jnOwkS+rbHPmnkJVdy5KZu+AzyUgocqnP/GD8II+4tdkVWOdfiR37T2MFrqe Xe0Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1721323798; x=1721928598; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=Zl96ahx3oWEb2k8RMlXvQ8WZHfqO0XqMjPPlyD49lnA=; b=riJloEIi5S4vyedp9uWuVTL7e5MzvSQhyy8rNsDu6wpJoBCMlWiW3bEcBrAOIJOf3q GcuvIqBn6NzH7qYayJoUTWPEG948kmWXRvgspIr6P+X0NbOICDsP1AJv0gYNsZUDLovF Sx8Yt+LEHXpxq5miBec5CheZAfY35q9ATOVp0F0fAqZkRwvQU5zRVKtdQdtpPxgJyNOC mE5DdUr77boy2iz7Fw4omXsptB8WGSA8fz/g1BsAoAPFZdrZudsjHcXLfr/jqd2QpycM qaxvj/2OuuzXKMgHpnhFv5oRhPo55INn3xB4Y4/rmBk52WeK3jhehdgaeFjbki7nilbF 3rRg== X-Forwarded-Encrypted: i=1; AJvYcCX+WAnHvVtPPdxvPwaFOpiLI1Yau/oJwBxnqFdTGC0WSPmZdQ1aHUfVZCZcSr/mvyiaQ+W4cOqBfdbyDJImcttxBormoodesgMI0EL5 X-Gm-Message-State: AOJu0Yz5Y6MEh4rzlwvy92gnH4gChNkDdbEffk33CnhthgOtswhpGIh0 UxYp315oJ1Vuj1HbDa2c78AYSjzG+MpH58seWmwMDXSkXQx5iNAclZRrHywUo4IwtOj4hbMBIS6 PDfS8Q1YfToQqQTVeRA0qRy9a9yQ= X-Google-Smtp-Source: AGHT+IHrGoDbaAQVv4kY3tmZcvzPR0QZstWTncyGoBTJmOj0mWfxvD+KnMf3pQtWqYUnKTYi4y0oJYCEhI9sQocDcQ0= X-Received: by 2002:a05:690c:12:b0:665:7661:2b7a with SMTP id 00721157ae682-665767076f3mr73401847b3.25.1721323798012; Thu, 18 Jul 2024 10:29:58 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: azeem subhani Date: Thu, 18 Jul 2024 22:29:46 +0500 Message-ID: Subject: Re: PgbackRest and EDB Query To: KK CHN Cc: Kashif Zeeshan , pgsql-general Content-Type: multipart/alternative; boundary="000000000000862904061d88ed1e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000862904061d88ed1e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi, By following these steps, you should be able to connect to the EDB server from the Repo server using passwordless authentication: 1. First, generate an SSH key pair on the client machine(Repo Server machine): * ssh-keygen -t rsa -b 2048 -f ~/.ssh/id_rsa_enteprisedb -N ""* 2. Copy the Public Key to the EDB Server: * ssh-copy-id -i ~/.ssh/id_rsa_enteprisedb.pub enterprisedb@192.168.87.112 * 3. Configure the SSH client on Repo Server to automatically use the SSH key for passwordless login without requiring the -i option: * vi ~/.ssh/config # add following contents* *Host 192.168.87.112 User enterprisedb IdentityFile ~/.ssh/id_rsa_enteprisedb* 4. Make sure config file has correct permissions: *chmod 600 ~/.ssh/config* Now, you should be able to connect to EBD server from Repo server without a password prompt. It works for me. On Thu, Jul 18, 2024 at 6:10=E2=80=AFPM KK CHN wrote: > > > Hi list, > > Thank you all for your inputs, I am trying pgbacrest with > Enterprised DB. Locally pgbackrest works for EDB but when I am trying f= or > remote repository I am facing an issue ( from the remote host to EDB > server password less authentication part ) > > Trying to use a remote host as Repo Server I am facing the issue of > passwordless authentication(Public key private key). > > 1. From the EDB server I added the user pgbackrest directory and > generated ssh-keys and copied the id_rsa.pub to the Repo server > (pgbackrest user's .ssh dir with necessary permissions) > everything(passwordless auth) working to one side. > > From the EDB Postgres Advanced Server nodes > $ sudo -u enterprisedb ssh pgbackrest@backup-server > > This works from EDB server machine without any issue(password less auth > works) > > > > 2 But from the reposerver > $sudo -u pgbackrest ssh enterprisedb@EDB_Server_IP unable to do > password less auth( Its asking password for enterpridb@EDB_Server ) > > How to do the passwordless auth from the Repo server to the EDB server > for the default "enterprisedb" user of EDB ? ( enterprisedb user doesn't > have any home dir I mean /home/enterprisedb, so I am not sure where to > create .ssh dir and authorized_keys for passwordless auth ) > > Any one who has already tackled this kindly guide me on how to achieve > this . > > > Thank you, > Krishane > > > > > > > > On Wed, Jul 17, 2024 at 9:07=E2=80=AFPM Kashif Zeeshan > wrote: > >> Hi >> >> On Wed, Jul 17, 2024 at 5:21=E2=80=AFPM KK CHN wrot= e: >> >>> Hi , >>> >>> I am trying pgbackrest(2.52.1) with postgresql( version 16) on a lab >>> setup on RHEL-9. Both PostgreSQL server and a remote Repository host >>> configured with pgbackrest and everything working fine as specified in = the >>> documentation. >>> >>> note: here I am running postgres server and pgbackrest everything as >>> postgres user and no issues in backup and recovery. >>> >>> >>> >>> Query >>> 1. Is it possible to use PgBackrest with EnterpriseDB(EDB -16) for th= e >>> backup and recovery process? Or pgback works only with the community >>> PostgreSQL database ? >>> >> It support both community PG and EDB PG. >> >>> >>> >>> [ when I ran initdb script of EDB while installing EDB it creates the >>> enterpisedb as user and edb as initial database by the script. ] >>> >> Enterprisedb is the default user created by EDB. >> >>> >>> >>> when I try to create the stanza on the EDB server it throws error >>> (pasted at bottom ). >>> >>> >>> >>> NOTE: >>> I know that my EDB running on port 5444 instead of 5432 and the >>> dbname =3D edb instead of postgres, and user as enterpisedb instead of >>> postgres how to specify these changes in the stanza creation step if E= DB >>> Supports pgbackrest tool ? >>> >> You can enter this connection information in the PbBackRest Conf file fo= r >> the stanza you create for your EDB Instance. >> >> e.g >> >> [global]repo1-path=3D/var/lib/edb/as15/backups >> [demo]pg1-path=3D/var/lib/edb/as15/datapg1-user=3Denterprisedbpg1-port= =3D5444pg-version-force=3D15 >> >> Refer to following edb documentation >> >> >> https://www.enterprisedb.com/docs/supported-open-source/pgbackrest/03-qu= ick_start/ >> >> >>> OR Am I doing a waste exercise [if pgbackrest won't go ahead with ED= B >>> ] ? >>> >>> >>> Any hints much appreciated. >>> >>> Thank you, >>> Krishane >>> >>> >>> ERROR: >>> root@uaterssdrservice01 ~]# sudo -u postgres pgbackrest --stanza=3DOD_D= M2 >>> --log-level-console=3Dinfo stanza-create >>> 2024-07-17 17:42:13.935 P00 INFO: stanza-create command begin 2.52.1: >>> --exec-id=3D1301876-7e055256 --log-level-console=3Dinfo --log-level-fil= e=3Ddebug >>> --pg1-path=3D/var/lib/pgsql/16/data --repo1-host=3D10.x.y.7 >>> --repo1-host-user=3Dpostgres --stanza=3DOD_DM2 >>> WARN: unable to check pg1: [DbConnectError] unable to connect to >>> 'dbname=3D'postgres' port=3D5432': connection to server on socket >>> "/tmp/.s.PGSQL.5432" failed: No such file or directory >>> Is the server running locally and accepting connections on that >>> socket? >>> ERROR: [056]: unable to find primary cluster - cannot proceed >>> HINT: are all available clusters in recovery? >>> 2024-07-17 17:42:13.936 P00 INFO: stanza-create command end: aborted >>> with exception [056] >>> [root@uaterssdrservice01 ~]# >>> >>> >>> >>> >>> --=20 Thanks Azeem Subhani --000000000000862904061d88ed1e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,

By following these steps= , you should be able to connect to the EDB server from the Repo server usin= g passwordless authentication:

1. First, generate = an SSH key pair on the client machine(Repo Server machine):
= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ssh-keygen -t rsa -b 2048 -f ~/.ssh/id= _rsa_enteprisedb -N ""
2. Copy the Public Key to th= e EDB Server:
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ssh-c= opy-id -i ~/.ssh/id_rsa_enteprisedb.pub enterprisedb@192.168.87.112
3. Configure the = SSH client on Repo Server to automatically use the SSH key for passwordless= login without requiring the -i option:
=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 vi ~/.ssh/config # add following contents
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Host 192.168.87.= 112
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 User enterprisedb=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Identi= tyFile ~/.ssh/id_rsa_enteprisedb
4. Make sure config file has c= orrect permissions:
=C2=A0=C2=A0=C2=A0=C2=A0 chmod 600 ~/.ssh/config<= /i>

Now, you should be able to connect to E= BD server from Repo server without a password prompt. It works for me.
<= /div>



On Thu, Jul 18, 2024 at 6:10=E2=80=AFPM = KK CHN <kkchn.in@gmail.com>= wrote:


Hi list,= =C2=A0

Thank you all for your=C2=A0 inputs, I am t= rying pgbacrest=C2=A0with Enterprised=C2=A0DB.=C2=A0 Locally pgbackrest wor= ks for=C2=A0 EDB but when I am trying for remote repository I am facing an = issue ( from the remote host to=C2=A0 EDB server=C2=A0 password less authen= tication part )

Trying to=C2=A0 use a remote host= =C2=A0 as Repo Server I am facing the issue of=C2=A0 passwordless=C2=A0 aut= hentication(Public key private key).

1.=C2=A0 From= the EDB server=C2=A0 I=C2=A0 added the user pgbackrest directory and gener= ated ssh-keys and copied the id_rsa.pub=C2=A0 =C2=A0to=C2=A0 the Repo serve= r (pgbackrest user's .ssh dir with necessary permissions) everything(pa= sswordless auth) working to one side.
From the EDB Postgres Advanced Server =
nodes
$ sudo =
-u enterprisedb ssh pgbackrest@backup=
-server
This works from=C2=A0 EDB server machine without any issue(password l= ess auth works)



2 Bu= t=C2=A0 =C2=A0from the reposerver=C2=A0 =C2=A0 =C2=A0=C2=A0
$sudo= -u pgbackrest=C2=A0 =C2=A0ssh enterprisedb@EDB_Server_IP=C2=A0 =C2=A0 =C2= =A0 =C2=A0unable to do password less auth( Its asking password for enterpri= db@EDB_Server )

How to do the passwordless auth=C2= =A0 from the=C2=A0 Repo server to the EDB server=C2=A0 for the default &quo= t;enterprisedb" user of=C2=A0 EDB ? ( enterprisedb user doesn't ha= ve any home dir=C2=A0 I mean /home/enterprisedb, so I am not sure where to = create .ssh dir and authorized_keys for=C2=A0 passwordless auth=C2=A0 )=C2= =A0

Any one who has already tackled this kindly gu= ide=C2=A0 me on how to achieve this .


Thank you,=C2=A0
Krishane


<= div>




On Wed, Jul 17, 2024= at 9:07=E2=80=AFPM Kashif Zeeshan <kashi.zeeshan@gmail.com> wrote:
Hi=C2=A0

On Wed, Jul 17, 2024 at 5:21=E2=80=AFPM KK CHN <kkchn.in@gmail.com>= wrote:
Hi ,=C2=A0

I am trying pgbackrest(2.52.1)=C2=A0= with postgresql( version 16)=C2=A0 on=C2=A0 a lab setup on=C2=A0RHEL-9. Bo= th=C2=A0 PostgreSQL server and a remote Repository host configured with pgb= ackrest and everything=C2=A0working fine as specified in the documentation.= =C2=A0=C2=A0

note:=C2=A0 here I am running postgre= s server and pgbackrest everything as postgres user and no issues in=C2=A0 = backup and recovery.=C2=A0



Query
1. Is it possible to use=C2=A0 PgBackrest with=C2=A0= EnterpriseDB(EDB -16) for the backup and recovery process?=C2=A0Or pgback = works only with the community=C2=A0 PostgreSQL database ?
It support both community PG and EDB PG.=C2=A0


[ when I ran=C2=A0 initdb script of EDB while installing ED= B it creates the enterpisedb=C2=A0 as user and edb as initial=C2=A0 databas= e by the script. ]
Enterprisedb=C2=A0is th= e default user created by EDB.=C2=A0


=
when I try to create the stanza on the EDB server it throws error=C2= =A0 (pasted at bottom ).



=
NOTE:=C2=A0
I know that=C2=A0 my EDB=C2=A0 running on=C2=A0 = port 5444 instead of=C2=A0 5432 and the dbname =3D edb instead of postgres,= and user as=C2=A0 enterpisedb instead of postgres how to specify these cha= nges in the stanza creation step if=C2=A0 EDB Supports pgbackrest tool ?=C2= =A0 =C2=A0
You can enter this connection infor= mation in the PbBackRest Conf file for the stanza you create for your EDB I= nstance.
=C2=A0
e.g
[global]
repo1-path=3D/var/lib/edb/as15/backups

[demo]
pg1-path=
=3D/var/lib/edb/as15/data
pg1-user=
=3Denterprisedb
pg1-port=
=3D5444
pg-version-forc=
e=3D15
Refer t= o following edb documentation

=C2=A0
OR=C2=A0 = =C2=A0Am I doing a waste exercise=C2=A0 [if pgbackrest won't go ahead w= ith EDB ] ?


Any hints much apprecia= ted.=C2=A0

Thank you,
Krishane


ERROR:
root@uaterssdrservice01 ~]#= sudo -u postgres pgbackrest --stanza=3DOD_DM2 --log-level-console=3Dinfo = =C2=A0stanza-create
2024-07-17 17:42:13.935 P00 =C2=A0 INFO: stanza-crea= te command begin 2.52.1: --exec-id=3D1301876-7e055256 --log-level-console= =3Dinfo --log-level-file=3Ddebug --pg1-path=3D/var/lib/pgsql/16/data --repo= 1-host=3D10.x.y.7 --repo1-host-user=3Dpostgres --stanza=3DOD_DM2
WARN: u= nable to check pg1: [DbConnectError] unable to connect to 'dbname=3D= 9;postgres' port=3D5432': connection to server on socket "/tmp= /.s.PGSQL.5432" failed: No such file or directory
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 Is the server running locally and accepting connections on that = socket?
ERROR: [056]: unable to find primary cluster - cannot proceed=C2=A0 =C2=A0 =C2=A0 =C2=A0HINT: are all available clusters in recovery?2024-07-17 17:42:13.936 P00 =C2=A0 INFO: stanza-create command end: abort= ed with exception [056]
[root@uaterssdrservice01 ~]#

<= /div>





--
Thanks
Azeem Subhani
--000000000000862904061d88ed1e--