Setup Mysql Foreign Data Wrapper in Postgresql

Question

Could somebody help me understand how I would go about setting up postgresql's mysql_fdw? I'm looking at https://github.com/EnterpriseDB/mysql_fdw, and I not sure what the first step is.

Petition

Answer 1

You linked to the ODBC foreign data wrapper odbc_fdw. You can use it for MySQL, but if you just want MySQL you should use mysql_fdw instead. That way you don't have to mess with ODBC.

Handily, the README for mysql_fdw contains detailed instructions for installation and configuration, so you should be fine setting it up; I won't duplicate those instructions here.

Nares

Thanks for replying and pointing out the wrong URL. Based on the README file, I was not able to deduce the install procedures. The first step is explained as "Install MySQL, or just the C client library"... how do I install the library? – Petition

@Petition Depends on your operating system. What're you running? (It's nice if you mention this and your PostgreSQL version in all questions where possible). You need to install the mysql client libraries. On Fedora that'd be yum install community-mysql-devel or yum install mariadb-devel. On CentOS/RHEL, yum install mysql-devel. On Debian/Ubuntu, apt-get install libmysqlclient-dev. – Nares

Thanks Craig, I'm running CentOS. As per your recommendation I had to install mysql-devel, as well as postgresql93-devel.x86_64 and gcc. After that, downloaded the Makefile, mysql_fdw.c, mysql_fdw--1.0.sql, mysql_fdw.control to a temp directory. Next, copied mysql_fdw--1.0.sql and mysql_fdw.control to /usr/pgsql-9.3/share/extension. At this point I was able to execute "PATH=/usr/pgsql-9.3/bin/:/usr/bin/:$PATH make USE_PGXS=1". Now in psql when I execute "CREATE EXTENSION mysql_fdw" I get "ERROR: could not access file "$libdir/mysql_fdw": No such file or directory" – Petition

Sounds like you forgot to make install - or in this case probably sudo PATH=/usr/pgsql-9.3/bin:$PATH make install . BTW for future reference rather than downloading individual files you can git clone the repository. – Nares

Answer 2

I was able to do it the following way.

Installing the package:

sudo apt-get install postgresql-9.5-mysql-fdw

Adds the extension in the Database:

CREATE EXTENSION mysql_fdw;

Add the mysql server to postgresql:

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'localhost', port '3306');

Create a user to access the database:

CREATE USER MAPPING FOR postgres SERVER mysql_server OPTIONS (username 'root', password 'passwordToConnect');

Import a Schema table from another server

IMPORT FOREIGN SCHEMA mySchema LIMIT TO (tableName) FROM SERVER mysql_server INTO public;

Imports all Schema tables from another server

IMPORT FOREIGN SCHEMA mySchema FROM SERVER mysql_server INTO public;

I hope I have helped.

Frey

this saved a lot of time for me – Cowrie