Skip to content

Postgres 2 MariaDB database link

MySQL FDW

Postgres foreign data wrapper for MariaDB database

Introduction

After quite a pause we continue the #fdw foreign data wrappers series of posts. In this post we will deal with MariaDB database access from within Postgres.

Since MariaDB is a fork of MySQL database, the same mysql_fdw foreign data wrapper extension can be used to connect to it. So, we will re-use the setup from mysql_fdw example. The only difference is that we will use MariaDB database instead of MySQL. Also since there is some time have passed the test scripts are evolved and improved.

Demo

In order to run commands below you should download or clone GitHub repo. It also contains readme file with project layout and files descriptions.

Demo infrastructure is spinned up via compose files. For our case we need two instances. One for MariaDB and one for Postgres. Inside mariadb instance we will create a single users table and populate it with 3 rows. From postgres instance we will select, insert, update and delete rows from this table. After that we will select from this table under mariadb instance again to confirm that the data has been changed accordingly.

Let's start mariadb and postgres instances. 

$ docker compose -f postgres_mariadb_compose.yml up -d
$ docker ps
CONTAINER ID   IMAGE                               COMMAND                  CREATED          STATUS          PORTS                                       NAMES
a220ab51c289   chumaky/postgres_mysql_fdw:latest   "docker-entrypoint.s…"   50 seconds ago   Up 50 seconds   0.0.0.0:5432->5432/tcp, :::5432->5432/tcp   postgres
c391a1fe0beb   mariadb                             "docker-entrypoint.s…"   50 seconds ago   Up 50 seconds   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp   mariadb

During mariadb instance startup it will create dev database and users table with 3 rows. 

$ docker logs mariadb
2025-05-04 22:47:09+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/mysql_setup.sql
Where mysql_setup.sql file contains the following commands: 
create database dev;
use dev;
create table users(id int, name varchar(10));
insert into users values (1, 'John'), (2, 'Mary'), (3, 'Peter');
alter table users add constraint primary key users_pk(id);

During startup of the postgres container it creates mysql_fdw extension and foreign data wrapper. Afterwards it connects to mariadb instance, waits in a loop until it's got initialized and imports dev schema into local public schema.

$ docker logs postgres
/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/postgres_mysql_setup.sql
CREATE EXTENSION
CREATE SERVER
CREATE USER MAPPING
2025-05-04 22:47:04.843 UTC [58] WARNING:  mysql db is not initialized yet. sleeping for 5 seconds...
2025-05-04 22:47:04.843 UTC [58] CONTEXT:  PL/pgSQL function inline_code_block line 15 at RAISE
postgres_mysql_setup.sql:27: WARNING:  mysql db is not initialized yet. sleeping for 5 seconds...
2025-05-04 22:47:09.849 UTC [58] WARNING:  mysql db is not initialized yet. sleeping for 5 seconds...
2025-05-04 22:47:09.849 UTC [58] CONTEXT:  PL/pgSQL function inline_code_block line 15 at RAISE
postgres_mysql_setup.sql:27: WARNING:  mysql db is not initialized yet. sleeping for 5 seconds...
postgres_mysql_setup.sql:27: INFO:  schema "dev" succesfully imported
DO

Once it's done we check current state of the users table in mariadb instance. 

postgres_mysql_setup.sql:72: NOTICE:  users table row count: 3
postgres_mysql_setup.sql:72: NOTICE:  before:
postgres_mysql_setup.sql:72: NOTICE:  id=1, name=John
postgres_mysql_setup.sql:72: NOTICE:  id=2, name=Mary
postgres_mysql_setup.sql:72: NOTICE:  id=3, name=Peter

Then we do consequent INSERT, UPDATE and DELETE operations from within postgres instance. 

postgres_mysql_setup.sql:72: NOTICE:  operations:
postgres_mysql_setup.sql:72: NOTICE:  insert into users (id, name) values (4, 'asdf');
postgres_mysql_setup.sql:72: NOTICE:  update users set name = 'qwer' where id = 2;
postgres_mysql_setup.sql:72: NOTICE:  delete from users where id = 1;

Finally, we check the state of the users table in mariadb instance again. As we can see the data has been changed accordingly. 

postgres_mysql_setup.sql:72: NOTICE:  after:
postgres_mysql_setup.sql:72: NOTICE:  id=2, name=qwer
postgres_mysql_setup.sql:72: NOTICE:  id=3, name=Peter
postgres_mysql_setup.sql:72: NOTICE:  id=4, name=asdf
DO

Note: All these tests are performed as part of postgres container startup.

From within mariadb container we can observer already changed data 

$ docker exec -it mariadb mariadb -uroot -proot

MariaDB [(none)]> use dev;
Database changed

MariaDB [dev]> select * from users;
+----+-------+
| id | name  |
+----+-------+
|  2 | qwer  |
|  3 | Peter |
|  4 | asdf  |
+----+-------+
3 rows in set (0.001 sec)

It should be added that in order to enable DML operations against foreign table corresponding source table in mariadb database must have a PK or UK constraint defined. Otherwise only SELECT is allowed.

Summary

We have described foreign data feature in Postgres database. It was demonstrated on dockerized postgres image with mysql_fdw extension installed that SELECT and DML operation are allowed to be triggered from Postgres and applied to MariaDB database.

Comments