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 1sgil4-006hAV-TG for pgsql-general@arkaria.postgresql.org; Wed, 21 Aug 2024 10:40:07 +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 1sgil3-009Wo5-1k for pgsql-general@arkaria.postgresql.org; Wed, 21 Aug 2024 10:40:05 +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 1sgil2-009Wl8-Jb for pgsql-general@lists.postgresql.org; Wed, 21 Aug 2024 10:40:05 +0000 Received: from mail-yw1-x1130.google.com ([2607:f8b0:4864:20::1130]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sgikz-000lLz-Mn for pgsql-general@postgresql.org; Wed, 21 Aug 2024 10:40:04 +0000 Received: by mail-yw1-x1130.google.com with SMTP id 00721157ae682-690b6cbce11so63472007b3.2 for ; Wed, 21 Aug 2024 03:40:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1724236801; x=1724841601; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=jQgNFTJRKDWJZ5r9U/63xLGxnSV6zkPL+0HGktklaoU=; b=Y+KbCcnQykXycHFtQL/BguHwLwk+P1J60x1C4veurGUB19I1LiXlUpny+E1u2KeSHw 1wbfanVIbNwIFEYuBz/rgs4mIAnnZD86joZ4KghKFWVU8G826bYHBvEer0oVurcctF64 v1r6EZKt6OCYWWgGUZYEs+1PpXyFAF3YYhgvE+ASiu88tgBj9lrqlmQjFq4U8chOfO1w 4+G7G66uAGvZ+MG5w2GhV8VOsIMyfH3BfojX7TaHva2CLLp8MbStA4n1GBYgLKoRSrZl +W/ykq2k5Pj9QHzhDE+k1Ig1CYPmq6c/hbL4Bnf8Iiy6LFzqgg89aY/GB5fE2NiBJeP/ JvGQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724236801; x=1724841601; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=jQgNFTJRKDWJZ5r9U/63xLGxnSV6zkPL+0HGktklaoU=; b=vnegkeCXb8RzbEBXaP8/cAmRuKyQB11bmuaecddVeHzFf+l28BjsFxxlHqpXyvm60G /DhzyOHLB9GTULHChbBSPr40zQpNlPymlKODKDgPLB/tcdMCRFSVT4Hii6lMf8na8w2G dLUTitP9zFP1ATq1ydRRZOpY5N72oBuXUkev5TKHey3EV3oKmPBznTXP9wLX+UBso6ZQ 6SE/dv4892J4+hfmQfDLNJ9FvN2zNSFcvuJTegpJ/UzaU4aw1fN24/XZ6fGLYY6MuCNs ONYthZ6rxmfKmau+JpcPinIA3bHqbu1sfaLBSOkJSDJ9ddLl0j0AosCZGaoIBuUuSCvB 0PIw== X-Gm-Message-State: AOJu0YwWjk0YLtRQHc5BR8xuGqa3BqSa8O0vaefixEKCbReV03sYijTE u3Y/5qJPyt0si+/s2uNluVBFCI0It1fmMr06V/V6fXEiMExJ/KwozG/sEIrIGY6npQkbci+6pas alaGb1Wzmc8Y9YVGIriI76O0cCNRRx8fc X-Google-Smtp-Source: AGHT+IFkF4DQEO8S9Yc2qEY6ex4Ib7lrCehFfY33PZAIyEtJJphZgbtEtp895kQ+vinEoIykLb6JoSXWh2O58QJhheE= X-Received: by 2002:a05:690c:60c7:b0:650:859b:ec8d with SMTP id 00721157ae682-6c09c78684dmr21357737b3.10.1724236801279; Wed, 21 Aug 2024 03:40:01 -0700 (PDT) MIME-Version: 1.0 From: KK CHN Date: Wed, 21 Aug 2024 16:10:03 +0530 Message-ID: Subject: pgbackrest restore with a checkpoint and timestamp after the checkpoint To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000000c91e806202f2ae1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000c91e806202f2ae1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable List, Query: Can I perform a pgbackrest restore with the last backup diff or incr + further transactions in the WAL replayed to restore the transactions that happened after the last pgbackrest backup checkpoint ? Scenario: I am trying to perform this and unable to get a solution. I have 20th Aug 2024 A Differential backup as follows.. * diff backup: 20240820-152602F_20240820-160402D* * timestamp start/stop: 2024-08-20 16:04:02+05:30 / 2024-08-20 16:04:05+05:30* 1. Today (21st Aug 2024) I've performed a table drop as follows and noted the time stamps in BOLD highlighted edb=3D# \dt List of relations Schema | Name | Type | Owner --------+------------------+-------+-------------- public | foo | table | enterprisedb public | important_table | table | enterprisedb public | important_table2 | table | enterprisedb public | important_table4 | table | enterprisedb (4 rows) edb=3D# select now(); now ---------------------------------- * 21-AUG-24 13:58:31.611403 +05:30 // Before table drop * (1 row) *edb=3D# drop table important_table4;* DROP TABLE edb=3D# \dt List of relations Schema | Name | Type | Owner --------+------------------+-------+-------------- public | foo | table | enterprisedb public | important_table | table | enterprisedb public | important_table2 | table | enterprisedb (3 rows) edb=3D# select now(); now ---------------------------------- *21-AUG-24 13:58:58.379552 +05:30 //after table drop* (1 row) edb=3D# 2. Issue as follows ... When I do a restore with the above differential backup and time stamp of recovery upto 21-AUG-24 13:58:*48.611403*+05:30" it recovers the database and I am able to see the dropped table important_table4 recovered. Query: IF THIS IS NOT the expected result which I want.. I want the restored db without the deleted table !!!! So I am recording a time stamp after the table drop as seen above. But when I give the time stamp anything greater than 21-AUG-24 13:58:48.611403+05:30" (Eg : time stamp 13:58:49.611403+05:30) with an expectation that the restored db server must show the dropped state ( important_table4 not to present there ) . The edb restart always fails after pgbackrest restore with any value higher than timestamp 13:58:48.611403 Why ?? As per my understanding any restore referring to a checkpoint ( the differential backup taken as listed above) and a time stamp of todays after dropping the table important_table4 must replay the WAL files after the differential backup taken dated as seen above and upto the timestamp (todays)after dropping the importatn_table4. C*orrect me If I am wrong here ?* I am *expecting* to see the edb=3D# \dt without the dropped table " important_table4 " ( if the WAL replayed upto the timestamp as I specified, Is this possible ? ) . But this never gets me a successful restart of the edb server ? Here the output : [root@uaterssdrservice01 bin]# sudo -u enterprisedb pgbackrest --stanza=3DDemo --delta --set=3D*20240820-152602F_20240820-160402D* --target-timeline=3Dcurrent --type=3Dtime --target=3D"21-AUG-24 13:58:49.611403+05:30" --target-action=3Dpromote restore 2024-08-21 14:34:17.116 P00 INFO: restore command begin 2.52.1: --delta --exec-id=3D252857-6013404c --log-level-console=3Dinfo --log-level-file=3Dd= ebug --pg1-path=3D/var/lib/edb/as16/data --pg-version-force=3D16 --repo1-host=3D10.10.20.7 --repo1-host-user=3Dpostgres --set=3D20240820-152602F_20240820-160402D --spool-path=3D/var/spool/pgbackr= est --stanza=3DRepo --target=3D"21-AUG-24 13:58:49.611403+05:30" --target-action=3Dpromote --target-timeline=3Dcurrent --type=3Dtime 2024-08-21 14:34:17.469 P00 INFO: repo1: restore backup set 20240820-152602F_20240820-160402D, recovery will start at 2024-08-20 16:04:02 2024-08-21 14:34:17.470 P00 INFO: remove invalid files/links/paths from '/var/lib/edb/as16/data' 2024-08-21 14:34:18.274 P00 INFO: write updated /var/lib/edb/as16/data/postgresql.auto.conf 2024-08-21 14:34:18.277 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started) 2024-08-21 14:34:18.277 P00 INFO: restore size =3D 89.8MB, file total =3D= 2588 2024-08-21 14:34:18.278 P00 INFO:* restore command end: completed successfully* (1164ms) But Issue is as follows. [root@uaterssdrservice01 bin]# systemctl start edb-as-16.service (*No Errors in console*) [root@uaterssdrservice01 bin]# sudo -u enterprisedb psql edb psql: error: connection to server on socket "/tmp/.s.PGSQL.5444" failed: No such file or directory Is the server running locally and accepting connections on that socket? [root@uaterssdrservice01 bin]# Why the server restart always fails on restore with this time stamp ( greater than 21-AUG-24 13:58:49.611403+05:30 ) ?? Or I have to understand: Never can we restore =C3=A4 db server after th= e last checkpoint , and all other transactions that happened are lost forever ? or in my Repo server the WALs are not replicated properly ? What may be the issue ? EPAS16 on RHEL 9 and Repo Server RHEL9 both different VMs.. Pgbackrest 2. 52.1 Pls shed some light on this Thank you, Krishane --0000000000000c91e806202f2ae1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
List,=C2=A0


Q= uery:=C2=A0
Can I=C2=A0 perform a=C2=A0 pgbackrest restore=C2=A0w= ith=C2=A0 the last backup diff or incr +=C2=A0=C2=A0further=C2=A0transactio= ns in the WAL=C2=A0 replayed to restore the=C2=A0 transactions that happene= d after the last=C2=A0 pgbackrest backup checkpoint=C2=A0 =C2=A0?=C2=A0 =C2= =A0=C2=A0


Scenario:=C2=A0=C2=A0

I am trying to perform this and unable to get a solut= ion.=C2=A0

I have 20th Aug 2024 A Differential bac= kup as follows..

=C2=A0 =C2=A0 =C2=A0 =C2=A0 diff ba= ckup: 20240820-152602F_20240820-160402D
=C2=A0timestamp star= t/stop: 2024-08-20 16:04:02+05:30 / 2024-08-20 16:04:05+05:30
=


1. Today (21st Aug 2024)=C2=A0 I've = performed a=C2=A0 table drop=C2=A0 as follows and noted the time stamps in = BOLD highlighted=C2=A0


edb=3D# \dt<= br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 List of relation= s
=C2=A0Schema | =C2=A0 =C2=A0 =C2=A0 Name =C2=A0 =C2=A0 =C2=A0 | Type = =C2=A0| =C2=A0 =C2=A0Owner
--------+------------------+-------+---------= -----
=C2=A0public | foo =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0| table | enterprisedb
=C2=A0public | important_table =C2=A0| table |= enterprisedb
=C2=A0public | important_table2 | table | enterprisedb
= =C2=A0public | important_table4 | table | enterprisedb
(4 rows)

e= db=3D# select now();
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0now
----------------------------------
=C2=A021-AUG-24 13:58:31.611403 +05:30=C2=A0 // = Before=C2=A0 table drop=C2=A0
(1 row)

edb=3D# drop table important_table4;=
DROP TABLE
edb=3D# \dt
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 List of relations
=C2=A0Schema | =C2=A0 =C2=A0 =C2= =A0 Name =C2=A0 =C2=A0 =C2=A0 | Type =C2=A0| =C2=A0 =C2=A0Owner
--------= +------------------+-------+--------------
=C2=A0public | foo =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| table | enterprisedb
=C2=A0publi= c | important_table =C2=A0| table | enterprisedb
=C2=A0public | importan= t_table2 | table | enterprisedb
(3 rows)

edb=3D# select now();=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0now
------------= ----------------------
=C2=A021-AUG-24 13:58:58.379552 +05:30=C2=A0 //after table drop
(1 row)<= br>
edb=3D#=C2=A0


2.=C2=A0 Issue=C2=A0 as follows ...

When I do a=C2=A0 restore with the above dif= ferential backup and time stamp of recovery=C2=A0=C2=A0upto=C2=A021-AUG-24 = 13:58:48.611403+05:30"=C2=A0 it recover= s the database and I am able to see the dropped table=C2=A0 important_table= 4 recovered.=C2=A0=C2=A0


=C2=A0 Que= ry:=C2=A0 =C2=A0IF=C2=A0 THIS IS NOT the expected=C2=A0result=C2=A0 which I= want..=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 I want th= e restored=C2=A0 db without the deleted table !!!!=C2=A0=C2=A0


So I am=C2=A0 recording a time s= tamp=C2=A0 =C2=A0after the table drop as seen above.=C2=A0

But when I give the time stamp anything greater than=C2=A0 = =C2=A021-AUG-24 13:58:48.611403+05:30"=C2=A0 (= Eg : time stamp=C2=A0 13:58:49.611403+05:30)=C2=A0 = =C2=A0 with an=C2=A0 expectation=C2=A0 that the restored db server must sho= w the dropped state ( important_table4=C2=A0 =C2=A0not to present there ) .= =C2=A0

The=C2=A0 edb restart always fails after=C2= =A0 pgbackrest restore with any value higher than=C2=A0 timestamp=C2=A0 13:= 58:48.611403=C2=A0 =C2=A0 =C2=A0Why ??

=C2=A0As pe= r my understanding any restore referring to a checkpoint ( the differential= backup taken as=C2=A0 listed above)=C2=A0 and a time stamp of todays after= dropping the table important_table4 must=C2=A0 replay the WAL files after = the differential backup taken dated as seen above and upto the timestamp (t= odays)after dropping the importatn_table4.=C2=A0 =C2=A0 =C2=A0Correct me= If=C2=A0 I am wrong here=C2=A0 ?

I am expe= cting to see=C2=A0 =C2=A0the=C2=A0 =C2=A0 edb=3D# \dt=C2=A0 =C2=A0=C2= =A0
=C2=A0 =C2=A0 without the dropped table=C2=A0 " importan= t_table4 " ( if the WAL replayed upto the timestamp as I specified, Is= this possible ? ) .=C2=A0 =C2=A0But this never=C2=A0 gets me a successful = restart=C2=A0 of the edb server=C2=A0 ?


=

Here the output=C2=A0 =C2=A0:
=

[root@uaterssdrservice01 bin]# sudo -u enterp= risedb pgbackrest --stanza=3DDemo --delta --set=3D20240820-152602F_20240= 820-160402D =C2=A0--target-timeline=3Dcurrent --type=3Dtime =C2=A0--tar= get=3D"21-AUG-24 13:58:49.611403+05:30" -= -target-action=3Dpromote restore


2024-08-21 14= :34:17.116 P00 =C2=A0 INFO: restore command begin 2.52.1: --delta --exec-id= =3D252857-6013404c --log-level-console=3Dinfo --log-level-file=3Ddebug --pg= 1-path=3D/var/lib/edb/as16/data --pg-version-force=3D16 --repo1-host=3D10.1= 0.20.7 --repo1-host-user=3Dpostgres --set=3D20240820-152602F_20240820-16040= 2D --spool-path=3D/var/spool/pgbackrest --stanza=3DRepo --target=3D"21= -AUG-24 13:58:49.611403+05:30" --target-action=3Dpromote --target-time= line=3Dcurrent --type=3Dtime
2024-08-21 14:34:17.469 P00 =C2=A0 INFO: re= po1: restore backup set 20240820-152602F_20240820-160402D, recovery will st= art at 2024-08-20 16:04:02
2024-08-21 14:34:17.470 P00 =C2=A0 INFO: remo= ve invalid files/links/paths from '/var/lib/edb/as16/data'
2024-= 08-21 14:34:18.274 P00 =C2=A0 INFO: write updated /var/lib/edb/as16/data/po= stgresql.auto.conf
2024-08-21 14:34:18.277 P00 =C2=A0 INFO: restore glob= al/pg_control (performed last to ensure aborted restores cannot be started)=
2024-08-21 14:34:18.277 P00 =C2=A0 INFO: restore size =3D 89.8MB, file = total =3D 2588
2024-08-21 14:34:18.278 P00 =C2=A0 INFO: restore comma= nd end: completed successfully (1164ms)

But=C2= =A0 Issue is=C2=A0 as follows.=C2=A0

[root@uaterssdrservice01= bin]# systemctl =C2=A0start =C2=A0edb-as-16.service=C2=A0 (No Errors in console)
[root@uaterssdrservice01 bin]# sudo = -u enterprisedb psql edb
psql: error: connection to server on socket &qu= ot;/tmp/.s.PGSQL.5444" failed: No such file or directory
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 Is the server running locally and accepting connections o= n that socket?
[root@uaterssdrservice01 bin]#

<= /div>

Why the server restart always fails=C2=A0 = =C2=A0on restore with this=C2=A0 time stamp ( greater than=C2=A0 =C2=A021-A= UG-24 13:58:49.611403+05:30 )=C2=A0 =C2=A0??=C2=A0 = =C2=A0

Or I have to understand: Never can we=C2=A0= restore=C2=A0 =C2=A0=C3=A4=C2=A0 db server after the last checkpoint ,=C2= =A0 and all other transactions that happened are lost forever=C2=A0 ?=C2=A0= =C2=A0 =C2=A0=C2=A0

or=C2=A0 in my Repo server th= e WALs are not=C2=A0 replicated properly ?=C2=A0 What=C2=A0may be=C2=A0 the= issue ?

EPAS16 on RHEL 9=C2=A0 =C2=A0and=C2=A0 Re= po Server=C2=A0 RHEL9 both different VMs.. Pgbackrest 2. 52.1

Pls shed some light on this=C2=A0=C2=A0

Thank you,
Krishane


--0000000000000c91e806202f2ae1--