DEV Community

hex song
hex song

Posted on

Rollback Capability Makes pg_walex a Truth Regret Pill

#postgres #database

Rollback Capability Makes pg_walex a Truth Regret Pill

The smallest, most powerful, fastest, and easiest-to-use PostgreSQL WAL log parsing tool—smooth as Dove chocolate

pg_walex 1.1.0 turns PostgreSQL WAL from a "read-only black box" into a "reversible time machine."

Version Update Preview

  • Output now includes operation type type, lsn, and timestamp
  • --rollback + GET/POST /api/v1/rollback: each forward statement is immediately followed by its inverse operation, enabling replay-based undo
  • Enhanced rollback capability for truncate and drop
  • Fixed the strncopy warning issue when compiling with older C compilers
  • Replaced strncopy with memcopy
  • Real-time monitoring + GET /api/v1/item

1. Main New Features

  • --rollback supports data rollback capability
  • RESTful API rollback capability, integrable with multiple platforms

2. Source Code Download

Source code is available from github, gitee, and gitcode:

git clone https://github.com/shaohuasong-fang/pg_walex.git
git clone https://gitee.com/developerhonor/pg_walex.git
git clone https://gitcode.com/sungsasong/pg_walex.git
Enter fullscreen mode Exit fullscreen mode

3. Compilation and Installation

3.1 Build 

# make -j$(nproc)
cc -O2 -g -std=c11 -Wall -Wextra -Wno-unused-parameter -pthread -D_POSIX_C_SOURCE=200809L -D_DEFAULT_SOURCE -Iinclude -Isrc -c -o src/pwe_util.o src/pwe_util.c
cc -O2 -g -std=c11 -Wall -Wextra -Wno-unused-parameter -pthread -D_POSIX_C_SOURCE=200809L -D_DEFAULT_SOURCE -Iinclude -Isrc -c -o src/pwe_version.o src/pwe_version.c
cc -O2 -g -std=c11 -Wall -Wextra -Wno-unused-parameter -pthread -D_POSIX_C_SOURCE=200809L -D_DEFAULT_SOURCE -Iinclude -Isrc -c -o src/pwe_wal.o src/pwe_wal.c
cc -O2 -g -std=c11 -Wall -Wextra -Wno-unused-parameter -pthread -D_POSIX_C_SOURCE=200809L -D_DEFAULT_SOURCE -Iinclude -Isrc -c -o src/pwe_tuple.o src/pwe_tuple.c
cc -O2 -g -std=c11 -Wall -Wextra -Wno-unused-parameter -pthread -D_POSIX_C_SOURCE=200809L -D_DEFAULT_SOURCE -Iinclude -Isrc -c -o src/pwe_catalog.o src/pwe_catalog.c
cc -O2 -g -std=c11 -Wall -Wextra -Wno-unused-parameter -pthread -D_POSIX_C_SOURCE=200809L -D_DEFAULT_SOURCE -Iinclude -Isrc -c -o src/pwe_recon.o src/pwe_recon.c
cc -O2 -g -std=c11 -Wall -Wextra -Wno-unused-parameter -pthread -D_POSIX_C_SOURCE=200809L -D_DEFAULT_SOURCE -Iinclude -Isrc -c -o src/pwe_output.o src/pwe_output.c
cc -O2 -g -std=c11 -Wall -Wextra -Wno-unused-parameter -pthread -D_POSIX_C_SOURCE=200809L -D_DEFAULT_SOURCE -Iinclude -Isrc -c -o src/pwe_main.o src/pwe_main.c
cc -O2 -g -std=c11 -Wall -Wextra -Wno-unused-parameter -pthread -D_POSIX_C_SOURCE=200809L -D_DEFAULT_SOURCE -Iinclude -Isrc -c -o src/pwe_http.o src/pwe_http.c
cc  -o pg_walex src/pwe_util.o src/pwe_version.o src/pwe_wal.o src/pwe_tuple.o src/pwe_catalog.o src/pwe_recon.o src/pwe_output.o src/pwe_main.o src/pwe_http.o -pthread
Enter fullscreen mode Exit fullscreen mode

