Postgres 2 SQLite database link
Postgres foreign data wrapper for SQLite database
This is part 3 of
There is no binaries available on official GitHub repository for this extension. You have to manually compile and install it. Procedure is quite simple. Also it requires only single sqlite3 libs package to be installed as a runtime dependency.
When done with installation you will be able to setup it already within postgres database. Process is standard: execute
CREATE EXTENSION command from within your postgres database. Aftewards create foreign server via
CREATE SERVER command which specifies path to the target sqlite database file.
Similarly to MySQL and Oracle I've created a postgres image with
sqlite_fdw extension installed.
It's available on docker hub.
Because of single library runtime dependency it is only +1 Mb in size comparing to the smallest official postgres alpine image.
Section below describes how to use the image.
SQLite is a serverless database which consists of a single file. For working example we need to spin up container from the mentioned above image and mount sqlite database file to it as a mount target. Thus we will get local sqlite database with write access granted to a postgres container with
sqlite_fdw extension installed.
sqlite database we will create a single table and populate it with 3 rows.
postgres instance we will select, insert and delete rows from this table.
After that we will select from this table again via
sqlite client locally to confirm that the data has been changed accordingly.
Note: If you use
docker then just replace
docker in all commands below.
Let's create sqlite database
$ sqlite3 test.db SQLite version 3.32.3 2020-06-18 14:00:33 Enter ".help" for usage hints.
Within the database create table
t and populate it with 3 rows.
sqlite> create table t(id int primary key); sqlite> insert into t values (1), (2), (3); sqlite> .headers on sqlite> select * from t; id 1 2 3
postgres database with
sqlite_fdw extension available from our
$ podman run -d --name sqlite_test -u $(id -u):$(id -g) \ -v $(pwd)/test.db:/tmp/test.db -p 5432:5432 \ -e POSTGRES_PASSWORD=postgres toleg/postgres_sqlite_fdw $ podman ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 57a3d931b446 docker.io/toleg/postgres_sqlite_fdw:latest postgres 3 seconds ago Up 3 seconds ago 0.0.0.0:5432->5432/tcp sqlite_test
By default container is triggered as a
Container will update local sqlite database file
test.db. We want to make sure that it will update it with privileges of a current user.
For this purpose we specify to start container as a current user
-u $(id -u):$(id -g).
We also mount local
test.db file as
/tmp/test.db file within the container.
Finally container is named as
In the same time within container postgres itself is owned by separately created
In order to allow
postgres account from within container to change externally created file in a host system we grant write access to others via
$ chmod o+w test.db $ ls -l test.db -rw-r--rw-. 1 toleg toleg 12288 жов 22 22:15 test.db
Start within our container named
psql shell connected to default
postgres database as default
postgres superuser account.
$ podman exec -it sqlite_test psql postgres postgres psql (13.0) Type "help" for help. postgres=#
Create foreign server to access sqlite database.
t foreign table object which will be pointed to the same named table in the
options (key 'true') clause to tell
sqlite_fdw extension that foreign table has PK/UK constraint defined.
This enables delete and update operations on a table.
=> create schema sqlite_fdw; CREATE SCHEMA => create extension sqlite_fdw schema sqlite_fdw; CREATE EXTENSION => create server sqlite foreign data wrapper sqlite_fdw options (database '/tmp/test.db'); CREATE SERVER => create foreign table t(id int options (key 'true')) server sqlite; CREATE FOREIGN TABLE
Select data from the foreign table.
=> select * from t; id ---- 1 2 3 (3 rows)
DML operations also work
=> insert into t values (4); INSERT 0 1 => delete from t where id = 1; DELETE 1 => select * from t; id ---- 2 3 4 (3 rows)
Check that local database file on a host system has changed accordingly.
sqlite> select * from t; id 2 3 4
For quicker setup of the mentioned layout there is a postgres_sqlite_compose.sh script available.
sqlite_fdw extension allows
DML operations to be triggered from postgres and applied to sqlite database.
For quickier setup there is a
toleg/postgres_sqlite_fdw docker image is available on docker hub.
sqlite_fdw extension pre-installed as well as
sqlite3 client utility.