Back to articles list
- 5 minutes read

Foreign Data Wrappers for PostgreSQL

Foreign Data Wrapper (FDW) is one of the big features that came with PostgreSQL 9.1. Simply, it allows you to access external non-Postgres data as if it were a regular Postgres table. Postgres FDW is an implementation of a decade-old SQL/MED (Management of External Data) standard in PostgreSQL that contains the information on how to allow databases to make external data sources (text files, web services, etc.) look like tables and have easy access to them using SQL commands.

In detail, FDW is a C library that acts like an adapter. Each foreign data wrapper is responsible for interacting with a particular external data. For example, it can be used in Finance Department reports as a CSV that you need to reconcile to account or in any data migration.

How are Foreign Data Wrappers Useful?

There are various uses for FDW and in many situations it can be almost indispensable. Here are some examples:

Data Migration Needed Immediately!

For example, imagine that you have a project which uses a database that costs a lot of money. At some time in the project lifecycle it may happen that the most expensive features that the database provides aren't used and there is no need to put a lot of money into something that can be replaced by an open source database like PostgreSQL. You can use an appropriate FDW to migrate your data from your costly external DB to Postgres.

Data for Analysis

You may find yourself in a situation where you have to perform some database analytics for an external partner from PostgreSQL. Instead of writing scripts to update the data, thanks to FDW, you can set up foreign tables and keep them updated. And since the new version for PostgreSQL 9.3 provides write capability, FDW for MySQL is write-able.

We Want Data for Testing

If we don't want to affect real data we can retrieve the data and manage it locally without interaction in the main database. This can be useful in performing tests.

FDW Between PostgreSQL Databases

A common use for FDW is to manage data between a few Postgres databases. This resolves problems encountered when you have a few smaller projects that use the data from one central database.

Example of Usage

The full list of different FDW is quite huge, but their usage is quite similar. Let's see how you can use FDW to access MySQL data from PostgreSQL.

Create a database for all tables

CREATEDB mysql_fdw

Log into the database and run the following

CREATE EXTENSION mysql_fdw;

Create a server that points to a remote database

CREATE SERVER mysql_svr
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (address '127.0.0.1', port '3306');

Create user connection parameters to the mysql database

CREATE USER MAPPING FOR postgres
SERVER mysql_server
OPTIONS (username 'foo', password 'bar');

Create a table to hold the data in PostgreSQL as foreign table

CREATE FOREIGN TABLE warehouse(
     warehouse_id int,
     warehouse_name text,
     warehouse_created datetime)
SERVER mysql_server
     OPTIONS (dbname 'db', table_name 'warehouse');

That all of the necessary steps. You can now run SQL statements and play with the table to check that, indeed, the data is fetched from MySQL tables and viewed in Postgres.

Write your own FDW

Of course you can write your own Foreign Data Wrappers. One way you can do this is using Multicorn. It's a PostgreSQL extension that allows you to write FDW in Python, developed at Kozea by Florian Mounier and Ronan Dunklau.

See how easily it can be done :) Ronan Dunklau presented it in his presentation at PGCon 2014 “Multicorn: writing FDWs in Python” or follow this tutorial: Writing an FDW

FDW Overview

In a nutshell, there are various Foreign Data Wrappers you can use.

The list below presents some of them. Most of them are dedicated for PostgreSQL 9.1+, which was released with read-only support of SQL/MED standard. Write-support was added in version 9.3.

RDBMS fdw

RDBMS fdw allows you to access data from various database engines.

  • mysql_fdw allows you to retrieve (and also write) data from a MySQL database (available for PostgreSQL 9.3+)
  • oracle_fdw provides access to an Oracle database.
  • sqlite_fdw provides access to SQLite
  • postgres_fdw provides access to PostgreSQL
  • odbc_fdw provides access for remote databases using Open Database Connectivity (ODBC)
  • jdbc_fdw allows you to retrieve data from a remote database using a JDBC driver
  • firebird_fdw provides access to Firebird
  • monetdb_fdw provides access to MonetDB
  • tds_fdw provides access to databases that use the Tabular Data Stream (TDS) Protocol such as Sybase databases and MS SQL Server

NoSQL fdw

NoSQL fdw allows you to read from various NoSQL databases.

Format fdw

Format fdw allows you to read from files in different formats

  • json_fdw provides access to JSON files
  • file_fdw enables you to read data from files, e.g., csv
  • multicdr_fdw represents CDR files stream as a table

Web fdw

Web fdw allows you to read data from data sources over the Internet

  • www_fdw enables you to query different web services, for example Google search
  • s3_fdw reads files located in Amazon S3 (Amazon Simple Storage Service)

Others

  • twitter_fdw - Foreign Data Wrapper for twitter search - It enables you to fetch text messages from Twitter over the Internet and returns the results as a table.
  • docker_fdw enables you to retrieve data from Docker.
  • hdfs_fdw enables you to retrieve data from Hadoop
  • dataclips_fdw fetches text messages from the Heroku Postgres dataclips server (based on twitter_fdw)
  • RSS_fdw enables you to access items from an RSS feed.
  • git_fdw enables you to access GIT repositories
  • ldap_fdw gives access to query LDAP servers
  • cstore_fdw extension which implements a Columnar Store

The list of currently available FDW is published on Wikipedia

It's said that FDW turned the “Internet of Things” into a “Database of Things.” Why “database of things?” The long list of FDW and ability to write your own definitely answers the question. Briefly, Foreign Data Wrapper is a bridge between Postgres and any place you want to get data from. Instead of incorporating logic into your application to talk to all available data sources, you can simply have an application talking to PostgreSQL which is talking to all the different data sources. For anyone who doesn’t already know about this feature, it is definitely a top one to get familiar with.

go to top