About

Postgres database images with different foreign data wrapper (FDW) extensions installed. Individual images with single FDW installed are used as a building blocks for the all inclusive image which contains all FDWs. Multiple FDWs allow to access data from different by nature datasources within single SELECT statement.

In terms of classical definitions, it turns postgres into a federated database system which implements SQL/MED extension of SQL standard. In more modern terms, it implements data virtualization feature.

This approach is implemented in Datero data platform. It's built on top of postgres database image with multiple FDWs isntalled. It also provides GUI for setting up datasource connections and SQL editor. Without any coding you could quickly setup data hub and start exploring your data.

Product is containerized and thus could be installed on-prem or in any cloud. For more details, please check Datero docs.

Contents

• How It Works
• Demo
• Datero image
  • Available tags
• Individual FDW images
  • Demo
  • Available tags
• Image building
• Image sizing
• Contribution

How It Works

Postgres database has such a nice feature as Foreign Data Wrapper. It allows to access data from some external source. Be it some other database or just file. In case of database it might be SQL or NoSQL one. There are plenty of different open source FDW extensions available.

What this project does is just compile and pack individual FDW extensions into the default postgres image. Afterwards, uses these images to create all inclusive image which contains all FDWs.

Depending on your needs you could use either individual FDW image or all inclusive one. In both cases you will have postgres database with FDW extension(s) available for installation/enablement. All you have to do is enable corresponding extensions, put your credentials to the external datasources and start join them from inside postgres :)

Demo

The most detailed demo is available in Datero tutorial.

A couple of simple demos are available in demo folder:

• MSSQL - Mongo - SQLite
• Oracle - Mysql

Navigate to the demo folder and execute from it docker-compose up -d. It will spin-up a few containers with postgres one at the end. Inside postgres container there will be a view created in public schema. That view will be joining data from foreign tables which are pointed to different source databases.

Datero image

Datero engine image is built on top of individual postgres images with single FDW installed. It's a mix image which contains all supported FDW extensions available for installation.

Included FDWs:

• Oracle
• TDS (MSSQL & Sybase)
• Mysql
• Mongo
• Redis
• SQLite
• Postgres (built-in)
• Flat Files (built-in)

Available image tags

Tag naming pattern corresponds one to one to the official postgres tags.

Please check Tags tab at Docker hub to see custom tags available.

Compatibility Matrix

Table below shows which FDW version is included into which Datero release. If there is no official FDW release available, version could be derived from some branch or commit hash. For example, TDS FDW is built from master branch and Redis FDW is built from REL_16_STABLE branch.

There are also two built-in FDWs are available by default: postgres_fdw and file_fdw. They are part of the official postgres distribution.

Individual FDW images

File naming pattern is as follow:

• postgres_<dbname>.docker
  • Base image building file referenced in docker's documentation as Dockerfile.
• postgres_<dbname>_compose.yml
  • Compose files to showcase a demo how to connect from postgres to different databases such as mysql.

For example, postgres_mysql.docker file specifies postgres database with mysql_fdw extension installed. It will make it listed in pg_available_extensions system view but you still have to install it onto specific database as extension via CREATE EXTENSION command. Consequently, postgres_mysql_compose.yml file launches postgres and mysql databases within the same network as postgres and mysql hosts.

Individual FDWs Demo

• Postgres with MySQL
• Postgres with Oracle
• Postgres with SQLite
• Postgres with MongoDB
• Postgres with MSSQL

Available tags

Tag naming pattern is <postgres_version>_fdw<fdw_version>. For example, 15.2_fdw2.9.0 tag for postgres_mysql_fdw image means postgres 15.2 version with 2.9.0 fdw version installed.

IMPORTANT: Docker doesn't support auto builds feature for free anymore. Also it doesn't show any digest or statistics for manually pushed tags. Nevertheless, these tags are fetchable and safe to use. Please check Tags tab at Docker hub to see custom tags available.

Image building

Note: If you use docker then just replace podman with docker in all commands below.

Build image tagged as postgres_mysql and launch pg_fdw_test container from it

$ podman build -t postgres_mysql -f postgres_mysql.docker

$ podman run -d --name pg_fdw_test -p 5432:5432 -e POSTGRES_PASSWORD=postgres postgres_mysql
6d6beb18e5b7036c058b2160bb9b57adf9011301658217abf67bea64471f5056

$ podman ps
CONTAINER ID  IMAGE                            COMMAND   CREATED        STATUS            PORTS                   NAMES
6d6beb18e5b7  localhost/postgres_mysql:latest  postgres  4 seconds ago  Up 4 seconds ago  0.0.0.0:5432->5432/tcp  pg_fdw_test

Login into the database and check that mysql_fdw is available for installation

$ podman exec -it pg_fdw_test psql postgres postgres

psql (12.4)
Type "help" for help.

postgres=# select * from pg_available_extensions where name = 'mysql_fdw';
   name    | default_version | installed_version |                     comment
-----------+-----------------+-------------------+--------------------------------------------------
 mysql_fdw | 1.1             |                   | Foreign data wrapper for querying a MySQL server
(1 row)

Image sizing

Table below shows additional size of the decompressed images compared to the official postgres image. Each FDW is compiled from sources. There are cleanup commands are executed after the compilation to minimize the image size. But there is no guarantee that it will cleanup everything. Hence, added size is not 100% consist of actual compiled FDW binaries.

Surprisingly, all-inclusive datero_engine image is identical in size to the postgres_oracle_fdw image. This is probably because that oracle_fdw image generates way more temporary files during the build process. And these files are not easily identifiable and removable.

In addition, oracle_fdw requires some oracle client to be present on the host machine. This adds 250 MB to the image size.

Anyway, datero_engine image contains all FDWs.

Contribution

Any contribution is highly welcomed. If you implementing new fdw image please keep corresponding file names accordingly to described pattern.

If you want to request some image to be prepared feel free to raise an issue for that. List of available FDW implementations could be found on official postgres wiki.