PgbackRest and EDB Query

OR Am I doing a waste exercise [if pgbackrest won't go ahead with EDB ]
?

Any hints much appreciated.

Thank you,
Krishane

ERROR:
root@uaterssdrservice01 ~]# sudo -u postgres pgbackrest --stanza=OD_DM2
--log-level-console=info stanza-create
2024-07-17 17:42:13.935 P00 INFO: stanza-create command begin 2.52.1:
--exec-id=1301876-7e055256 --log-level-console=info --log-level-file=debug
--pg1-path=/var/lib/pgsql/16/data --repo1-host=10.x.y.7
--repo1-host-user=postgres --stanza=OD_DM2
WARN: unable to check pg1: [DbConnectError] unable to connect to
'dbname='postgres' port=5432': 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 ~]#

Hi

On Wed, Jul 17, 2024 at 5:21 PM KK CHN <kkchn.in@gmail.com> wrote:

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 the
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 = edb instead of postgres, and user as enterpisedb instead of
postgres how to specify these changes in the stanza creation step if EDB
Supports pgbackrest tool ?

You can enter this connection information in the PbBackRest Conf file for
the stanza you create for your EDB Instance.

e.g

[global]repo1-path=/var/lib/edb/as15/backups
[demo]pg1-path=/var/lib/edb/as15/datapg1-user=enterprisedbpg1-port=5444pg-version-force=15

Refer to following edb documentation

https://www.enterprisedb.com/docs/supported-open-source/pgbackrest/03-quick_start/

OR Am I doing a waste exercise [if pgbackrest won't go ahead with EDB
] ?

Any hints much appreciated.

Thank you,
Krishane

ERROR:
root@uaterssdrservice01 ~]# sudo -u postgres pgbackrest --stanza=OD_DM2
--log-level-console=info stanza-create
2024-07-17 17:42:13.935 P00 INFO: stanza-create command begin 2.52.1:
--exec-id=1301876-7e055256 --log-level-console=info --log-level-file=debug
--pg1-path=/var/lib/pgsql/16/data --repo1-host=10.x.y.7
--repo1-host-user=postgres --stanza=OD_DM2
WARN: unable to check pg1: [DbConnectError] unable to connect to
'dbname='postgres' port=5432': 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 ~]#

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 for
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 PM Kashif Zeeshan <kashi.zeeshan@gmail.com>
wrote:

Hi

On Wed, Jul 17, 2024 at 5:21 PM KK CHN <kkchn.in@gmail.com> wrote:

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 the
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 = edb instead of postgres, and user as enterpisedb instead of
postgres how to specify these changes in the stanza creation step if EDB
Supports pgbackrest tool ?

You can enter this connection information in the PbBackRest Conf file for
the stanza you create for your EDB Instance.

e.g

[global]repo1-path=/var/lib/edb/as15/backups
[demo]pg1-path=/var/lib/edb/as15/datapg1-user=enterprisedbpg1-port=5444pg-version-force=15

Refer to following edb documentation

https://www.enterprisedb.com/docs/supported-open-source/pgbackrest/03-quick_start/

OR Am I doing a waste exercise [if pgbackrest won't go ahead with EDB
] ?

Any hints much appreciated.

Thank you,
Krishane

ERROR:
root@uaterssdrservice01 ~]# sudo -u postgres pgbackrest --stanza=OD_DM2
--log-level-console=info stanza-create
2024-07-17 17:42:13.935 P00 INFO: stanza-create command begin 2.52.1:
--exec-id=1301876-7e055256 --log-level-console=info --log-level-file=debug
--pg1-path=/var/lib/pgsql/16/data --repo1-host=10.x.y.7
--repo1-host-user=postgres --stanza=OD_DM2
WARN: unable to check pg1: [DbConnectError] unable to connect to
'dbname='postgres' port=5432': 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 ~]#

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 PM Kashif Zeeshan <kashi.zeeshan@gmail.com>
wrote:

Hi

On Wed, Jul 17, 2024 at 5:21 PM KK CHN <kkchn.in@gmail.com> wrote:

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 the
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 = edb instead of postgres, and user as enterpisedb instead of
postgres how to specify these changes in the stanza creation step if EDB
Supports pgbackrest tool ?

You can enter this connection information in the PbBackRest Conf file for
the stanza you create for your EDB Instance.

