pgbackrest restore with a checkpoint and timestamp after the checkpoint

[KK CHN <kkchn.in@xxxxxxxxx>]

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=# \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=# select now();
               now
----------------------------------
 21-AUG-24 13:58:31.611403 +05:30  // Before  table drop 
(1 row)

edb=# drop table important_table4;
DROP TABLE
edb=# \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=# select now();
               now
----------------------------------
 21-AUG-24 13:58:58.379552 +05:30  //after table drop
(1 row)

edb=# 

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.     Correct me If  I am wrong here  ?

I am expecting to see   the    edb=# \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=Demo --delta --set=20240820-152602F_20240820-160402D  --target-timeline=current --type=time  --target="21-AUG-24 13:58:49.611403+05:30" --target-action="" restore

2024-08-21 14:34:17.116 P00   INFO: restore command begin 2.52.1: --delta --exec-id=252857-6013404c --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/edb/as16/data --pg-version-force=16 --repo1-host=10.10.20.7 --repo1-host-user=postgres --set=20240820-152602F_20240820-160402D --spool-path=/var/spool/pgbackrest --stanza=Repo --target="21-AUG-24 13:58:49.611403+05:30" --target-action="" --target-timeline=current --type=time
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 = 89.8MB, file total = 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   ä  db server after the 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