Skip to content

Postgres 2 MySQL database link

MySQL FDW

Postgres foreign data wrapper for MySQL database

Foreign data access

This is the first part of #fdw foreign data wrappers series of posts.

One of the great features in Postgres is the ability to access data stored in outer sources as if they were stored in ordinary tables within the database. There is no requirement to the source as is. For example, it can be another RDBMS such as Oracle or MySQL. But we are not limited to relational databases only. It can be also just a csv file or even Hadoop file system objects. All you need to do to get access to these data is to install corresponding foreing data wrapper extension.

Default Postgres installation bundle inlcudes only two foreign data wrappers: file_fdw and postgres_fdw. With file_fdw you could access files stored outside of a database with the condition that they are accessible to postgres o/s process running the database instance. In turn postgres_fdw allows to connect to other postgres databases (or to itself). This form of connection creates what is known as database link object in some other RDBMS such as Oracle. One particular use case of connecting to itself in postgres is implementation of async logging or autonomous transactions.

mysql_fdw

In order to connect to MySQL database from Postgres it's required to manually compile and install mysql_fdw extension to your postrges database. After doing this you will be able to execute CREATE EXTENSION command from within your postgres database and setup foreign server metadata via CREATE SERVER command with target mysql server details.

Docker image

Manually compiling extension from source files may be somehow challenging. Especially if you didn't do this before. To simplify the things you could use one of the docker images available which already have mysql_fdw extension installed.

I've created such image as well. You could pick it up here. It's built upon latest official postgres alpine docker image. At the time of writing it was corresponding to postgres 12.4 version. Section below describes how to use the image.

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 MySQL and one for Postgres. Inside mysql instance we will create a single table and populate it with 3 rows. From postgres instance we will select, insert and delete rows from this table. After that we will select from this table under mysql instance again to confirm that the data has been changed accordingly.

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

Let's start mysql and postgres instances. It will create inside mysql instance dev database with single t(id int) table and populate it with three values 1, 2, 3. 

$ podman-compose -f postgres_mysql_compose.yml up -d
$ podman ps
CONTAINER ID  IMAGE                                      COMMAND               CREATED         STATUS             PORTS                                         NAMES
2326d38f1dda  docker.io/chumaky/postgres_mysql_fdw:latest  postgres            9 seconds ago   Up 9 seconds ago   0.0.0.0:3306->3306/tcp                        fdw_postgres_1
57667e289a05  k8s.gcr.io/pause:3.2                                             12 seconds ago  Up 11 seconds ago  0.0.0.0:3306->3306/tcp                        9c812f7c78fa-infra
dc75f8a3f1fb  docker.io/library/mysql:latest             mysqld                11 seconds ago  Up 10 seconds ago  0.0.0.0:3306->3306/tcp                        fdw_mysql_1

Note: Both containers are prefixed with fdw_. This is default behavior when created containers are prefixed with the directory name where compose file is located. If you cloned or stored repo into a different directory name it will be prefixed with that name instead.

Connect to fdw_postgres_1 container and select from t table stored in mysql database. From within postgres it's accessible as an external table t stored in mysql schema. 

$ podman exec -it fdw_postgres_1 psql postgres postgres

postgres=> select * from mysql.t;
 id
----
  1
  2
  3
(3 rows)

DML operations also work 

postgres=> insert into mysql.t values (4);
INSERT 0 1
postgres=> delete from mysql.t where id = 1;
DELETE 1
postgres=> select * from mysql.t;
 id
----
  2
  3
  4
(3 rows)

From within fdw_mysql_1 container we can observer already changed data 

$ podman exec -it fdw_mysql_1 mysql -uroot -proot

mysql> use dev;
Database changed

mysql> select * from t;
+----+
| id |
+----+
|  2 |
|  3 |
|  4 |
+----+
3 rows in set (0.00 sec)

It should be added that in order to enable DML operations against foreign table corresponding source table in mysql 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 mysql database.

Comments