3.2 Install 

# make install PREFIX=/usr/local
install -d /usr/local/bin
install -m 0755 pg_walex /usr/local/bin/pg_walex
Enter fullscreen mode Exit fullscreen mode

3.3 Check Version 

# pg_walex --version
pg_walex 1.1.0
Enter fullscreen mode Exit fullscreen mode

4. Verify Basic DML Rollback Capability

4.1 Create Database 

# psql -U postgres -d postgres -p6432
psql (18.4)
Type "help" for help.

postgres=# create database db_rollback;
CREATE DATABASE
Enter fullscreen mode Exit fullscreen mode

4.2 Ensure wal_level = logical 

db_rollback=# show wal_level ;
 wal_level 
-----------
 logical
(1 row)
Enter fullscreen mode Exit fullscreen mode

4.3 View LSN and Corresponding File 

db_rollback=# SELECT pg_walfile_name(pg_current_wal_lsn()),pg_current_wal_lsn();
     pg_walfile_name      | pg_current_wal_lsn 
--------------------------+--------------------
 0000000100000005000000BB | 5/BB47AF10
(1 row)
Enter fullscreen mode Exit fullscreen mode

4.4 Create Sample Table 

db_rollback=# CREATE TABLE tab_rollback(id int primary key,name varchar(20));
CREATE TABLE
db_rollback=# INSERT INTO tab_rollback VALUES(1,'Verifying rollback');
INSERT 0 1
Enter fullscreen mode Exit fullscreen mode

4.5 View LSN and Corresponding File Again 

db_rollback=# SELECT pg_walfile_name(pg_current_wal_lsn()),pg_current_wal_lsn();
     pg_walfile_name      | pg_current_wal_lsn 
--------------------------+--------------------
 0000000100000005000000BB | 5/BB47DAC8
(1 row)
Enter fullscreen mode Exit fullscreen mode

4.6 Simulate Accidental Data Deletion 

db_rollback=# DELETE FROM tab_rollback WHERE id = 1;
DELETE 1
Enter fullscreen mode Exit fullscreen mode

4.7 View Log LSN and Corresponding File 

db_rollback=# SELECT pg_walfile_name(pg_current_wal_lsn()),pg_current_wal_lsn();
     pg_walfile_name      | pg_current_wal_lsn 
--------------------------+--------------------
 0000000100000005000000BB | 5/BB47DB38
(1 row)
Enter fullscreen mode Exit fullscreen mode

4.8 Find the Accidentally Deleted Data

Here, search within the interval between start LSN 5/BB47DAC8 and end LSN 5/BB47DB38:

[root@PGServer ~]# pg_walex -f /data/tmp/data/pg_wal/0000000100000005000000BB --rollback -s 5/BB47DAC8 -e 5/BB47DB38
\connect db_rollback
-- pg_walex: type=delete,xid=1233,lsn=5/BB47DAC8,timestamp=2026-10-27 08:02:10.117709+00
BEGIN;
DELETE FROM public.tab_rollback WHERE id IS NOT DISTINCT FROM 1;
-- pg_walex: rollback,xid=1233,lsn=5/BB47DAC8,timestamp=2026-10-27 08:02:10.117709+00
INSERT INTO public.tab_rollback (id, name) VALUES (1, 'Verifying rollback');
COMMIT;
Enter fullscreen mode Exit fullscreen mode

5. Verify Rollback Capability for DROP and TRUNCATE

Note: The recovery of delete operations and TRUNCATE is limited by cache size. If data is accidentally deleted, stop writing to the database as soon as possible.

5.1 Verify DROP Rollback Capability

5.1.1 View LSN and File Location 

db_rollback=# SELECT pg_walfile_name(pg_current_wal_lsn()),pg_current_wal_lsn();
     pg_walfile_name      | pg_current_wal_lsn 
--------------------------+--------------------
 0000000100000005000000BB | 5/BB47DC78
