Skip to content

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 🙂

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Open source Team

Open source Team

Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.View Author posts

721 posts since September 2011

Most read in Database

The Database posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *