Foreign Data Wrappers in PostgreSQL
Foreign Data Wrapper (FDW) is new concept which introduced in PostgreSQL 9.1. It allows us to access external non-Postgres data in the form of regular Postgres table. FDW allow us to make external data sources (text files, web services, etc.) look like tables and have easy access to them using SQL commands.
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.
Uses :
1) Urgent Data Migration
2) Data Analysis
3) Testing Purpose
There are various uses for FDW and in many Cases. Below are some examples:
Urgent Data Migration
Imagine that you have a project which uses a database that costs a lot of money. At some time in the project life-cycle 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 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, We can use 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.
Testing Purpose
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.
To use the FDW there’s four basic things you’ll want to do:
Create Extension
Create the Remote server
Create a user mapping for the Remote server
Create your foreign tables
Start querying on data
Example : Here is an example of creating a foreign table with postgres_fdw.
Create Extension First install the extension:
CREATE EXTENSION postgres_fdw;
Create the Foreign server
Then create a foreign server using CREATE SERVER. In this example we wish to connect to a PostgreSQL server on host 192.83.123.89 listening on port 5432. The database to which the connection is made is named foreign_db on the remote server:
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host ‘192.83.123.89’, port ‘5432’, dbname ‘foreign_db’);
Create a user mapping for the Foreign server
A user mapping, defined with CREATE USER MAPPING, is needed as well to identify the role that will be used on the remote server:
CREATE USER MAPPING FOR local_user
SERVER foreign_server
OPTIONS (user ‘foreign_user’, password ‘password’);
Create your foreign tables
Now it is possible to create a foreign table with CREATE FOREIGN TABLE. In this example we wish to access the table named some_schema.some_table on the remote server. The local name for it will be foreign_table:
CREATE FOREIGN TABLE foreign_table (
id integer NOT NULL,
data text )
SERVER foreign_server
OPTIONS (schema_name ‘some_schema’, table_name ‘some_table’);
Start querying on data
SELECT * FROM foreign_table LIMIT 5;
Sample Implementation :
CREATE FOREIGN DATA WRAPPER w_fdw;
ALTER FOREIGN DATA WRAPPER w_fdw OWNER TO Sachin;
— Foreign Server: wserver
— DROP SERVER wserver
CREATE SERVER wserver
FOREIGN DATA WRAPPER w_fdw
OPTIONS (hostaddr ‘127.0.0.1’,dbname ‘Basket_one’,port ‘5434’);
ALTER SERVER backupserver OWNER TO Sachin;
— User Mappings
CREATE USER MAPPING FOR Sachin SERVER wserver
OPTIONS (username ‘foo’, password ‘bar’);
Create a table to hold the data in PostgreSQL as foreign table
CREATE FOREIGN TABLE Employee (
Employee_id int,
Employee_name text )
SERVER wserver
OPTIONS (dbname ‘Senteno’, table_name ‘Employee’ );
Best Open Source Business Intelligence Software Helical Insight is Here
A Business Intelligence Framework
Thanks & Regards
A Prasanth Kumar
PostgreSQL