(1 row)
Enter fullscreen mode Exit fullscreen mode

5.1.2 Create Table and Insert 5 Rows 

db_rollback=# CREATE TABLE tab_drop(id int primary key,name varchar(20));
CREATE TABLE
db_rollback=# INSERT INTO tab_drop VALUES(1,'first');
INSERT 0 1
db_rollback=# INSERT INTO tab_drop VALUES(2,'second');
INSERT 0 1
db_rollback=# INSERT INTO tab_drop VALUES(3,'third');
INSERT 0 1
db_rollback=# INSERT INTO tab_drop VALUES(4,'forth');
INSERT 0 1
db_rollback=# INSERT INTO tab_drop VALUES(5,'fifth');
INSERT 0 1
Enter fullscreen mode Exit fullscreen mode

5.1.3 Execute DROP TABLE 

-- LSN before deletion, used as the start LSN for the DROP operation
db_rollback=# SELECT pg_walfile_name(pg_current_wal_lsn()),pg_current_wal_lsn();
     pg_walfile_name      | pg_current_wal_lsn 
--------------------------+--------------------
 0000000100000005000000BB | 5/BB4A89A0
(1 row)

db_rollback=# DROP TABLE tab_drop ;
DROP TABLE
Enter fullscreen mode Exit fullscreen mode

5.1.4 View LSN and File Location 

db_rollback=# SELECT pg_walfile_name(pg_current_wal_lsn()),pg_current_wal_lsn();
     pg_walfile_name      | pg_current_wal_lsn 
--------------------------+--------------------
 0000000100000005000000BB | 5/BB4ACB68
(1 row)
Enter fullscreen mode Exit fullscreen mode

5.1.5 WAL Rollback Capability for DROP 

[root@PGServer ~]# pg_walex -f /data/tmp/data/pg_wal/0000000100000005000000BB --rollback -s 5/BB4A89A0 -e 5/BB4ACB68
\connect db_rollback
-- pg_walex: type=drop,xid=1240,lsn=5/BB4ABB08,timestamp=2026-10-27 08:11:09.278194+00
BEGIN;
DROP TABLE public.tab_drop;
-- pg_walex: rollback,xid=1240,lsn=5/BB4AC640,timestamp=2026-10-27 08:11:09.278194+00
CREATE TABLE public.tab_drop (
  id int4 NOT NULL,
  name varchar(20),
  PRIMARY KEY (id)
);
INSERT INTO public.tab_drop (id, name) VALUES (3, 'third');
INSERT INTO public.tab_drop (id, name) VALUES (2, 'second');
INSERT INTO public.tab_drop (id, name) VALUES (1, 'first');
INSERT INTO public.tab_drop (id, name) VALUES (5, 'fifth');
INSERT INTO public.tab_drop (id, name) VALUES (4, 'forth');
COMMIT;
Enter fullscreen mode Exit fullscreen mode

5.2 Verify TRUNCATE Rollback Capability

5.2.1 Simulate Data 

db_rollback=# CREATE TABLE tab_truncate(id int primary key,name varchar(20));
CREATE TABLE
db_rollback=# INSERT INTO tab_truncate (id, name) VALUES
    (1, 'Alice'),
    (2, 'Bob'),
    (3, 'Charlie'),
    (4, 'Diana'),
    (5, 'Eve');
INSERT 0 5
Enter fullscreen mode Exit fullscreen mode

5.2.2 View LSN and File Location 

db_rollback=# SELECT pg_walfile_name(pg_current_wal_lsn()),pg_current_wal_lsn();
     pg_walfile_name      | pg_current_wal_lsn 
--------------------------+--------------------
 0000000100000005000000BB | 5/BB4D55D0
(1 row)
Enter fullscreen mode Exit fullscreen mode

5.2.3 Clear Data 

db_rollback=# truncate table tab_truncate ;
TRUNCATE TABLE
Enter fullscreen mode Exit fullscreen mode

5.2.4 View LSN and File Location 

