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 1sXDIp-002Sbz-R4 for pgsql-general@arkaria.postgresql.org; Fri, 26 Jul 2024 05:15:39 +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 1sXDIo-005YMZ-8H for pgsql-general@arkaria.postgresql.org; Fri, 26 Jul 2024 05:15:38 +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 1sXDIn-005YKK-Oa for pgsql-general@lists.postgresql.org; Fri, 26 Jul 2024 05:15:37 +0000 Received: from mail-lj1-x22c.google.com ([2a00:1450:4864:20::22c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sXDIk-001Vyx-Nf for pgsql-general@postgresql.org; Fri, 26 Jul 2024 05:15:37 +0000 Received: by mail-lj1-x22c.google.com with SMTP id 38308e7fff4ca-2ef248ab2aeso12321541fa.0 for ; Thu, 25 Jul 2024 22:15:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1721970933; x=1722575733; 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=yTWJ54FzoeygAo5yB/WmaIMsU1m+P8ZtbYubxLGXN1E=; b=KGjSYk5RsRL0qi3Pg+GszJg6gWrKhryHh0gCq6Z0cjX/3dV+Rz1sdTwyo8RKQxd92+ NI27CGTP6DRTwnRecA1NCWYmvNRlDXEZC2ooEUa0kCxZQ0fl6LXiIY95yRSfYIiM8MJ/ CgfF4rpXodunGtMV8Sh2lVST72z8IK4zPbs25hMnmYI7C3+KPr54DaX/Ua89tc3zMCXB HPM2GMaC3YgPtKojarBT0o923RdhPc7RFwnFU2ghtSHFgio1PWy4DjyvJ0RrBemF66Do 2RUdNMa13Iyao7axFjP0NDyQsEOzQToy8chhrESIygCzuDoWE60G2TqJzQETO5jZ7Hxr LxRA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1721970933; x=1722575733; 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=yTWJ54FzoeygAo5yB/WmaIMsU1m+P8ZtbYubxLGXN1E=; b=oYf/vJrojzOfeGiUNWJmCXs+IFl04E8NrUGcL2sKz7P7nK5Hk+24NdPIZaIpz0lLf/ 6I5OgpoBW7rq8TdwBK6nOkI/E88Dyzl7ocGs/2ULoUx6YyxuM/ta8jAr9wGTRirATQ/n dXuB99cKaD2w7iZdMyhn6MkS1gKZwYslXMspD9lyyhlRqCVxghZZx0vHzDL+3F5ONQQk YE4VfqS6d2NysWESH3YbTVD4NjXGK7xIzfCe00ljduwUy4Vt0M8Rv/ADvctSrZNI1PXD Ntx5spN8jAjsCV8bHM2U/nYNzhFh752ZeF3ndCj4NO2yJVY0StYu+hB3icwxfeHHCj0f da4w== X-Gm-Message-State: AOJu0Ywi1zI002UZ71RzblhdPh9UTqbEsxiVGC+iWWyZXNItBN7XU8O0 1oRbok1C8by+yYqWIxqYSw+SWkeeJK+mBtwc1o8pMXGvwAixLbZWoZF9R3ats3FZ44O8O4WGP1P mTOKNQx9CUv+l9JsE2ant03y/1t2hBVME X-Google-Smtp-Source: AGHT+IG93kEj+KLAbCoh1xL9h9ISFTohtlTMgp2jnO7fy8GZ1oNxJT/hcVDCIPLlYAOK14IG2trY8m/riMCb5abnu2o= X-Received: by 2002:a05:6512:2809:b0:52e:d0f8:2d43 with SMTP id 2adb3069b0e04-52fd602b6d4mr3549169e87.17.1721970932357; Thu, 25 Jul 2024 22:15:32 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Muhammad Ikram Date: Fri, 26 Jul 2024 10:15:15 +0500 Message-ID: Subject: Re: PgbackRest PointTIme Recovery : server unable to start back To: KK CHN Cc: pgsql-general Content-Type: multipart/alternative; boundary="000000000000bca2b6061e1f998c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000bca2b6061e1f998c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi KK CHN Could you check server logs ? Your service trace suggests that it started server and then failure happene= d 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=3Dexited, status=3D1/FAILURE Regards, Ikram On Fri, Jul 26, 2024 at 10:04=E2=80=AFAM KK CHN 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 th= e > 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=3DDemo_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 *=E2=87=92* Drop the important tabl= e > ([section] > stopped the EDB server. > > $ sudo -u enterprisedb pgbackrest --stanza=3DDemo_Repo --delta > --set=3D20240719-122703F_20240724-094727I --target-timeline=3Dcurrent > --type=3Dtime "--target=3D2024-07-24 09:44:01.3255+05:30" > --target-action=3Dpromote 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 > =C3=97 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=3D/usr/edb/as16/bin/edb-as-16-check-db-di= r > ${PGDATA} (code=3Dexited, status=3D0/SUCCESS) > Process: 41908 ExecStart=3D/usr/edb/as16/bin/edb-postgres -D ${PGDATA= } > (code=3Dexited, status=3D1/FAILURE) > Main PID: 41908 (code=3Dexited, status=3D1/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 Serve= r > 16. > Jul 26 09:48:50 service01 systemd[1]: edb-as-16.service: Main process > exited, code=3Dexited, status=3D1/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=3DDemo_Repo inf= o > 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 > > > > --=20 Muhammad Ikram --000000000000bca2b6061e1f998c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi KK CHN

Could you check se= rver logs ?
Your service trace suggests that it started server an= d then failure happened

ul 26 09:48:49 service01 s= ystemd[1]: Started EDB Postgres Advanced Server 16.
Jul 26 09:48:50 serv= ice01 systemd[1]: edb-as-16.service: Main process exited, code=3Dexited, st= atus=3D1/FAILURE



Reg= ards,
Ikram


On Fri, Jul 26, 2024 at 10:04=E2= =80=AFAM KK CHN <kkchn.in@gmail.co= m> wrote:
List,=C2=A0

I am followi= ng the=C2=A0 =C2=A0PTR=C2=A0 on RHEL9 EPAS16.
I am able to do= a=C2=A0 backup(Full, diff and incr)=C2=A0 and=C2=A0 =C2=A0restore from a f= ull backup=C2=A0 and restart of EPAS16 works fine.=C2=A0

But when I do an incremental backup=C2=A0 = after doing the=C2=A0 =C2=A0procedures mentioned in the PTR section of the = above=C2=A0 reference link and=C2=A0 try=C2=A0 restoring the EDB database= =C2=A0from the INCR backup=C2=A0=C2=A0 and then starting up the=C2=A0 EPAS1= 6 the server=C2=A0 always results in dead state=C2=A0

<= div>=C2=A0My repo server is another machine.=C2=A0 If=C2=A0 I do=C2=A0 a=C2= =A0 full restore=C2=A0 on the DB server=C2=A0 ( sudo -u enterprisedb pgback= rest --stanza=3DDemo_Repo=C2=A0 --delta restore) it works=C2=A0 and the ser= ver starts without any issue.=C2=A0=C2=A0
Restoring=C2=A0 from=C2= =A0 Incremental backup tty output shows successful but edb service start=C2= =A0 results in=C2=A0 failure.=C2=A0

Any help i= s much appreciated.=C2=A0

Krishane.=C2=A0




STEPS followe= d:

after dropping=C2=A0 the table=C2=A0 pg-primary= =C2=A0<= /span>=E2= =87=92=C2=A0Drop the important table ([section]
stopped the EDB = server.

$ sudo -u enterprisedb pgbackrest -= -stanza=3DDemo_Repo=C2=A0 =C2=A0 --delta=C2=A0 --set=3D20240719-122703F_202= 40724-094727I --target-timeline=3Dcurrent --type=3Dtime "--target=3D20= 24-07-24 09:44:01.3255+05:30" --target-action=3Dpromote =C2=A0restore<= /div>
.....................................................
<= br>2024-07-26 09:48:06.343 P00 =C2=A0 INFO: restore command end: completed = successfully (1035ms)


But=C2=A0= =C2=A0=C2=A0

[root@rservice01 ~]# sudo systemctl = start edb-as-16.service
[root@service01 ~]# sudo systemctl status edb-as= -16.service
=C3=97 edb-as-16.service - EDB Postgres Advanced Server 16=C2=A0 =C2=A0 =C2=A0Loaded: loaded (/etc/systemd/system/edb-as-16.service= ; disabled; preset: disabled)
=C2=A0 =C2=A0 =C2=A0Active: failed = (Result: exit-code) since Fri 2024-07-26 09:48:50 IST; 8s ago
=C2=A0 =C2= =A0Duration: 242ms
=C2=A0 =C2=A0 Process: 41903 ExecStartPre=3D/usr/edb/= as16/bin/edb-as-16-check-db-dir ${PGDATA} (code=3Dexited, status=3D0/SUCCES= S)
=C2=A0 =C2=A0 Process: 41908 ExecStart=3D/usr/edb/as16/bin/edb-postgr= es -D ${PGDATA} (code=3Dexited, status=3D1/FAILURE)
=C2=A0 =C2=A0Main PI= D: 41908 (code=3Dexited, status=3D1/FAILURE)
=C2=A0 =C2=A0 =C2=A0 =C2=A0= CPU: 331ms

Jul 26 09:48:48 service01 systemd[1]: Starting EDB Postg= res Advanced Server 16...
Jul 26 09:48:48 service01 edb-postgres[41908]:= 2024-07-26 09:48:48 IST LOG: =C2=A0redirecting log output to logging colle= ctor process
Jul 26 09:48:48 service01 edb-postgres[41908]: 2024-07-26 0= 9:48:48 IST HINT: =C2=A0Future log output will appear in directory "lo= g".
Jul 26 09:48:49 service01 systemd[1]: Started EDB Postgres Adva= nced Server 16.
Jul 26 09:48:50 service01 systemd[1]: edb-as-16.service:= Main process exited, code=3Dexited, status=3D1/FAILURE
Jul 26 09:48:50 = service01 systemd[1]: edb-as-16.service: Killing process 41909 (edb-postgre= s) 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=C2=A0to perform a restore and re= covery=C2=A0 from an incr=C2=A0 backup ?


<= /div>





<= /div>
On The Repo Server
[root@service02 ~]# =C2=A0sudo -u po= stgres pgbackrest --stanza=3DDemo_Repo info
stanza: Demo_Repo
=C2=A0 = =C2=A0 status: ok
=C2=A0 =C2=A0 cipher: aes-256-cbc

=C2=A0 =C2=A0= db (current)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 wal archive min/max (16): 0000= 00020000000000000021/0000000B0000000000000041

=C2=A0 =C2=A0 =C2=A0 = =C2=A0 full backup: 20240719-122703F
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 timestamp start/stop: 2024-07-19 12:27:03+05:30 / 2024-07-19 12:27:0= 6+05:30
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 wal start/stop: 000000= 02000000000000002A / 00000002000000000000002A
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 database size: 61.7MB, database backup size: 61.7MB
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 repo1: backup size: 9.6MB

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

=C2=A0 =C2=A0 =C2=A0 =C2=A0 = diff backup: 20240719-122703F_20240719-123408D
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 timestamp start/stop: 2024-07-19 12:34:08+05:30 / 2024-07= -19 12:34:10+05:30
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 wal start/s= top: 00000002000000000000002E / 00000002000000000000002E
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 database size: 61.7MB, database backup size: 6.= 4MB
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 repo1: backup size: 6.4KB<= br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 backup reference list: 2024071= 9-122703F

=C2=A0 =C2=A0 =C2=A0 =C2=A0 incr backup: 20240719-122703F_= 20240723-110212I
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 timestamp sta= rt/stop: 2024-07-23 11:02:12+05:30 / 2024-07-23 11:02:15+05:30
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 wal start/stop: 000000070000000000000038= / 000000070000000000000038
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 da= tabase size: 48MB, database backup size: 6.4MB
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 repo1: backup size: 9.8KB
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 backup reference list: 20240719-122703F, 20240719-122703F_202= 40719-123408D

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

=C2=A0 = =C2=A0 =C2=A0 =C2=A0 incr backup: 20240719-122703F_20240724-094727I
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 timestamp start/stop: 2024-07-24 09:= 47:27+05:30 / 2024-07-24 09:47:30+05:30
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 wal start/stop: 00000009000000000000003D / 000000090000000000000= 03D
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 database size: 62MB, datab= ase backup size: 11.5MB
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 repo1:= backup size: 72KB
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 backup refe= rence list: 20240719-122703F, 20240719-122703F_20240719-123408D, 20240719-1= 22703F_20240723-110212I, 20240719-122703F_20240723-141818




--
Muhammad Ikram

--000000000000bca2b6061e1f998c--