SQLite extension to synchronize data using PostgreSQL logical replication

2 hours ago 1

Download postgresql extension from the releases page. Here's a great article that explains how to install the SQLite extension.

Go 1.24+ and CGO_ENABLED=1 is required.

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

Use .so extension for Linux, .dylib for MacOS and .dll for Windows

1. Modify postgresql.conf:

Set wal_level = logical.
Adjust max_replication_slots and max_wal_senders according to the number of subscribers and replication slots needed.

Add an entry to allow the replication user to connect from the subscriber's IP address. For example:

host replication rep_user subscriber_ip/32 md5

3. Create a Replication User:

Create a user with replication privileges:

CREATE ROLE rep_user WITH REPLICATION LOGIN PASSWORD 'secret';

Define which tables or all tables in a database should be replicated:

CREATE PUBLICATION my_publication FOR TABLE table1, table2; -- or for all tables in the current database: CREATE PUBLICATION my_publication FOR ALL TABLES;

1. Convert PostgreSQL databse to SQLite

go install github.com/litesql/postgresql/cmd/pg2sqlite@latest

pg2sqlite [postgresql_url] example.db

sqlite3 example.db # Load the extension .load ./postgresql # check version (optional) SELECT pg_info();

3. Start replication to sqlite

Create a slot (if necessary)

SELECT pg_create_slot('postgres://rep_user:[email protected]:5432/postgres', 'my_slot');

Insert data into pg_sub virtual table to start replication.

INSERT INTO temp.pg_sub(connect, slot, publication) VALUES('postgres://rep_user:[email protected]:5432/postgres', 'my_slot', 'my_publication');

You can configure replication by passing parameters to the VIRTUAL TABLE.

Param Description Default

use_namespace	Keep schema/namespace (otherwise always use main database)	false
position_tracker_table	Table to store replication position checkpoints	pg_pub_stat
timeout	Timeout in milliseconds	10000
logger	Log errors to "stdout, stderr or file:/path/to/log.txt"