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.

Answer 1

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.

Answer 2

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.