e.g

[global]repo1-path=/var/lib/edb/as15/backups
[demo]pg1-path=/var/lib/edb/as15/datapg1-user=enterprisedbpg1-port=5444pg-version-force=15

Refer to following edb documentation

https://www.enterprisedb.com/docs/supported-open-source/pgbackrest/03-quick_start/

OR Am I doing a waste exercise [if pgbackrest won't go ahead with EDB
] ?

Any hints much appreciated.

Thank you,
Krishane

ERROR:
root@uaterssdrservice01 ~]# sudo -u postgres pgbackrest --stanza=OD_DM2
--log-level-console=info stanza-create
2024-07-17 17:42:13.935 P00 INFO: stanza-create command begin 2.52.1:
--exec-id=1301876-7e055256 --log-level-console=info --log-level-file=debug
--pg1-path=/var/lib/pgsql/16/data --repo1-host=10.x.y.7
--repo1-host-user=postgres --stanza=OD_DM2
WARN: unable to check pg1: [DbConnectError] unable to connect to
'dbname='postgres' port=5432': 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 ~]#

Hi,

passwordless connection can be established using ssh key, and when you
don't specify the ssh key in command using -i switch:* -i
/path/to/your/private/key*
You simply need to set the SSH key as the default key which I have
explained earlier, how to do that.

As you are currently trying through following command, without specifying
an ssh key for passwordless connection.

From the EDB Postgres Advanced Server nodes
$ sudo -u enterprisedb ssh pgbackrest@backup-server

On Fri, Jul 19, 2024 at 10:06 AM Kashif Zeeshan <kashi.zeeshan@gmail.com>
wrote:

Hi

On Thu, Jul 18, 2024 at 6:10 PM KK CHN <kkchn.in@gmail.com> 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 for
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 )

Please make sure that the passwordless connection is made between both
from EDB Server to Repo Server and from Repo Server to EDB Server.
For this you need to generate the ssh keys on both EDB server abd Repo
Servers and copy the id_rsa.pub from EDB Server to Repo Server and vise
versa.
I hope this helps.

Regards
Kashif Zeeshan

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 PM Kashif Zeeshan <kashi.zeeshan@gmail.com>
wrote:

Hi

On Wed, Jul 17, 2024 at 5:21 PM KK CHN <kkchn.in@gmail.com> wrote:

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
the 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 = edb instead of postgres, and user as enterpisedb instead of
postgres how to specify these changes in the stanza creation step if EDB
Supports pgbackrest tool ?

You can enter this connection information in the PbBackRest Conf file
for the stanza you create for your EDB Instance.

e.g

[global]repo1-path=/var/lib/edb/as15/backups
[demo]pg1-path=/var/lib/edb/as15/datapg1-user=enterprisedbpg1-port=5444pg-version-force=15

Refer to following edb documentation

https://www.enterprisedb.com/docs/supported-open-source/pgbackrest/03-quick_start/

OR Am I doing a waste exercise [if pgbackrest won't go ahead with
EDB ] ?

Any hints much appreciated.

Thank you,
Krishane

ERROR:
root@uaterssdrservice01 ~]# sudo -u postgres pgbackrest
--stanza=OD_DM2 --log-level-console=info stanza-create
2024-07-17 17:42:13.935 P00 INFO: stanza-create command begin
2.52.1: --exec-id=1301876-7e055256 --log-level-console=info
--log-level-file=debug --pg1-path=/var/lib/pgsql/16/data
--repo1-host=10.x.y.7 --repo1-host-user=postgres --stanza=OD_DM2
WARN: unable to check pg1: [DbConnectError] unable to connect to
'dbname='postgres' port=5432': 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 ~]#

--
Thanks
Azeem Subhani

Having only one /etg/pgbakrest/pgbackrest.conf file on the Repo Server
how to specify stanza name and global for multiple EPAS servers?
My Repo Server: cat /etc/pgbackrest/pgbackrest.conf
####################################################################
[Demo_Repo]
pg1-host=10.20.20.6
pg1-host-user=enterprisedb
pg1-path=/var/lib/edb/as16/data
pg-version-force=16

[global]
#### about the repository

repo1-path=/var/lib/edb_BackupRepo

repo1-retention-full=2
repo1-cipher-type=aes-256-cbc

