Horton

Thread

  1. PgBackRest : Restore to a checkpoint shows further transactions

    KK CHN <kkchn.in@gmail.com> — 2024-09-25T06:12:58Z

    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.

  2. Re: PgBackRest : Restore to a checkpoint shows further transactions

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-09-25T15:36:13Z

    On 9/24/24 23:12, KK CHN wrote:
> List,
> 
> 
> PgBackRest :  I tried to restore the latest backup taken at my 

> 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  
> 10: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 to2024-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 !!!

0:36:13.363 of what date?

What is the type of the timestamp fields you are looking at, timestamp 
or timestamptz?

> Thank you,
> 
> Krishane.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

  3. Re: PgBackRest : Restore to a checkpoint shows further transactions

    Greg Sabino Mullane <htamfids@gmail.com> — 2024-09-25T21:32:23Z

    On Wed, Sep 25, 2024 at 2:13 AM KK CHN <kkchn.in@gmail.com> wrote:

> PgBackRest :  I tried to restore the latest backup taken at my RepoServer
> to a  testing EPAS server freshly deployed .
> ...
>
Now I  comment out the archive command in the test EPAS server
> postgresql.conf  and started the EPAS server.
>

* To disable archiving, you can pass --archive-mode=off to pgbackrest in
the restore command. Actually, for safety, you should always use this
argument when restoring.

* Please say "Postgres" not "EPAS"

* The restore starts from your incremental backup, but then it will replay
WAL created after that backup. Check out the postgresql.auto.conf file.

* I think what you are looking for is --type=immediate

* For debugging and helping to learn, it can be useful to use
--pg1-path=/tmp/restoretest to restore to a different data directory. Just
remember to change the port. And always use --archive-mode=off :)

See specifically this section of the docs:
https://pgbackrest.org/command.html#command-restore

Cheers,
Greg