zknill / sqledge Goto Github PK

Replicate postgres to SQLite on the edge

Go 100.00%

sqledge's Introduction

SQLedge

[State: alpha]

SQLedge uses Postgres logical replication to stream the changes in a source Postgres database to a SQLite database that can run on the edge. SQLedge serves reads from its local SQLite database, and forwards writes to the upstream Postgres server that it's replicating from.

This lets you run your apps on the edge, and have local, fast, and eventually consistent access to your data.

SQL generation

The pkg/sqlgen package has an SQL generator in it, which will generate the SQLite insert, update, delete statements based on the logical replication messages received.

SQL parsing

When the database is started, we look at which tables already exist in the sqlite copy, and make sure new tables are created automatically on the fly.

Postgres wire proxy

SQLedge contains a Postgres wire proxy, default on localhost:5433. This proxy uses the local SQlite database for reads, and forwards writes to the upstream Postgres server.

Compatibility

When running, the SQL statements interact with two databases; Postgres (for writes) and SQLite (for reads).

The Postgres wire proxy (which forwards reads to SQLite) doesn't currently translate any of the SQL statements from the Postgres query format/functions to the SQLite format/functions. Read queries issued against the Postgres wire proxy need to be compatible with SQLite directly. This is fine for simple SELECT queries, but you will have trouble with Postgres-specific query functions or syntax.

Copy on startup

SQLEdge maintains a table called postgres_pos, this tracks the LSN (log sequence number) of the received logical replication messages so it can pick up processing where it left off.

If no LSN is found, SQLedge will start a postgres COPY of all tables in the public schema. Creating the appropriate SQLite tables, and inserting data.

When the replication slot is first created, it exports a transaction snapshot. This snapshot is used for the initial copy. This means that the COPY command will read the data from the transaction at the moment the replication slot was created.

Trying it out

Create a database
```
create database myappdatabase;
```
Create a user -- must be a super user because we create a publication on all tables
```
create user sqledger with login superuser password 'secret';
```

Run the example

SQLEDGE_UPSTREAM_USER=sqledger SQLEDGE_UPSTREAM_PASSWORD=secret SQLEDGE_UPSTREAM_NAME=myappdatabase go run ./cmd/sqledge/main.go

Connect to the postgres wire proxy

psql -h localhost -p 5433

$ CREATE TABLE my_table (id serial not null primary key, names text);
$ INSERT INTO my_table (names) VALUES ('Jane'), ('John');

$ SELECT * FROM my_table;

The read will be served from the local database

Connect to the local sqlite db
```
sqlite3 ./sqledge.db

.schema
```

Config

All config is read from environment variables. The full list is available in the struct tags on the fields in pkg/config/config.go

sqledge's People

Contributors

Stargazers

Watchers

Forkers

spartakos87 the-alchemist cxz bedros suryatmodulus hien richardsonjf nanderoo blofeldthefish ssun3 qqq-tech daniel-007 super-rain mz0in tantaman gemini-kenshi kanbenn ego muthhus

sqledge's Issues

Getting error while trying to replicate remote PostgreSQL database to local server.

Getting the below error while trying to replicating remote PostgreSQL database to local server. Created DB and user on remote postgres. But when trying to run the replication query getting the below error.

10:43AM FTL failed in replicate error="streaming failed: start slot: start replication: ERROR: unrecognized pgoutput option: messages (SQLSTATE XX000)"
exit status 1

PostgreSQL version: 13.11

Bad assignment of Environment Variable in the Proxy Struct

In pkg/config/config.go on the main branch the SQLEDGE_PROXY_ADDRESS is assigned to Port.
I think the correct env var name would be SQLEDGE_PROXY_PORT

	Proxy struct {
		Address string `env:"SQLEDGE_PROXY_ADDRESS,default=localhost"`
		Port    int    `env:"SQLEDGE_PROXY_ADDRESS,default=5433"`
	}

offline writes?

Any plans on allowing writes to happen while the SQLite DB is disconnected from the remote Postgres DB?

Handle queries not supported by SQLite

Right now, we assume that SQLedge should be used for reads, and writes should be sent to to upstream Postgres.

This means the Postgres connection to SQLedge can only be passed read queries that SQLite supports. Given the connection expects to be contacting a Postgres database we should probably handle reads that SQLite can't serve by routing them back to the upstream Postgres database, instead of using the local SQLite database.

Replicate issue on pg_stat_statements extension.

Upon attempting to connect to the database with pg_stat_statement enabled, we encountered the below error. However, after dropping the extension, we were able to connect to the database without encountering any issues.

12:38PM DBG COPY pg_stat_statements_info TO STDOUT WITH BINARY;
12:38PM ERR copy error error="ERROR: cannot copy from view \"pg_stat_statements_info\" (SQLSTATE 42809)"
12:38PM DBG ROLLBACK
12:38PM DBG ROLLBACK
12:38PM DBG COMMIT
12:38PM FTL failed in replicate error="streaming failed: copy: recover: runtime error: slice bounds out of range [:11] with capacity 0"
exit status 1

Is there any way to run this with pg_stat_statement enabled?

This could probably be off by default, as it will introduce more latency than the write alone (as we wait to received the write back).