toleg/postgres_sqlite_fdw repository overview

⁠DEPRECATION NOTICE

This project is archived. It will be available for pulling but no updates will be pushed anymore.

Project is migrated to a new place: https://hub.docker.com/r/chumaky/postgres_sqlite_fdw⁠

It's made a part of Datero⁠ data platform. For docker source files, please check GitHub⁠ repo.

⁠About

Postgres database image with different foreign data wrapper extensions installed. Multiple FDWs allow to execute Heterogeneous SQL over different by nature databases within single SELECT statement!

This project creates two types of images. Individual ones and single all inclusive image. Individual images contain single FDW installed. While all inclusive image contains all supported FDWs. Heterogeneous SQL as a feature is available only with all inclusive image.

IMPORTANT:

⁠Contents

• All inclusive image⁠
  • Available tags⁠
• Docker/Image files⁠
  • Initialization files⁠
  • Available tags⁠
• Image building⁠
• Demos⁠
• Contribution⁠

⁠All inclusive image

All inclusive image is built on top of individual postgres images⁠ with single FDW installed. It's a mix image which contains all supported FDWs available for installation. It makes it possible to query data from different by nature databases within single SELECT statement. Which in fact implements Heterogeneous SQL feature.

Included FDWs:

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

⁠Available image tags

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

⁠Docker/Image files

• 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.

⁠Initialization files

sql folder contains initialization files that simplifies creation of foreign data wrapper extension and acessing data from an external database. Naming pattern is as follow:

• <dbname>_setup.sql
  • Create non-postgres database and populate it with some data
• postgres_<dbname>_setup.sql
  • Create foreign data wrapper extension from within postgres to connect to <dbname> and access data from it.
• postgres_all_setup.sql
  • Create all available foreign data wrapper extensions within postgres in a separate schemas. Applicable only for All inclusive image⁠

⁠Available tags

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

⁠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)

⁠Demos

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

⁠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⁠.