Re: PgbackRest PointTIme Recovery : server unable to start back - Mailing list pgsql-general

Hi KK CHN

Could you check server logs ?

Your service trace suggests that it started server and then failure happened

ul 26 09:48:49 service01 systemd[1]: Started EDB Postgres Advanced Server 16.
Jul 26 09:48:50 service01 systemd[1]: edb-as-16.service: Main process exited, code=exited, status=1/FAILURE

Regards,

Ikram

On Fri, Jul 26, 2024 at 10:04 AM KK CHN <kkchn.in@gmail.com> wrote:

List, 

Reference: https://pgbackrest.org/user-guide-rhel.html#pitr

I am following the   PTR  on RHEL9 EPAS16.

I am able to do a  backup(Full, diff and incr)  and   restore from a full backup  and restart of EPAS16 works fine. 

But when I do an incremental backup  after doing the   procedures mentioned in the PTR section of the above  reference link and  try  restoring the EDB database from the INCR backup   and then starting up the  EPAS16 the server  always results in dead state 

 My repo server is another machine.  If  I do  a  full restore  on the DB server  ( sudo -u enterprisedb pgbackrest --stanza=Demo_Repo  --delta restore) it works  and the server starts without any issue.  

Restoring  from  Incremental backup tty output shows successful but edb service start  results in  failure. 

Any help is much appreciated. 

Krishane. 

STEPS followed:

after dropping  the table  pg-primary ⇒ Drop the important table ([section]

stopped the EDB server.

$ sudo -u enterprisedb pgbackrest --stanza=Demo_Repo    --delta  --set=20240719-122703F_20240724-094727I --target-timeline=current --type=time "--target=2024-07-24 09:44:01.3255+05:30" --target-action=promote  restore

.....................................................

2024-07-26 09:48:06.343 P00   INFO: restore command end: completed successfully (1035ms)

But    

[root@rservice01 ~]# sudo systemctl start edb-as-16.service
[root@service01 ~]# sudo systemctl status edb-as-16.service
× edb-as-16.service - EDB Postgres Advanced Server 16
     Loaded: loaded (/etc/systemd/system/edb-as-16.service; disabled; preset: disabled)
     Active: failed (Result: exit-code) since Fri 2024-07-26 09:48:50 IST; 8s ago
   Duration: 242ms
    Process: 41903 ExecStartPre=/usr/edb/as16/bin/edb-as-16-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
    Process: 41908 ExecStart=/usr/edb/as16/bin/edb-postgres -D ${PGDATA} (code=exited, status=1/FAILURE)
   Main PID: 41908 (code=exited, status=1/FAILURE)
        CPU: 331ms

Jul 26 09:48:48 service01 systemd[1]: Starting EDB Postgres Advanced Server 16...
Jul 26 09:48:48 service01 edb-postgres[41908]: 2024-07-26 09:48:48 IST LOG:  redirecting log output to logging collector process
Jul 26 09:48:48 service01 edb-postgres[41908]: 2024-07-26 09:48:48 IST HINT:  Future log output will appear in directory "log".
Jul 26 09:48:49 service01 systemd[1]: Started EDB Postgres Advanced Server 16.
Jul 26 09:48:50 service01 systemd[1]: edb-as-16.service: Main process exited, code=exited, status=1/FAILURE
Jul 26 09:48:50 service01 systemd[1]: edb-as-16.service: Killing process 41909 (edb-postgres) with signal SIGKILL.
Jul 26 09:48:50 service01 systemd[1]: edb-as-16.service: Failed with result 'exit-code'.
[root@service01 ~]#

why is it unable to perform a restore and recovery  from an incr  backup ?

On The Repo Server

[root@service02 ~]#  sudo -u postgres pgbackrest --stanza=Demo_Repo info
stanza: Demo_Repo
    status: ok
    cipher: aes-256-cbc

    db (current)
        wal archive min/max (16): 000000020000000000000021/0000000B0000000000000041

        full backup: 20240719-122703F
            timestamp start/stop: 2024-07-19 12:27:03+05:30 / 2024-07-19 12:27:06+05:30
            wal start/stop: 00000002000000000000002A / 00000002000000000000002A
            database size: 61.7MB, database backup size: 61.7MB
            repo1: backup size: 9.6MB

        incr backup: 20240719-122703F_20240719-123353I
            timestamp start/stop: 2024-07-19 12:33:53+05:30 / 2024-07-19 12:33:56+05:30
            wal start/stop: 00000002000000000000002C / 00000002000000000000002C
            database size: 61.7MB, database backup size: 6.4MB
            repo1: backup size: 6.2KB
            backup reference list: 20240719-122703F

        diff backup: 20240719-122703F_20240719-123408D
            timestamp start/stop: 2024-07-19 12:34:08+05:30 / 2024-07-19 12:34:10+05:30
            wal start/stop: 00000002000000000000002E / 00000002000000000000002E
            database size: 61.7MB, database backup size: 6.4MB
            repo1: backup size: 6.4KB
            backup reference list: 20240719-122703F

        incr backup: 20240719-122703F_20240723-110212I
            timestamp start/stop: 2024-07-23 11:02:12+05:30 / 2024-07-23 11:02:15+05:30
            wal start/stop: 000000070000000000000038 / 000000070000000000000038
            database size: 48MB, database backup size: 6.4MB
            repo1: backup size: 9.8KB
            backup reference list: 20240719-122703F, 20240719-122703F_20240719-123408D

        incr backup: 20240719-122703F_20240723-141818I
            timestamp start/stop: 2024-07-23 14:18:18+05:30 / 2024-07-23 14:18:22+05:30
            wal start/stop: 00000008000000000000003C / 00000008000000000000003C
            database size: 75.4MB, database backup size: 33.8MB
            repo1: backup size: 4.7MB
            backup reference list: 20240719-122703F, 20240719-122703F_20240719-123408D, 20240719-122703F_20240723-110212I

        incr backup: 20240719-122703F_20240724-094727I
            timestamp start/stop: 2024-07-24 09:47:27+05:30 / 2024-07-24 09:47:30+05:30
            wal start/stop: 00000009000000000000003D / 00000009000000000000003D
            database size: 62MB, database backup size: 11.5MB
            repo1: backup size: 72KB
            backup reference list: 20240719-122703F, 20240719-122703F_20240719-123408D, 20240719-122703F_20240723-110212I, 20240719-122703F_20240723-141818

--

Muhammad Ikram