Re: PgbackRest and EDB Query

On Fri, Jul 19, 2024 at 5:59 PM KK CHN <kkchn(dot)in(at)gmail(dot)com> wrote:

> Hi list,
>
> Thank you all for the great help and guidance, I am able to configure
> pgbackrest with EPAS-16 and a Repo server both separate machines..
> Password less auth also worked well. Backup and restore all fine.
>
> Query
> How can I make the Reposerver to host more than one EPAS-16 server
> instance's running on multiple nodes ?
>
Yes,
You have to add different stanzas for different servers.

Regards
Kashif Zeeshan

>
> 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(at)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(at)uaterssdrservice02 ~]#
>
> Thanks again
> Krishane
>
>
> On Fri, Jul 19, 2024 at 11:24 AM azeem subhani <azeemsd07(at)gmail(dot)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(at)backup-server
>>
>>
>>
>>
>> On Fri, Jul 19, 2024 at 10:06 AM Kashif Zeeshan <kashi(dot)zeeshan(at)gmail(dot)com>
>> wrote:
>>
>>> Hi
>>>
>>> On Thu, Jul 18, 2024 at 6:10 PM KK CHN <kkchn(dot)in(at)gmail(dot)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(at)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(at)EDB_Server_IP unable to
>>>> do password less auth( Its asking password for enterpridb(at)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(dot)zeeshan(at)gmail(dot)com>
>>>> wrote:
>>>>
>>>>> Hi
>>>>>
>>>>> On Wed, Jul 17, 2024 at 5:21 PM KK CHN <kkchn(dot)in(at)gmail(dot)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(at)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(at)uaterssdrservice01 ~]#
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>
>> --
>> Thanks
>> Azeem Subhani
>>
>