Rollback Capability Makes pg_walex a Truth Regret Pill

hex song — Wed, 28 Oct 2026 02:35:34 +0000

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

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

3.2 Install

# make install PREFIX=/usr/local
install -d /usr/local/bin
install -m 0755 pg_walex /usr/local/bin/pg_walex

3.3 Check Version

# pg_walex --version
pg_walex 1.1.0

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

4.2 Ensure wal_level = logical

db_rollback=# show wal_level ;
 wal_level 
-----------
 logical
(1 row)

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)

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

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)

4.6 Simulate Accidental Data Deletion

db_rollback=# DELETE FROM tab_rollback WHERE id = 1;
DELETE 1

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)

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;

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)

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

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

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)

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;

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

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)

5.2.3 Clear Data

db_rollback=# truncate table tab_truncate ;
TRUNCATE TABLE

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)

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;

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

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;

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;

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.

DEV Community: hex song

Rollback Capability Makes pg_walex a Truth Regret Pill

Rollback Capability Makes pg_walex a Truth Regret Pill

Version Update Preview

1. Main New Features

2. Source Code Download

3. Compilation and Installation

3.1 Build

3.2 Install

3.3 Check Version

4. Verify Basic DML Rollback Capability

4.1 Create Database

4.2 Ensure wal_level = logical

4.3 View LSN and Corresponding File

4.4 Create Sample Table

4.5 View LSN and Corresponding File Again

4.6 Simulate Accidental Data Deletion

4.7 View Log LSN and Corresponding File

4.8 Find the Accidentally Deleted Data

5. Verify Rollback Capability for DROP and TRUNCATE

5.1 Verify DROP Rollback Capability

5.1.1 View LSN and File Location

5.1.2 Create Table and Insert 5 Rows

5.1.3 Execute DROP TABLE

5.1.4 View LSN and File Location

5.1.5 WAL Rollback Capability for DROP

5.2 Verify TRUNCATE Rollback Capability

5.2.1 Simulate Data

5.2.2 View LSN and File Location

5.2.3 Clear Data

5.2.4 View LSN and File Location

5.2.5 Verify TRUNCATE Rollback Capability

6. RESTful API Support

6.1 Start

6.2 Verify Monitoring Starting from a Specified LSN

6.3 Verify Rollback API Capability

Conclusion