Connecting your PostgreSQL instance to a MariaDB/MySQL instance

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:

postgres@pgreporting:/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:

postgres@pgreporting:/home/postgres/ [pg952] which pg_config
/u01/app/postgres/product/95/db_2/bin/pg_config
postgres@pgreporting:/home/postgres/ [pg952] which mysql_config
/usr/bin/mysql_config

Once you are ready it is straight forward:

postgres@pgreporting:/var/tmp/ [pg952] unzip mysql_fdw-master.zip
postgres@pgreporting:/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="libmysqlclient.so" -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="libmysqlclient.so" -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="libmysqlclient.so" -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="libmysqlclient.so" -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="libmysqlclient.so" -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 mysql_fdw.so 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  
postgres@pgreporting:/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  mysql_fdw.so '/u01/app/postgres/product/95/db_2/lib/mysql_fdw.so'
/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;
CREATE EXTENSION
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)

postgres=

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'@'192.168.22.39' identified by "web";
Query OK, 0 rows affected (0.00 sec)

MariaDB [DS2]> SHOW GLOBAL VARIABLES LIKE 'PORT';
+---------------+-------+
| 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 '192.168.22.41', 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)
,CREDITCARDEXPIRATION 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;
 count  
--------
 200000
(1 row)

postgres=# 

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;
IMPORT FOREIGN SCHEMA
postgres= show search_path;
   search_path   
-----------------
 "$user", public
(1 row)
postgres= set search_path= 'mysql';
SET
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 192.168.22.41 via TCP/IP with cipher  (server version: 5.5.47-MariaDB, protocol version: 10) 
 count  
--------
 120000
(1 row)

One command and everything is there 🙂