db_rollback=# SELECT pg_walfile_name(pg_current_wal_lsn()),pg_current_wal_lsn();
     pg_walfile_name      | pg_current_wal_lsn 
--------------------------+--------------------
 0000000100000005000000BB | 5/BB4D9E30
(1 row)
Enter fullscreen mode Exit fullscreen mode

5.2.5 Verify TRUNCATE Rollback Capability 

[root@PGServer ~]# pg_walex -f /data/tmp/data/pg_wal/0000000100000005000000BB --rollback -s 5/BB4D55D0 -e 5/BB4D9E30
\connect db_rollback
-- pg_walex: type=truncate,xid=1243,lsn=5/BB4D9AA8,timestamp=2026-10-27 08:18:03.800537+00
BEGIN;
TRUNCATE TABLE public.tab_truncate;
-- pg_walex: rollback,xid=1243,lsn=5/BB4D9CF8,timestamp=2026-10-27 08:18:03.800537+00
INSERT INTO public.tab_truncate (id, name) VALUES (3, 'Charlie');
INSERT INTO public.tab_truncate (id, name) VALUES (2, 'Bob');
INSERT INTO public.tab_truncate (id, name) VALUES (1, 'Alice');
INSERT INTO public.tab_truncate (id, name) VALUES (5, 'Eve');
INSERT INTO public.tab_truncate (id, name) VALUES (4, 'Diana');
COMMIT;
Enter fullscreen mode Exit fullscreen mode

In summary: For tables affected by DROP and TRUNCATE, the data can also be recovered through WAL logs.

6. RESTful API Support

6.1 Start 

[root@PGServer ~]# pg_walex --serve --monitor --listen 0.0.0.0 --port 8900 \
  --token my-secret-token --dir  /data/tmp/data/pg_wal
pg_walex 1.1.0 REST API listening on http://0.0.0.0:8900/api/v1
pg_walex: default WAL directory: /data/tmp/data/pg_wal
pg_walex: monitoring /data/tmp/data/pg_wal; latest statement at /api/v1/item
Enter fullscreen mode Exit fullscreen mode

6.2 Verify Monitoring Starting from a Specified LSN 

[root@PGServer ~]# curl -s -H "Authorization: Bearer my-secret-token"   'http://127.0.0.1:8900/api/v1/sql?dir=/data/tmp/data/pg_wal&start_lsn=5/BB4D55D0'
\connect db_rollback
-- pg_walex: type=truncate,xid=1243,lsn=5/BB4D9AA8,timestamp=2026-10-27 08:18:03.800537+00
BEGIN;
TRUNCATE TABLE public.tab_truncate;
COMMIT;
Enter fullscreen mode Exit fullscreen mode

6.3 Verify Rollback API Capability 

[root@PGServer ~]# curl -s -H "Authorization: Bearer my-secret-token" \
  'http://127.0.0.1:8900/api/v1/rollback?dir=/data/tmp/data/pg_wal&start_lsn=5/BB4D55D0'
\connect db_rollback
-- pg_walex: type=truncate,xid=1243,lsn=5/BB4D9AA8,timestamp=2026-10-27 08:18:03.800537+00
BEGIN;
TRUNCATE TABLE public.tab_truncate;
-- pg_walex: rollback,xid=1243,lsn=5/BB4D9CF8,timestamp=2026-10-27 08:18:03.800537+00
INSERT INTO public.tab_truncate (id, name) VALUES (3, 'Charlie');
INSERT INTO public.tab_truncate (id, name) VALUES (2, 'Bob');
INSERT INTO public.tab_truncate (id, name) VALUES (1, 'Alice');
INSERT INTO public.tab_truncate (id, name) VALUES (5, 'Eve');
INSERT INTO public.tab_truncate (id, name) VALUES (4, 'Diana');
COMMIT;
Enter fullscreen mode Exit fullscreen mode

Conclusion

pg_walex lets you: read, restore, regret, and monitor in real time.
Make DROP no longer the end, and TRUNCATE no longer a matter of prayer—if the WAL is still there, there is still a way to recover.

Top comments (0)