Download the postgresql extension from the releases page.

If you want to build it yourself, install Go 1.25+ and enable CGO:

go build -ldflags="-s -w" -buildmode=c-shared -o postgresql.so

Use:

• .so on Linux
• .dylib on macOS
• .dll on Windows

1. Edit postgresql.conf

   • Set wal_level = logical
   • Increase max_replication_slots and max_wal_senders as needed

2. Edit pg_hba.conf

   • Add a replication entry for the subscriber IP, for example:

host    replication     rep_user        subscriber_ip/32        md5

3. Create a replication user:

CREATE ROLE rep_user WITH REPLICATION LOGIN PASSWORD 'secret';

4. Create a publication:

CREATE PUBLICATION my_publication FOR TABLE table1, table2;
-- or for all tables
CREATE PUBLICATION my_publication FOR ALL TABLES;

5. Restart PostgreSQL.

Optional: convert PostgreSQL schema and data to SQLite:

go install github.com/litesql/postgresql/cmd/pg2sqlite@latest
pg2sqlite [postgresql_url] example.db

Load the extension:

sqlite3 example.db
.load ./postgresql
SELECT pg_info();

Start replication:

1. Create a slot if needed:

SELECT pg_create_slot(
  'postgres://rep_user:secret@127.0.0.1:5432/postgres',
  'my_slot'
);

2. Start replication by inserting into pg_sub:

INSERT INTO pg_sub(connect, slot, publication)
VALUES(
  'postgres://rep_user:secret@127.0.0.1:5432/postgres',
  'my_slot',
  'my_publication'
);

The extension stores changes in pg_history. Undo completed PostgreSQL transactions in reverse order.

Syntax:

pg_undo(<dsn>, <slot>, <startSeq>, <filter>)

Example:

SELECT pg_undo(
  'postgres://postgres:secret@localhost:5432/postgres',
  'my_slot',
  0,
  ''
);

startSeq is the sequence number in pg_history. Use 0 to revert the most recent transaction only.

You can also specify a time duration to undo transactions from that point up to now:

SELECT pg_undo(
  'postgres://postgres:secret@localhost:5432/postgres',
  'my_slot',
  '5m',
  '' 
);

The fourth parameter filters which entities to revert. You can filter by table name or by table name with a specific column value.

Syntax:

tableName[.column=value]

Examples:

1. Revert all changes from the past 5 minutes on the users table:

SELECT pg_undo(
    'postgres://postgres:secret@localhost:5432/postgres',
    'my_slot',
    '5m',
    'users' 
);

2. Revert all changes from the past 5 minutes on the users table where id=42:

SELECT pg_undo(
    'postgres://postgres:secret@localhost:5432/postgres',
    'my_slot',
    '5m',
    'users.id=42' 
);

Example: To skip history logging for a subscription, set type when inserting into pg_sub:

INSERT INTO pg_sub(connect, slot, publication, type)
VALUES(
    'postgres://rep_user:secret@127.0.0.1:5432/postgres',
    'my_slot',
    'my_publication',
    1
);

Configure replication parameters on the virtual table:

Enable logging with:

export SQLITE_PG_LOG=1

Logs will print to stderr.