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 1sXD8M-002RdB-3Z for pgsql-general@arkaria.postgresql.org; Fri, 26 Jul 2024 05:04:50 +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 1sXD8K-005KJt-7N for pgsql-general@arkaria.postgresql.org; Fri, 26 Jul 2024 05:04:48 +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 1sXD8J-005KJl-Pe for pgsql-general@lists.postgresql.org; Fri, 26 Jul 2024 05:04:47 +0000 Received: from mail-yw1-x112e.google.com ([2607:f8b0:4864:20::112e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sXD8G-001Vsp-RA for pgsql-general@postgresql.org; Fri, 26 Jul 2024 05:04:47 +0000 Received: by mail-yw1-x112e.google.com with SMTP id 00721157ae682-65f9e25fffaso17339417b3.3 for ; Thu, 25 Jul 2024 22:04:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1721970283; x=1722575083; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=+3Yz2TWmYedAFTW1UQiel/Rt044hmOUxniWJ4EdaUSE=; b=e6mv8NnvI28HRzwuh9eCAFiJ/pycfv5WpmmlKYaLMoYPo5nk7Gfpb7IJrnYI9KhYyn SSfP6mcydOuy8Mp5OGxZNUbJVQ6dU4+eO39TGi86eXAMvY46G68QelVmnXgWtqd7/C6Z xMjAvwY+vJ+Xv2pe0sTIV/eRXTREqlNJAtXzhXmTOt74ZhZpG2gMGj2htiqviTaicd3e mT8LXjn2GXY/wozxjQw39AExIWJbVOcn6LlhjjKAQu1Ov6piKrsoBMzvqGS+WK1ATxws CjspREO2JCLi+EXhS1u15ZA1ZvQuycp+sRJ4d6q412B1zrDoXEKp937b/QHlXzg21hyc r7FQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1721970283; x=1722575083; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=+3Yz2TWmYedAFTW1UQiel/Rt044hmOUxniWJ4EdaUSE=; b=wOPo6JPuuNRPCxbXq3Iyq2U8e2Aw0KUOk1berHhdO6eQ0kzEINw9a8omn+cUa8HXLk cWId4q93hsAj3V/QxkPm3c9TbCYk1fr48ra5o6z3Ol709bdXzjAMZctfZTVw4QBiqlv3 jAjJKlXh6+ipuPbraB3l+ac8QNpGacK7PYPOsw9rllmA+YqWA9GCW6vNrjYkw8fzdtA8 7GuShtekrU5+QXakmePG55ZC7KXRK0qptdVlvWr1gS8d7QgEsyQW/vnmsfylSJTVdM1a gr47UvtEdTgPTedQnY2Z7dcZnsWBj7RU9KqlmdCHuV1Q/nScTULdcMpqa5L6tEYZ1Sr4 4B5w== X-Gm-Message-State: AOJu0YwO6Anq+pVbJoTupattdli3KfeVlLjScwwC97xk67NWhnCd5AeD YNPKW+qYAFKfs5E3Dim5cwySHh/3Bquh33RzCElxTOfc5U18jeXWo111aeSRpphLnRo74Fshvpd Y5reEJW9Pzljgb/ejxXSy2uep8ufa2gpO X-Google-Smtp-Source: AGHT+IHj68xRsS/VAiwwTNoh0qflc53rG7K6aulNtH/+0TZFqVaUzBexMAQvCKDY0ghhNbFcC2fmG/hqd7nvWTo4CUU= X-Received: by 2002:a05:690c:630b:b0:627:e43f:7dd4 with SMTP id 00721157ae682-67515f8b45bmr53050597b3.45.1721970282505; Thu, 25 Jul 2024 22:04:42 -0700 (PDT) MIME-Version: 1.0 From: KK CHN Date: Fri, 26 Jul 2024 10:43:38 +0530 Message-ID: Subject: PgbackRest PointTIme Recovery : server unable to start back To: pgsql-general Content-Type: multipart/alternative; boundary="00000000000000ad22061e1f733e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000000ad22061e1f733e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=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 table ([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-dir ${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 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-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 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 --00000000000000ad22061e1f733e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
List,=C2=A0

I am following the=C2=A0 =C2=A0= PTR=C2=A0 on RHEL9 EPAS16.
I am able to do a=C2=A0 backup(Ful= l, diff and incr)=C2=A0 and=C2=A0 =C2=A0restore from a full backup=C2=A0 an= d restart of EPAS16 works fine.=C2=A0
<= br>
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 refere= nce 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 EPAS16 the server=C2=A0 a= lways results in dead state=C2=A0

=C2=A0My repo se= rver 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 pgbackrest --stanza=3DDem= o_Repo=C2=A0 --delta restore) it works=C2=A0 and the server starts without = any issue.=C2=A0=C2=A0
Restoring=C2=A0 from=C2=A0 Incremental bac= kup tty output shows successful but edb service start=C2=A0 results in=C2= =A0 failure.=C2=A0

Any help is much appreciate= d.=C2=A0

Krishane.=C2=A0

=


STEPS followed:

=
after dropping=C2=A0 the table=C2=A0 pg-primary=C2= =A0= =E2=87=92=C2=A0Drop the important table ([section]
stopped the E= DB server.

$ sudo -u enterprisedb pgbackres= t --stanza=3DDemo_Repo=C2=A0 =C2=A0 --delta=C2=A0 --set=3D20240719-122703F_= 20240724-094727I --target-timeline=3Dcurrent --type=3Dtime "--target= =3D2024-07-24 09:44:01.3255+05:30" --target-action=3Dpromote =C2=A0res= tore
.....................................................
<= div>
2024-07-26 09:48:06.343 P00 =C2=A0 INFO: restore command end: compl= eted successfully (1035ms)


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

[root@rservice01 ~]# sudo syst= emctl start edb-as-16.service
[root@service01 ~]# sudo systemctl status = edb-as-16.service
=C3=97 edb-as-16.service - EDB Postgres Advanced Serve= r 16
=C2=A0 =C2=A0 =C2=A0Loaded: loaded (/etc/systemd/system/edb-as-16.s= ervice; disabled; preset: disabled)
=C2=A0 =C2=A0 =C2=A0Active: faile= d (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/u= sr/edb/as16/bin/edb-as-16-check-db-dir ${PGDATA} (code=3Dexited, status=3D0= /SUCCESS)
=C2=A0 =C2=A0 Process: 41908 ExecStart=3D/usr/edb/as16/bin/edb= -postgres -D ${PGDATA} (code=3Dexited, status=3D1/FAILURE)
=C2=A0 =C2=A0= Main PID: 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 ED= B Postgres 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 loggin= g collector process
Jul 26 09:48:48 service01 edb-postgres[41908]: 2024-= 07-26 09:48:48 IST HINT: =C2=A0Future log output will appear in directory &= quot;log".
Jul 26 09:48:49 service01 systemd[1]: Started EDB Postgr= es Advanced Server 16.
Jul 26 09:48:50 service01 systemd[1]: edb-as-16.s= ervice: 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=C2=A0to perform a restore= and recovery=C2=A0 from an incr=C2=A0 backup ?







On The Repo Server
[root@service02 ~]# =C2=A0sud= o -u postgres 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= ): 000000020000000000000021/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:06+05:30
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 wal start/stop:= 00000002000000000000002A / 00000002000000000000002A
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 database size: 61.7MB, database backup size: 61.7M= B
=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-123= 353I
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 timestamp start/stop: 202= 4-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 / 00000002000= 000000000002C
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 database size: 6= 1.7MB, 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 star= t/stop: 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
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 backup reference list: 202= 40719-122703F

=C2=A0 =C2=A0 =C2=A0 =C2=A0 incr backup: 20240719-1227= 03F_20240723-110212I
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 timestamp= start/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: 00000007000000000000= 0038 / 000000070000000000000038
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 database 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-1227= 03F_20240719-123408D

=C2=A0 =C2=A0 =C2=A0 =C2=A0 incr backup: 202407= 19-122703F_20240723-141818I
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ti= mestamp 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: 000000080000000= 00000003C / 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


--00000000000000ad22061e1f733e--