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 1stLGg-00FSnB-Vs for pgsql-general@arkaria.postgresql.org; Wed, 25 Sep 2024 06:12:55 +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 1stLGg-003v0N-3x for pgsql-general@arkaria.postgresql.org; Wed, 25 Sep 2024 06:12:54 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1stLGf-003v0E-Ex for pgsql-general@lists.postgresql.org; Wed, 25 Sep 2024 06:12:53 +0000 Received: from mail-yw1-x1130.google.com ([2607:f8b0:4864:20::1130]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1stLGc-000wX2-OA for pgsql-general@postgresql.org; Wed, 25 Sep 2024 06:12:52 +0000 Received: by mail-yw1-x1130.google.com with SMTP id 00721157ae682-6de15eefdd3so47308607b3.0 for ; Tue, 24 Sep 2024 23:12:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1727244770; x=1727849570; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=WzdTdDYgebO6Ij5YS7QXBNQZdjUNAs+rYP4WphfTxA0=; b=RKARTyxeWJt20ppWucGA368uievkKbJ7gP8Cd3sGFtOrqaDUDN66mjREyMVjlx2SJf WfAkLFwespFTu2JfXizy7BUMaDrjUqEFUtbHP0wwMdqPkmA181V332mGkSOm47q+eJ48 uxoiB+9BUzuDuiJX1JoYpnwWRi4z1z0LEDM+qvpYzk6fREGi6On/R60XVbj0qUnLjgGw d2A/z1z+ffCQQ8gPJRLDFuvDQPl0FKXuAkvnU4tyfXUs2vqfPxH7Or9b461IxH696PYQ /LZQb1labe3VTc6+ZH7XbRSPwioMvNa/HZlxizk7WbhM0MbfDQyNjMcgPwK2fofmavjJ m6+Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1727244770; x=1727849570; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=WzdTdDYgebO6Ij5YS7QXBNQZdjUNAs+rYP4WphfTxA0=; b=drJ/Vj95F7V2rPyiobO9u0AV2FSUH3lVhqFfW6DA7QSFtBOqoRwEiauk8/paW9TqVp uQW/5YFaeZQlfgMPt8L27yWjfSOxgjXn2E8W83UsPZsb2KP5yiVuCR14kGDoxgM1lOJ2 I41C/SI+dtxgrbFWtRDlJuN1z8V6RHk2EvJhcFXOtA+9yKFDucKuxJrB3SaG7LaSXKo8 /mKJVNlS4gWYsrc9ZL2tjClnqic7cBzEsGdA8q0nP019JJ+a5NcZK2rLxfC5fr2a7FbJ M422GvJU4K1vW+U/5QeaJ2huPkEvspLfa+PxMYrWmZxk7shh5GTpa2OjKoLY1//8apTw hsfA== X-Gm-Message-State: AOJu0Yz1lQAWBW1rLe659Zi99C+vCBEdLWzm6k7UIoTTvJM18BOuV1ac NHxpsfmO5iKeLkaGcVqAwrAyeKfu+hWWckdqIKR9A8GpauxHX8RFrHmLHN0RHCX3lGOxbPt4XAc MEjbfYvrLNRedQGHfJhKZPkyoo+kiaxf4 X-Google-Smtp-Source: AGHT+IHQgwH2ih7dOTj3H3NsNKmozUxJojxQvhC79XYRHgUOF5ANHPVoVAPkZSh5/2cHKtWCgjkUiZfb3F73LxrcjRw= X-Received: by 2002:a05:690c:397:b0:686:1240:621a with SMTP id 00721157ae682-6e21d9eae13mr14446317b3.31.1727244769689; Tue, 24 Sep 2024 23:12:49 -0700 (PDT) MIME-Version: 1.0 From: KK CHN Date: Wed, 25 Sep 2024 11:42:58 +0530 Message-ID: Subject: PgBackRest : Restore to a checkpoint shows further transactions To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000f007510622eb82fb" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f007510622eb82fb Content-Type: text/plain; charset="UTF-8" List, PgBackRest : I tried to restore the latest backup taken at my RepoServer to a testing EPAS server freshly deployed . I have a full backup, two diff and one INCR as on today morning. The latest one is INCR full backup: 20240922-232733F timestamp start/stop: 2024-09-22 23:27:33+05:30 / 2024-09-23 09:17:00+05:30 diff backup: 20240922-232733F_20240924-222336D timestamp start/stop: 2024-09-24 22:23:36+05:30 / 2024-09-24 22:55:41+05:30 incr backup: 20240922-232733F_20240925-082637I timestamp start/stop: 2024-09-25 08:26:37+05:30 / 2024-09-25 08:36:00+05:30 On my Test EPAS Server : [root@dbch ~]# *sudo -u enterprisedb pgbackrest --stanza=Repo1 --delta --set=20240922-232733F_20240925-082637*I --target-timeline=current *restore* 2024-09-25 10:28:42.493 P00 INFO: restore command end: completed successfully (2657236ms) Now I comment out the archive command in the test EPAS server postgresql.conf and started the EPAS server. WHen I issue a query to select few rows To my surprise I am seeing the records with columns with time stamp up to a time 10.36:11:968 and 1 0:36:13.363 : How did this happen ? I specified the restore point file (incr) taken at 2024-09-25 08:26:37, naturally I expected restore may show records up to this time stamp or up to 2024-09-25 08:36:00+05:30 but it shows further to 10.36:11:968 and 0:36:13.363 but not beyond this !!! But my restore ends successfully at 2024-09-25 10:28:42.493 P00 INFO: restore command end: completed successfully (2657236ms) Could someone explain how this comes about ? But no other records latest than 10.36:11:968 and 0:36:13.363 showing .. How is it delimited here at this time stamp ? SO I guess this is due to specifying --target-timeline=current ? But restore finished at 10.28:42.493 OR Does this take all wal and replay up to the EPAS service starting time of the testing EPAS server ? Thank you, Krishane. For more inputs : I have queried like this below.. t_db=# select * from c.cti_all_info ORDER BY received_time DESC LIMIT 1; id | caller_number | call_identifier | ivr_start_time | ivr_connect_time | ivr_drop_time | ivr_drop_reason | call_landing_time | call_start_time | call_end_time | call_drop_reason | sip_extension | call_direction | message_list | voice_path | partition_key | received_time | remarks | source_ip_address | pilot_number ---------+---------------+--------------------+-------------------------+------------------+---------------+-----------------+-------------------+-----------------+-------------------------+------------------- +---------------+----------------------------------------------------------------------------------------------------------------------------+------------+---------------+---------------------- ---+---------+-------------------+-------------- 66769044 | 555657643942 | 140771.5140 | 2024-09-25 10:36:11.968 | | | | | | 2024-09-25 10:36:13.363 | User Disconnected | | IN | ["{\"srcType\":\"ACS\",\"srId\":\" I have PgBack successfully running on a Production Server and a Repo Server RHEL9.4, PgBackRest 2.52.1 and EPAS 16.1 . Restore performing for the first time. --000000000000f007510622eb82fb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
List,


PgBackRest :=C2=A0= I tried to restore the latest backup taken at my RepoServer to a=C2=A0 tes= ting EPAS server freshly deployed .

I have a full = backup, two diff=C2=A0 and one INCR=C2=A0 as on today morning.=C2=A0 =C2=A0= The latest one is INCR=C2=A0


= =C2=A0 =C2=A0 full backup: 20240922-232733F
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 timestamp start/stop: 2024-09-22 23:27:33+05:30 / 2024-09-23 = 09:17:00+05:30

=C2=A0 diff backup: 20240922-23= 2733F_20240924-222336D
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 timesta= mp start/stop: 2024-09-24 22:23:36+05:30 / 2024-09-24 22:55:41+05:30


=C2=A0incr backup: 20240922-232733F_2= 0240925-082637I
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 timestamp star= t/stop: 2024-09-25 08:26:37+05:30 / 2024-09-25 08:36:00+05:30<= br>



On my Test=C2=A0= EPAS Server :=C2=A0

[root@dbch ~]# = sudo -u enterprisedb pgbackrest --stanza=3DRepo1=C2=A0 --delta --set=3D20240922-232733F_20240925-082637I =C2=A0--target-timeline=3Dcurrent =C2=A0restore

<= br>

=

2024-09-25= 10:28:42.493 P00=C2=A0=C2=A0 INFO: restore command end: completed successfully (2657236ms)

=

Now I=C2=A0 comment out the archive command in the te= st EPAS server postgresql.conf=C2=A0 and started the EPAS server.


WHen I issue= a query=C2=A0 to select few rows=C2=A0 To my surprise=C2=A0 I am seeing th= e records with columns with time stamp up to a time 10.36:11:968=C2=A0 and= =C2=A0 10:36:13.363=C2=A0:=C2=A0=C2=A0


How did this= happen ?=C2=A0 =C2=A0I specified the restore point file (incr) taken at=C2= =A0=C2=A02024-09-25=C2=A008:26:37,=C2=A0 =C2=A0naturally=C2=A0 I exp= ected restore may show=C2=A0 records up to this time stamp or=C2=A0 =C2=A0u= p to=C2=A0202= 4-09-25 08:36:00+05:30=C2=A0 =C2=A0but it shows further to=C2=A0=C2=A010.36:11:968=C2=A0 and= =C2=A0=C2=A00:36:13.363=C2=A0but not beyond this !!!


But my restore=C2=A0 ends successfully=C2=A0 =C2=A0at=C2=A0=C2=A02024-09-25 10:28:42.49= 3 P00=C2=A0=C2=A0 INFO: restore command end: completed successfully (2657236ms)

=C2=A0Could someone=C2=A0explain how = this comes about ?

But no other records=C2=A0 latest=C2=A0 than=C2=A0= =C2=A010.36:11:96= 8=C2=A0 and=C2=A0=C2=A00:36:13.3= 63=C2=A0 =C2=A0showing=C2=A0 .. How i= s it delimited here at this time stamp ?=C2=A0


SO=C2=A0 I = guess this is due to specifying=C2=A0=C2=A0--target-timeline=3Dcurrent=C2=A0 =C2=A0= ?=C2=A0 But restore finished=C2=A0at 10.28:= 42.493=C2=A0=C2=A0

OR

=C2=A0= Does this take all wal and replay up to=C2=A0 the= =C2=A0 EPAS=C2=A0 service starting=C2=A0 time of the testing EPAS server ?=C2=A0<= /span>


Thank you,

Krishane.


For more inputs= :=C2=A0 I have queried like this below..=C2=A0


t_db=3D# select * from c.cti_all_info =C2=A0ORDER BY received_ti= me DESC LIMIT 1;
=C2=A0 =C2=A0id =C2=A0 =C2=A0| caller_number | =C2=A0ca= ll_identifier =C2=A0 | =C2=A0 =C2=A0 ivr_start_time =C2=A0 =C2=A0 =C2=A0| i= vr_connect_time | ivr_drop_time | ivr_drop_reason | call_landing_time | cal= l_start_time | =C2=A0 =C2=A0 =C2=A0call_end_time =C2=A0 =C2=A0 =C2=A0| call= _drop_reason
| sip_extension | call_direction |

=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0message_list

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| voice_path | p= artition_key | =C2=A0 =C2=A0 =C2=A0received_time
=C2=A0 =C2=A0| remarks = | source_ip_address | pilot_number
---------+---------------+-----------= ---------+-------------------------+------------------+---------------+----= -------------+-------------------+-----------------+-----------------------= --+-------------------
+---------------+--------------------------------= ---------------------------------------------------------------------------= -----------------+------------+---------------+----------------------
--= -+---------+-------------------+--------------
=C2=A066769044 |=C2=A0 = =C2=A0 555657643942 | 140771.5140 | 2024-09-25 10:3= 6:11.968 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 | 2024-09-25 10:36:13.363 | U= ser Disconnected
| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | IN= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | ["{\"srcType\":= \"ACS\",\"srId\":\"



I have PgBack successfully=C2= =A0running on a Production Server and a Repo Server=C2=A0 =C2=A0RHEL9.4, Pg= BackRest 2.52.1 and EPAS 16.1 .=C2=A0 Restore performing for the first time= .=C2=A0





--000000000000f007510622eb82fb--