PostgreSQL Foreign Data Wrappers

Hi all,

I would like to mention about generating Foreign Data Wrappers(FDW) on PostgreSQL 9.6.

FDW is used for remote access to tables from external data storage. If needed to use remote table in a query, you can use FDW tables. For instance you can get a table from remote database, but there is a condition. The condition is that user should have proper privileges on FDW table.

There is two extension for FDW on PostgreSQL. First one is used for accessing PostgreSQL database to PostgreSQL database, called postgres_fdw. Second one is used for accessing PostgreSQL database to different databases (SQL Server, Sysbase) called tds_fdw. 

Foreign Data Wrappers feature lets you to cross-query between remote database tables. Postgres_fdw and tds_fdw has different structure. Tds_fdw usign Tabular Data Stream application layer protocol to transfer data between database server and client. Generating Tds_fdw and Postgres_fdw is similar. I share an example for generating FDW between two PostgreSQL databases end of this article. You can use these script for your FDW.

Why we need to use FDW? 

• Getting data from remote database
• More clear syntax for access remote tables.
• SELECT, INSERT, UPDATE, DELETE statements with FDW tables.
• Possible import whole schema.
• Possible to use join statement to remote table with data on internal server.

There is some restrictions

• FDW is so poor with INNER JOIN statements in 9.6. 
• FDW architecture so different from dblink. When using FDW tables with some aggregate function in your scripts, FDW access gets whole table data to target database and than calculate or applying conditions. That's why FDW is poor on 9.6. To avoid this case you can use Common Table Expressions. 
• If your main table is altered, you should alter your FDW table. It's so manual.
• You shouldn't miss main table's column when you create FDW table. If you miss some column while create FDW table, PostgreSQL doesn't care about it and it let you create FDW table. You can select FDW table without this column/columns. So you have to be aware about it. 
• Data type of columns of main table must be same FDW table ones. If not, It doesn't work.

There is four steps to maintain FDW.

1. Install extension
• Sharing data between two PostgreSQL databases
• CREATE EXTENSION postgres_fdw;
• Between different databases like from PostgreSQL to others SQL Server/Sysbase
• CREATE EXTENSION tds_fdw;
2. Create SERVER connection
•  For postgres_fdw extension
• CREATE SERVER <server_name> FOREIGN DATA WRAPPER postgres_fdw options(...)
• For tds_fdw extension 
• CREATE SERVER <server_name> FOREIGN DATA WRAPPER tds_fdw options(...)
3. Create USER MAPPING
• For postgres_fdw and tds_fdw both are same
  
• CREATE USER MAPPING FOR postgres SERVER <server_name> OPTIONS(...)
4. Create FDW table 
• For postgres_fdw and tds_fdw both are same
  
• CREATE FOREIGN TABLE interns (column1,column2..)

CREATE SERVER

After installing postgres_fdw extension, we need to create a server. Via CREATE SERVER command, database users can access foreign database with used options. So, you need to be sure host and port informations in options well. There is few options to create server as user, password, host, port, dbname.

Guess there is postgreSQL server on host 192.80.80.80, listening on port 5496 and user called gunce can access test_db database. For creating SERVER you can use following script;

CREATE SERVER tesfdw_server 
FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS (host '192.80.80.80', port '5496', user 'gunce', password 'password', dbname 'test_db');

You can check your FDW setup with SQL statement.

select * from pg_foreign_server;

By the way, working on same host is not necessary. You should add host and port information in option.

Alter Server;
Possible to alter created SERVER.
ALTER SERVER <server_name> OPTIONS (ADD|SET|DROP);

Add new connection options;
ALTER SERVER testfdw_server options (host '192.80.80.81', port '5432');
 
Change port option;
ALTER SERVER testfdw_server options (SET port '5444');

Drop server;
To drop server you can use following script;
DROP SERVER <server_name> ;

CREATE USER MAPPING

Next step is creating USER MAPPING after create SERVER. We create user mapping cause the user will reach database with given privileges. So you need to add user name and password to access foreign database in options is necessary.

The synopsis for creating user mapping is like bellow;

CREATE USER MAPPING FOR <user_name> 
SERVER <server_name> OPTIONS (options ..);

User, password, dbname, host, port can be options. 

CREATE USER MAPPING FOR postgres 
SERVER testfdw_server
OPTIONS (host '192.80.80.81', port '5432', user 'gunce', password 'password');

Alter USER MAPPING;
You can change options which is used in create user mapping script and also add or drop options. Just decide SET|DROP|ADD options on USER MAPPING.

ALTER USER MAPPING FOR postgres
SERVER testfdw_server
OPTIONS (SET host '192.165.10.10');

Or

ALTER USER MAPPING FOR postgres
SERVER testfdw_server
OPTIONS (ADD host '192.165.10.10', port '54322', user 'gunce', password 'password');

Or

ALTER USER MAPPING FOR postgres
SERVER testfdw_server
OPTIONS (drop host '192.165.10.10', port '54322', user 'gunce', password 'password');

Drop USER MAPPING;
DROP USER MAPPING FOR postgres SERVER testfdw_server;

CREATE FDW

