PostgreSQL Point-in-Time Recovery with Oops-Queries
Wed, 2026-07-22 17:26 —
Shinguz
While working on the topic “PostgreSQL for Dolphins and Sea Lions,” I discovered that in the PostgreSQL universe, the topic of point-in-time recovery tends to be sidestepped when it comes to specific statements or transactions. And when examples are provided, they primarily focus on DDL statements rather than DML statements.
If you want to recover to some previous point in time (say, right before the junior DBA dropped your main transaction table), just specify the required stopping point. You can specify the stop point, known as the “recovery target”, either by date/time, named restore point or by completion of a specific transaction ID. As of this writing only the date/time and named restore point options are very usable, since there are no tools to help you identify with any accuracy which transaction ID to use. [ 1 ]
In the universe I come from, things are done differently. Here, it is absolutely no problem to perform a Point-in-Time Recovery down to the exact statement or transaction — provided you know what you are doing.
But now I am itching to find out if that is possible in this world, which is new to me…?
The Test Environment
As you know from our training sessions, we always run such experiments on our test table under load (insert_test.sh):
test=# DELETE FROM test WHERE id BETWEEN 10 AND 20;
DELETE 11
The Alert
Now the DBA receives the message: “Oops! Something broke! Can you please fix it?”
So what information do we need to be able to help here?
- When, as precisely as possible, did it break?
- On July 22, around 10:55 CEST ➜ Note: PostgreSQL always seems to operate in UTC here, so: 08:55 UTC.
- How, with which statement, and on which database did it break?
delete from test where id between 10 and 20- Database:
test - How man rows were (roughly) deleted?
Aside from the usual discussions about shutting down the database, shutting down the application, etc., to prevent further damage, the focus now is on how to restore the database to its state prior to the “oops” query.
Preparations
To determine EXACTLY where we want to stop during point-in-time recovery, we need the archived WAL files. And we will use the pg_waldump tool to examine the WAL files.
We should not access WAL files that are still “active”,
Can give wrong results when the server is running. [ 2 ]
which can potentially lead to incorrect results and errors:
$ pg_waldump 000000010000000000000033 >/dev/null
pg_waldump: error: error in WAL record at 0/330B6CF8: invalid record length at 0/330B6D20: expected at least 24, got 0
$ pg_waldump 000000010000000000000034 >/dev/null
pg_waldump: error: could not find a valid record after 0/34000000
To ensure this, we will rotate out the current WAL file so that it is archived:
postgres=# SELECT pg_switch_wal();
pg_switch_wal
---------------
0/3228AB50
First, we want to know which WAL files are relevant for the time period in question (Note: The server also runs on UTC!):
$ PGDATA='/var/lib/postgresql/18/main'
$ WALARCHIVE='/mnt/backup/wal_archive'
$ PATH="${PATH}:/usr/lib/postgresql/18/bin"
$ ll ${WALARCHIVE}/????????????????????????
-rw------- 1 postgres postgres 16777216 Jul 22 08:32 /mnt/backup/wal_archive/00000001000000000000002F
-rw------- 1 postgres postgres 16777216 Jul 22 08:47 /mnt/backup/wal_archive/000000010000000000000030
-rw------- 1 postgres postgres 16777216 Jul 22 09:02 /mnt/backup/wal_archive/000000010000000000000031
-rw------- 1 postgres postgres 16777216 Jul 22 09:11 /mnt/backup/wal_archive/000000010000000000000032
We can see this in more detail by looking into the WAL files (is there a more elegant way to do this?):
$ for file in ${WALARCHIVE}/???????????????????????? ; do
echo ${file}
pg_waldump ${file} | head | grep COMMIT
done
/mnt/backup/wal_archive/000000010000000000000030
rmgr: Transaction len (rec/tot): 34/ 34, tx: 109112, lsn: 0/300000E0, prev 0/300000A0, desc: COMMIT 2026-07-22 08:32:31.317115 UTC
rmgr: Transaction len (rec/tot): 34/ 34, tx: 109113, lsn: 0/300001C0, prev 0/30000180, desc: COMMIT 2026-07-22 08:32:31.368472 UTC
rmgr: Transaction len (rec/tot): 34/ 34, tx: 109114, lsn: 0/300002A0, prev 0/30000260, desc: COMMIT 2026-07-22 08:32:31.427410 UTC
/mnt/backup/wal_archive/000000010000000000000031
rmgr: Transaction len (rec/tot): 34/ 34, tx: 127114, lsn: 0/31001D78, prev 0/31000FF8, desc: COMMIT 2026-07-22 08:47:31.912547 UTC
rmgr: Transaction len (rec/tot): 34/ 34, tx: 127115, lsn: 0/31001E58, prev 0/31001E18, desc: COMMIT 2026-07-22 08:47:31.971700 UTC
rmgr: Transaction len (rec/tot): 34/ 34, tx: 127116, lsn: 0/31001F38, prev 0/31001EF8, desc: COMMIT 2026-07-22 08:47:32.026471 UTC
/mnt/backup/wal_archive/000000010000000000000032
rmgr: Transaction len (rec/tot): 34/ 34, tx: 145081, lsn: 0/320000E0, prev 0/320000A0, desc: COMMIT 2026-07-22 09:02:31.648356 UTC
rmgr: Transaction len (rec/tot): 34/ 34, tx: 145082, lsn: 0/320001C0, prev 0/32000180, desc: COMMIT 2026-07-22 09:02:31.689970 UTC
rmgr: Transaction len (rec/tot): 34/ 34, tx: 145083, lsn: 0/320002A0, prev 0/32000260, desc: COMMIT 2026-07-22 09:02:31.749528 UTC
If the time of the incident is correct (08:55/CEST), then the statement should be in WAL 000000010000000000000031…
Just to be safe, let us do a quick check:
$ pg_waldump ${WALARCHIVE}/000000010000000000000031 | grep DELETE
rmgr: Heap len (rec/tot): 59/ 8179, tx: 135586, lsn: 0/312DF748, prev 0/312DF720, desc: DELETE xmax: 135586, off: 10, infobits: [KEYS_UPDATED], flags: 0x01, blkref #0: rel 1663/21506/21508 blk 0 FPW
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1758, prev 0/312DF748, desc: DELETE xmax: 135586, off: 11, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1790, prev 0/312E1758, desc: DELETE xmax: 135586, off: 12, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E17C8, prev 0/312E1790, desc: DELETE xmax: 135586, off: 13, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1800, prev 0/312E17C8, desc: DELETE xmax: 135586, off: 14, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1838, prev 0/312E1800, desc: DELETE xmax: 135586, off: 15, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1870, prev 0/312E1838, desc: DELETE xmax: 135586, off: 16, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E18A8, prev 0/312E1870, desc: DELETE xmax: 135586, off: 17, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E18E0, prev 0/312E18A8, desc: DELETE xmax: 135586, off: 18, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1918, prev 0/312E18E0, desc: DELETE xmax: 135586, off: 19, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1950, prev 0/312E1918, desc: DELETE xmax: 135586, off: 20, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
Lucky us! There are DELETEs in there — exactly 11! So we might already be close…
To filter more precisely, we now need:
- the OID of the tablespace
- the OID of the database
- the OID of the table
postgres=# SELECT oid, spcname AS tablespace, spcowner::regrole AS owner
FROM pg_tablespace
;
oid | tablespace | owner
------+------------+----------
1663 | pg_default | postgres
1664 | pg_global | postgres
postgres=# SELECT oid, datname AS database
FROM pg_database
WHERE oid = 21506
;
oid | database
-------+-----------
5 | postgres
16388 | enswitch
1 | template1
4 | template0
21498 | oli
21506 | test
postgres=# \connect test
test=# SELECT c.oid, ns.nspname AS schema, c.relname AS object_name, c.relkind
FROM pg_class AS c
JOIN pg_namespace AS ns ON ns.oid = c.relnamespace
WHERE c.relkind = 'r' -- Ordinary Table
AND c.relname = 'test'
;
oid | schema | object_name | relkind
-------+--------+-------------+---------
21508 | public | test | r
Now we can filter down to the exact table:
$ pg_waldump --relation=1663/21506/21508 --rmgr=Heap ${WALARCHIVE}/000000010000000000000031 | grep DELETE
rmgr: Heap len (rec/tot): 59/ 8179, tx: 135586, lsn: 0/312DF748, prev 0/312DF720, desc: DELETE xmax: 135586, off: 10, infobits: [KEYS_UPDATED], flags: 0x01, blkref #0: rel 1663/21506/21508 blk 0 FPW
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1758, prev 0/312DF748, desc: DELETE xmax: 135586, off: 11, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1790, prev 0/312E1758, desc: DELETE xmax: 135586, off: 12, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E17C8, prev 0/312E1790, desc: DELETE xmax: 135586, off: 13, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1800, prev 0/312E17C8, desc: DELETE xmax: 135586, off: 14, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1838, prev 0/312E1800, desc: DELETE xmax: 135586, off: 15, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1870, prev 0/312E1838, desc: DELETE xmax: 135586, off: 16, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E18A8, prev 0/312E1870, desc: DELETE xmax: 135586, off: 17, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E18E0, prev 0/312E18A8, desc: DELETE xmax: 135586, off: 18, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1918, prev 0/312E18E0, desc: DELETE xmax: 135586, off: 19, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1950, prev 0/312E1918, desc: DELETE xmax: 135586, off: 20, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
This now returns exactly the 11 rows in the test table.
Now let us take a closer look at the whole thing using the viewer of our choice (preferably filtering by LSN). We see the preceding transaction and our transaction up to the COMMIT, all as a backward-linked list (prev ➜ lsn)?
rmgr: Transaction len (rec/tot): 34/ 34, tx: 135585, lsn: 0/312DF720, prev 0/312DF6E0, desc: COMMIT 2026-07-22 08:54:34.033961 UTC
rmgr: Heap len (rec/tot): 59/ 8179, tx: 135586, lsn: 0/312DF748, prev 0/312DF720, desc: DELETE xmax: 135586, off: 10, infobits: [KEYS_UPDATED], flags: 0x01, blkref #0: rel 1663/21506/21508 blk 0 FPW
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1758, prev 0/312DF748, desc: DELETE xmax: 135586, off: 11, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1790, prev 0/312E1758, desc: DELETE xmax: 135586, off: 12, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E17C8, prev 0/312E1790, desc: DELETE xmax: 135586, off: 13, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1800, prev 0/312E17C8, desc: DELETE xmax: 135586, off: 14, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1838, prev 0/312E1800, desc: DELETE xmax: 135586, off: 15, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1870, prev 0/312E1838, desc: DELETE xmax: 135586, off: 16, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E18A8, prev 0/312E1870, desc: DELETE xmax: 135586, off: 17, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E18E0, prev 0/312E18A8, desc: DELETE xmax: 135586, off: 18, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1918, prev 0/312E18E0, desc: DELETE xmax: 135586, off: 19, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 135586, lsn: 0/312E1950, prev 0/312E1918, desc: DELETE xmax: 135586, off: 20, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/21506/21508 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 135586, lsn: 0/312E1988, prev 0/312E1950, desc: COMMIT 2026-07-22 08:54:34.052628 UTC
So our Oops! query transaction has XID 135586 and starts at LSN 0/312DF748. Since we NO LONGER want this transaction during recovery, the recovery will be exclusive!
This means we already have the recovery_target information:
#
# postgres.conf
#
recovery_target_xid = '135586'
recovery_target_lsn = '0/312DF748'
recovery_target_inclusive = off
Recovery to XID
Now we can perform Point-in-Time Recovery with the XID as the target. This looks like the following in the log:
LOG: starting PostgreSQL 18.4 (Debian 18.4-1.pgdg13+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
LOG: database system was interrupted; last known up at 2026-07-21 14:21:59 UTC
cp: cannot stat '/mnt/backup/wal_archive/00000002.history': No such file or directory
LOG: starting backup recovery with redo LSN 0/21000108, checkpoint LSN 0/2100BAF0, on timeline ID 1
LOG: restored log file "000000010000000000000021" from archive
LOG: starting point-in-time recovery to XID 135586
LOG: redo starts at 0/21000108
LOG: restored log file "000000010000000000000022" from archive
LOG: completed backup recovery with redo LSN 0/21000108 and end LSN 0/2100C070
LOG: consistent recovery state reached at 0/2100C070
LOG: database system is ready to accept read-only connections
LOG: restored log file "000000010000000000000023" from archive
LOG: restored log file "000000010000000000000024" from archive
LOG: restored log file "000000010000000000000025" from archive
LOG: restored log file "000000010000000000000026" from archive
LOG: restored log file "000000010000000000000027" from archive
LOG: restored log file "000000010000000000000028" from archive
LOG: restored log file "000000010000000000000029" from archive
LOG: restored log file "00000001000000000000002A" from archive
LOG: restored log file "00000001000000000000002B" from archive
LOG: restored log file "00000001000000000000002C" from archive
LOG: restored log file "00000001000000000000002D" from archive
LOG: restored log file "00000001000000000000002E" from archive
LOG: restored log file "00000001000000000000002F" from archive
LOG: restored log file "000000010000000000000030" from archive
LOG: restored log file "000000010000000000000031" from archive
LOG: recovery stopping before commit of transaction 135586, time 2026-07-22 08:54:34.052628+00
LOG: pausing at the end of recovery
HINT: Execute pg_wal_replay_resume() to promote.
First, we check whether the UPS query was rolled back and, if so, up to which point the data is still/once again present. If the result is satisfactory, the database can be promoted back from recovery mode to primary mode:
test=# SELECT pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
test=# select pg_promote();
pg_promote
------------
t
A look at the log shows us that everything worked out:
LOG: received promote request
LOG: redo done at 0/312E1988 system usage: CPU: user: 0.12 s, system: 0.07 s, elapsed: 169.82 s
LOG: last completed transaction was at log time 2026-07-22 08:54:34.033961+00
cp: cannot stat '/mnt/backup/wal_archive/00000002.history': No such file or directory
LOG: selected new timeline ID: 2
cp: cannot stat '/mnt/backup/wal_archive/00000001.history': No such file or directory
LOG: archive recovery complete
LOG: checkpoint starting: force
LOG: database system is ready to accept connections
Recovery to LSN
Of course, we also wanted to test whether the whole process works with LSN as well. This is what it looks like in the log:
LOG: starting PostgreSQL 18.4 (Debian 18.4-1.pgdg13+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
LOG: database system was interrupted; last known up at 2026-07-21 14:21:59 UTC
cp: cannot stat '/mnt/backup/wal_archive/00000002.history': No such file or directory
LOG: starting backup recovery with redo LSN 0/21000108, checkpoint LSN 0/2100BAF0, on timeline ID 1
LOG: restored log file "000000010000000000000021" from archive
LOG: starting point-in-time recovery to WAL location (LSN) "0/312DF748"
LOG: redo starts at 0/21000108
LOG: restored log file "000000010000000000000022" from archive
LOG: completed backup recovery with redo LSN 0/21000108 and end LSN 0/2100C070
LOG: consistent recovery state reached at 0/2100C070
LOG: database system is ready to accept read-only connections
LOG: restored log file "000000010000000000000023" from archive
LOG: restored log file "000000010000000000000024" from archive
LOG: restored log file "000000010000000000000025" from archive
LOG: restored log file "000000010000000000000026" from archive
LOG: restored log file "000000010000000000000027" from archive
LOG: restored log file "000000010000000000000028" from archive
LOG: restored log file "000000010000000000000029" from archive
LOG: restored log file "00000001000000000000002A" from archive
LOG: restored log file "00000001000000000000002B" from archive
LOG: restored log file "00000001000000000000002C" from archive
LOG: restored log file "00000001000000000000002D" from archive
LOG: restored log file "00000001000000000000002E" from archive
LOG: restored log file "00000001000000000000002F" from archive
LOG: restored log file "000000010000000000000030" from archive
LOG: restored log file "000000010000000000000031" from archive
LOG: recovery stopping before WAL location (LSN) "0/312DF748"
LOG: pausing at the end of recovery
HINT: Execute pg_wal_replay_resume() to promote.
And then, after checking and promoting, we proceed as follows:
LOG: received promote request
LOG: redo done at 0/312DF748 system usage: CPU: user: 0.13 s, system: 0.05 s, elapsed: 90.70 s
LOG: last completed transaction was at log time 2026-07-22 08:54:34.033961+00
cp: cannot stat '/mnt/backup/wal_archive/00000002.history': No such file or directory
LOG: selected new timeline ID: 2
cp: cannot stat '/mnt/backup/wal_archive/00000001.history': No such file or directory
LOG: archive recovery complete
LOG: checkpoint starting: force
LOG: database system is ready to accept connections
Follow-up
Whether and how you can — or want to — recover the data generated AFTER the UPS query is a whole other story…
Just to be safe: Back up the affected database again before the restore/recovery — maybe we can or need to extract some more data from it?
