Have you ever felt the need to combined data from two databases or files in a simple way? PostgreSQL has a useful feature called Foreign Data Wrapper, which lets you create foreign tables in a PostgreSQL database that link other data sources. Queries happen near real time against a foreign table, the Foreign Data Wrapper will query the external data source and return the results as if they were coming from a table in your database.
After successfully implementing one such solution, I felt obliged to share these quick steps of how to set up postges mysql_fdw in Ubuntu.
What is a Foreign Data Wrapper(FDW)
The foreign data wrapper are libraries responsible for fetching data from the remote data source and returning it to the postgresql. FDWs allow you to connect to other data sources from within Postgres. From there you can query them with SQL, join across disparate data sets, or join across different systems.
Step 1: Download the postgres mysql_fdw extension from git hub
$ git clone https://github.com/enterprisedb/mysql_fdw.git -- DOWNLOAD mysql_fdw from git
Step 2: cd into the directory where the code was downloaded
$ cd mysql_fdw -- cd into the mysql_fdw folder
Step 3: Compile the code using make
$ make USE_PGXS=1
Step 4: Install the foreign data wrapper
Make USE_PGXS=1 install
Step 5: Log into postgres
Psql -U “username” -d “databasename” -p “port”
Step 6: Create the extension in postgres. CREATE EXTENSION loads a new extension into the current database
CREATE EXTENSION mysql_fdw;
Step 7: Define a new foreign-data wrapper. The foreign-data wrapper name must be unique within the database. CREATE SERVER defines a new foreign server.
CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'xx.xxx.xx.xxx’, port ‘xxxx’);
Step 8: CREATE USER MAPPING defines a mapping of a user to a foreign server. A user mapping typically encapsulates connection information that a foreign-data wrapper uses together with the information encapsulated by a foreign server to access an external data resource.
CREATE USER MAPPING FOR “postgres username”
SERVER “Give your foreign server a name”
OPTIONS (username 'type mysql username', password 'type mysql password’);
CREATE FOREIGN TABLE “table name”(id smallint,advert_id smallint, created_at timestamp)
SERVER mysql_server OPTIONS (dbname 'remote mysql db name, table_name 'mysql table name');