Some time ago I blogged on how to connect your PostgreSQL instance to an Oracle instance (here and here). In this post I’ll do the same with a MariaDB/MySQL instance.

For connecting PostgreSQL to MariaDB/Mysql we’ll need the foreign data wrapper for MySQL. As the foreign data wrapper for MySQL requires the MySQL/MariaDB client and development libraries lets install these first:

[email protected]:/home/postgres/ [pg952] sudo yum install -y mariadb-libs mariadb-devel

Make sure that you have mysql_config as well as pg_config in your PATH:

[email protected]:/home/postgres/ [pg952] which pg_config
[email protected]:/home/postgres/ [pg952] which mysql_config

Once you are ready it is straight forward:

[email protected]:/var/tmp/ [pg952] unzip
[email protected]:/var/tmp/mysql_fdw-master/ [pg952] make USE_PGXS=1
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I/usr/include/mysql -D _MYSQL_LIBNAME="" -I. -I./ -I/u01/app/postgres/product/95/db_2/include/server -I/u01/app/postgres/product/95/db_2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o connection.o connection.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I/usr/include/mysql -D _MYSQL_LIBNAME="" -I. -I./ -I/u01/app/postgres/product/95/db_2/include/server -I/u01/app/postgres/product/95/db_2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o option.o option.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I/usr/include/mysql -D _MYSQL_LIBNAME="" -I. -I./ -I/u01/app/postgres/product/95/db_2/include/server -I/u01/app/postgres/product/95/db_2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o deparse.o deparse.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I/usr/include/mysql -D _MYSQL_LIBNAME="" -I. -I./ -I/u01/app/postgres/product/95/db_2/include/server -I/u01/app/postgres/product/95/db_2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o mysql_query.o mysql_query.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I/usr/include/mysql -D _MYSQL_LIBNAME="" -I. -I./ -I/u01/app/postgres/product/95/db_2/include/server -I/u01/app/postgres/product/95/db_2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o mysql_fdw.o mysql_fdw.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -shared -o connection.o option.o deparse.o mysql_query.o mysql_fdw.o -L/u01/app/postgres/product/95/db_2/lib -Wl,--as-needed -Wl,-rpath,'/u01/app/postgres/product/95/db_2/lib',--enable-new-dtags  
[email protected]:/var/tmp/mysql_fdw-master/ [pg952] make USE_PGXS=1 install
/bin/mkdir -p '/u01/app/postgres/product/95/db_2/lib'
/bin/mkdir -p '/u01/app/postgres/product/95/db_2/share/extension'
/bin/mkdir -p '/u01/app/postgres/product/95/db_2/share/extension'
/bin/install -c -m 755 '/u01/app/postgres/product/95/db_2/lib/'
/bin/install -c -m 644 .//mysql_fdw.control '/u01/app/postgres/product/95/db_2/share/extension/'
/bin/install -c -m 644 .//mysql_fdw--1.0.sql  '/u01/app/postgres/product/95/db_2/share/extension/'

From here on it is the usual steps. Create the extension:

postgres= create extension mysql_fdw;
postgres= dx
                            List of installed extensions
   Name    | Version |   Schema   |                   Description                    
 mysql_fdw | 1.0     | public     | Foreign data wrapper for querying a MySQL server
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)


Make sure you are allowed to connect to your MySQL instance from where your PostgreSQL runs on. In my case I did this in the MariaDB instance:

MariaDB [DS2]> grant all on *.* TO 'web'@'' identified by "web";
Query OK, 0 rows affected (0.00 sec)

| Variable_name | Value |
| port          | 3306  |
1 row in set (0.00 sec)

Once this is ready we can proceed on the PostgreSQL instance:

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw  OPTIONS (host '', port '3306');
CREATE USER MAPPING FOR postgres SERVER mysql_server OPTIONS (username 'web', password 'web');
CREATE schema mysql;
CREATE FOREIGN TABLE mysql.customer(
 CUSTOMERID           bigint
,FIRSTNAME            varchar(50)
,LASTNAME             varchar(50)
,ADDRESS1             varchar(50)
,ADDRESS2             varchar(50)
,CITY                 varchar(50)
,STATE                varchar(50)
,ZIP                  bigint
,COUNTRY              varchar(50)
,REGION               int
,EMAIL                varchar(50)
,PHONE                varchar(50)
,CREDITCARDTYPE       bigint   
,CREDITCARD           varchar(50)
,USERNAME             varchar(50)
,PASSWORD             varchar(50)
,AGE                  int
,INCOME               bigint
,GENDER               varchar(1)
SERVER mysql_server
     OPTIONS (dbname 'DS2', table_name 'CUSTOMERS');

postgres= select count(*) from mysql.customer;
(1 row)


If you wonder where the remote table comes from then have a look at the previous post where the schema is setup in the MariaDB/MySQL instance.

Even better, as with the oracle_fdw (if you are on PostgreSQL 9.5+), you can import the foreign schema:

postgres= import foreign schema "DS2" from server mysql_server into mysql;
postgres= show search_path;
 "$user", public
(1 row)
postgres= set search_path= 'mysql';
postgres= d
               List of relations
 Schema |    Name    |     Type      |  Owner   
 mysql  | CATEGORIES | foreign table | postgres
 mysql  | CUSTOMERS  | foreign table | postgres
 mysql  | CUST_HIST  | foreign table | postgres
 mysql  | INVENTORY  | foreign table | postgres
 mysql  | ORDERLINES | foreign table | postgres
 mysql  | ORDERS     | foreign table | postgres
 mysql  | PRODUCTS   | foreign table | postgres
 mysql  | REORDER    | foreign table | postgres
 mysql  | customer   | foreign table | postgres
(9 rows)

postgres=# select count(*) from "ORDERS";
INFO:  Successfully connected to MySQL database DS2 at server via TCP/IP with cipher  (server version: 5.5.47-MariaDB, protocol version: 10) 
(1 row)

One command and everything is there 🙂

Thumbnail [60x60]
Daniel Westermann