Foreign Data Wrappers

Some database management systems allow to query data from different databases. PostgreSQL implements this capability via the feature known as Foreign Data Wrapper. It allows create "foreign tables" in a PostgreSQL databas that actually are proxies for external data sources. When foreign table is included in a query, Foreign Data Wrapper locates the correspoding data source, extracts the required data and return result as it would come from the current database table.

PostgreSQL offers two foreign data wrappers:

• file_fdw to access flat files (requires Postgres 9.1 and later)
• postgres_fdw to access tables from another PostgreSQL database (requires Postgres 9.3 and later)

This is how to use a Foreign Data Wrapper on example of postgres_fdw:

1. Install the postgres_fdw extension

   CREATE EXTENSION postgres_fdw;

2. Grant privileges to user in the destination (dest_user is a user registered in the destination database server)

   GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw to dest_user;
   

3. Create a server

   CREATE SERVER app_database_server
   FOREIGN DATA WRAPPER postgres_fdw
   OPTIONS (dbname 'database_name', host 'server_name', port '5432');
   

4. Create user mapping from destination user to source user "src_user"

   CREATE USER MAPPING for dest_user
   SERVER server_name
   OPTIONS (user 'src_user', password 'some_password');
   

5. Create foreign table definition in the destination server with the same structure as the source table, but with OPTIONS specifying schema_name and table_name

   CREATE FOREIGN TABLE tbl1(id f1, f2 character varying(20))
   SERVER server_name
   OPTIONS (schema_name 'public', table_name 'tbl1');
   

Now all necessary things are set up and it is reasonable to check if we can query the foreign table:

SELECT * FROM tbl1;

Have questions? Contact us