Now, we are on final step. There is few way to do it. Creating FDW tables individually or creating based on a schema like generate fdw all of tables in that schema.

When we create a foreign table, we are importing data from foreign database. Of course this is not physical importing but, with this step, the user we used in user mapping before is going to access foreign database. That's the point what we are trying. 

So, If you want to create a single FDW table, be careful cause main table's column data type and FDW table's one should be same. You don't have to add all  columns of main table for FDW table. If miss a column which is main table has, you won't get error, but you will not be able to see this column in FDW side. So you can see whole columns without missed one. 

Basically you can ass FDW table as bellow;

CREATE FOREIGN TABLE <table_name>
(<column1> data_type, <column2> data_type, ...) 
SERVER <server_name>
OPTIONS (option values..);

For instance;


CREATE FOREIGN TABLE fdw_tbl_test(id serial, name character varing) SERVER testfdw_server OPTIONS(host '192.160.80.80', port '5432');

Import foreign schema;

If you don't prefer to create foreign tables individually, you can import foreign tables based on schema. Creating FDW schema help us to generate all foreign tables under that schema in foreign database. 

IMPORT FOREIGN SCHEMA <schema_name> FROM SERVER <server_name> INTO <fdw_schema_name>;
 
You can also limit based on table.

IMPORT FOREIGN SCHEMA dummy LIMIT TO (<table1>, <table2>) FROM SERVER dummy_ INTO <schema_name>;

Drop Foreign Tables;
It is similar with dropping a regular table. Follow next line to do it.
DROP FOREIGN TABLE <fdw_table>;

I'm going to generate two different PostgreSQL 9.6 cluster as bellow. I'm gonna set port numbers as 54962 and 54963. When connect database with port number is 54963, we will be able to access a remote table which is in database with port 54962.
 
Check your Foreign Tables 
You can check your foreign tables with view.
 
select * from pg_foreign_table;
It doesn't matter even if you create foreign table individually or using import schema option. You'll see whole table created both of individually or based on schema. 

With Foreign Table you can perform INSERT, UPDATE, DELETE with SELECT. But If you don't want to user perform INSERT, UPDATE, DELETE statement, you can alter server or foreign table.

ALTER FOREIGN TABLE <table_name> OPTIONS (ADD updatable 'false');

ALTER SERVER <server_name> OPTIONS (ADD updatable 'false');


That's all. Let's start for example!

Generating first cluster. 

/usr/pgsql-9.6/bin/initdb -D /var/lib/pgsql/9.6-2Before starting this service, I changed port number as 54962. Because I don't want to cause conflict between two PostgreSQL cluster when starting services. 

I generate second cluster and after initilization cluster I change port number as 54963.
 
/usr/pgsql-9.6/bin/initdb -D /var/lib/pgsql/9.6-3
I generate a table in first cluster, cluster's port number is 54962.

[postgres@gunce ~]$ psql -p54962
psql (11devel, server 9.6.8)
Type "help" for help.

[postgres] # CREATE DATABASE dummy;
CREATE DATABASE
 
In first PostgreSQL cluster, I'm going to generate a table, role and grant privileges on that table for generated user. This table is going to be FDW table.

Change database
[postgres] # \c dummy
psql (11devel, server 9.6.8)
You are now connected to database "dummy" as user "postgres".

Generate a role
[dummy] # CREATE ROLE gunce WITH LOGIN PASSWORD 'password';
CREATE ROLE

Create a table. This going to be our foreign table in second cluster later.[dummy] # CREATE TABLE interns (id SERIAL, fname CHARACTER VARYING(100),lname CHARACTER varying (100), major CHARACTER VARYING(50), status BOOLEAN);
CREATE TABLE

Then grant correct privileges our future FDW table.
[dummy] # GRANT ALL PRIVILEGES ON interns TO gunce;
GRANT

Just insert a value, later, we'll check FDW table.
[dummy] # INSERT INTO interns (fname,lname,major,status) VALUES('pınar','unnamed','engineering',true);
INSERT 0 1

Now, apply whole steps I mentioned in beginning of this article on second PostgreSQL 9.6 cluster.

[dummy] # CREATE EXTENSION postgres_fdw;
CREATE EXTENSION

You can create your server with different options. I created both of cluster in same server. So I don't need to use host for this example. If your clusters established in different servers you need to use a one more option called host. Then you can add in your CREATE SERVER script.
[dummy] # CREATE SERVER dummy_ FOREIGN DATA WRAPPER postgres_fdw options (dbname 'dummy',port '54962');
CREATE SERVER

[dummy] # CREATE USER MAPPING FOR postgres SERVER dummy_ OPTIONS (user 'gunce', password 'password');
CREATE USER MAPPING

[dummy] # CREATE FOREIGN TABLE interns(id serial, fname character varying(100),lname character varying (100), major character varying(50), status boolean) SERVER dummy_;
CREATE FOREIGN TABLE

In second PostgreSQL 9.6 cluster, select interns FDW table ans see;

[dummy] # SELECT * FROM interns;
 id | fname |  lname  |    major    | status
----+-------+---------+-------------+--------
  1 | pınar | unnamed | engineering | t
(1 row)
 
Looks perfect!


Loves,