repo1-cipher-pass=0oahu5f5dvH7eD4TI1eBEl8Vpn14hWEmgLGuXgpUHo9R2VQKCw6Sm99FnOfHBY
process-max=5
log-level-console=info
log-level-file=debug
start-fast=y
delta=y
repo1-block=y
repo1-bundle=y
[global:archive-push]
compress-level=3
######################################################################

1. So if there are multiple EPAS servers running on different
nodes 10.20.20.7, 10.20.20.8, etc. how to specify the stanzas and
globals for each EPAS server in single ( /etc/pgbackrest/pgbackrest.conf
) on Repo server ?

2. Say there are X numbers (say 10 EPAS servers from different geo
locations) of EPAS servers each has a daily growth of aprox 1 GB/day
then what should be the connectivity capacity parameters need to
consider to cater the archiving and replication by pgbackrest in a
production environment to the repo server ?

3. Also what will be the best backup configuration in a crontab for
achieving maximum RPO ? I mean zero data loss ? ( incr or diff
repetition intervals ?) here my sample crontab, only for full and diff
(in lab setup) but for production env and for near zero data lost what
configs needed in cron ?

my sample cron here.
[root@RepoServer ~]# crontab -u postgres -l
30 06 * * 0 pgbackrest --type=full --stanza=Demo2 backup
// only on sundays

04 16 * * 1-6 pgbackrest --type=diff --stanza=Demo2 backup
// on everyday diff
[root@uaterssdrservice02 ~]#

Thanks again
Krishane

On Fri, Jul 19, 2024 at 11:24 AM azeem subhani <azeemsd07@gmail.com>
wrote:

Hi,

passwordless connection can be established using ssh key, and when you
don't specify the ssh key in command using -i switch:* -i
/path/to/your/private/key*
You simply need to set the SSH key as the default key which I have
explained earlier, how to do that.

As you are currently trying through following command, without specifying
an ssh key for passwordless connection.

From the EDB Postgres Advanced Server nodes
$ sudo -u enterprisedb ssh pgbackrest@backup-server

On Fri, Jul 19, 2024 at 10:06 AM Kashif Zeeshan <kashi.zeeshan@gmail.com>
wrote:

Hi

On Thu, Jul 18, 2024 at 6:10 PM KK CHN <kkchn.in@gmail.com> 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 for
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 )

Please make sure that the passwordless connection is made between both
from EDB Server to Repo Server and from Repo Server to EDB Server.
For this you need to generate the ssh keys on both EDB server abd Repo
Servers and copy the id_rsa.pub from EDB Server to Repo Server and vise
versa.
I hope this helps.

Regards
Kashif Zeeshan

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 PM Kashif Zeeshan <kashi.zeeshan@gmail.com>
wrote:

Hi

On Wed, Jul 17, 2024 at 5:21 PM KK CHN <kkchn.in@gmail.com> wrote:

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
the 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 = edb instead of postgres, and user as enterpisedb instead of
postgres how to specify these changes in the stanza creation step if EDB
Supports pgbackrest tool ?

You can enter this connection information in the PbBackRest Conf file
for the stanza you create for your EDB Instance.

e.g

[global]repo1-path=/var/lib/edb/as15/backups
[demo]pg1-path=/var/lib/edb/as15/datapg1-user=enterprisedbpg1-port=5444pg-version-force=15

Refer to following edb documentation

https://www.enterprisedb.com/docs/supported-open-source/pgbackrest/03-quick_start/

OR Am I doing a waste exercise [if pgbackrest won't go ahead with
EDB ] ?

Any hints much appreciated.

Thank you,
Krishane

ERROR:
root@uaterssdrservice01 ~]# sudo -u postgres pgbackrest
--stanza=OD_DM2 --log-level-console=info stanza-create
2024-07-17 17:42:13.935 P00 INFO: stanza-create command begin
2.52.1: --exec-id=1301876-7e055256 --log-level-console=info
--log-level-file=debug --pg1-path=/var/lib/pgsql/16/data
--repo1-host=10.x.y.7 --repo1-host-user=postgres --stanza=OD_DM2
WARN: unable to check pg1: [DbConnectError] unable to connect to
'dbname='postgres' port=5432': 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 ~]#

--
Thanks
Azeem Subhani