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
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
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 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 allows you to read from various NoSQL databases.
- couchdb_fdw provides access to CouchDB
- redis_fdw provides access to Redis
- mongo_fdw provides access to MongoDB
- rethinkdb_fdw provides access to RethinkDB
- wdb_fdw provides access to WhiteDB
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 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)
- 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.