PostgreSQL Foreign Data Wrapper FDW

The purpose of this page is to give you examples of how to connect your instance of PostgreSQL to other sources (PostgreSQL databases, other databases, csv files, etc...) using the PostgreSQL Foreign Data Wrapper (FDW) feature. Reasons why I love Foreign Data Wrapper (FDW) feature:

Once the connection is made, you can access your data using SQL. This means that data transformation is as easy as writing "Insert Into ... Select * from ..."
FDW does not keep a tight hold of the connected source (database for example). This make maintaining the remote source easier because you not always have to manage the connection. It seems as though it only connects when needed.
When the remote source is not available, the query fails gracefully.
You can materialize the data locally to remove connectivity dependencies.
Offers a great strategy for replication

CSV Example

The below code will create a foreign data wrapper to a csv file named 'orders_export_1.csv' located on the same server.

CREATE EXTENSION file_fdw;
CREATE SERVER orders_export FOREIGN DATA WRAPPER file_fdw;

--DROP FOREIGN TABLE orders_export;
CREATE FOREIGN TABLE orders_export (
 "Name" text,
 "Email" text,
 "Financial Status" text,
 "Paid at" timestamp,
-- more fields here --
 "Tax 5 Name" text,
 "Tax 5 Value" numeric,
 "Phone" text,
 "Receipt Number" numeric
) SERVER orders_export
OPTIONS ( filename '/home/ubuntu/Desktop/orders_export_1.csv', header 'true', format 'csv' );

Example Connecting to Firebird Database

The below are the commands I used to connect PostgreSQL to an obscure database called firebird:

# commands to install the firebird tools locally
sudo apt-get update
sudo apt-get -y install libfbclient2
sudo apt-get -y install firebird2.5-examples
sudo apt-get -y install firebird-dev
sudo apt-get -y install make
sudo apt -y install gcc
sudo apt-get -y install automake
sudo apt-get -y install git
sudo apt-get -y install postgresql-server-dev-9.3
git clone https://github.com/ibarwick/libfq.git
cd libfq
sudo ./configure 
sudo automake --add-missing
sudo autoreconf
sudo automake --add-missing
sudo make
sudo make install
cd
git clone https://github.com/ibarwick/firebird_fdw.git
cd firebird_fdw/
sudo make
sudo make install
sudo nano /etc/ld.so.conf
# append to end of file: /usr/local/lib
sudo ldconfig
sudo -u postgres psql
## below are executed inside psql
CREATE EXTENSION firebird_fdw;
CREATE FOREIGN DATA WRAPPER firebird HANDLER firebird_fdw_handler VALIDATOR firebird_fdw_validator;
CREATE SERVER firebird FOREIGN DATA WRAPPER firebird_fdw OPTIONS (address 'wincdb', database '/glds/winc/data/training.fdb');
CREATE USER MAPPING FOR adempiere SERVER firebird OPTIONS (username 'SYSDBA', password 'NewBkey');
CREATE SCHEMA fb;

CREATE FOREIGN TABLE IF NOT EXISTS fb.bi_country ( country_id integer, country_name varchar, country_code varchar) SERVER firebird;
SELECT * FROM bi_country;

PostgreSQL Foreign Data Wrapper FDW

CSV Example

Example Connecting to Firebird Database

References