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. C*orrect 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=promote 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=promote --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
