Foreign data wrapper & create foreign table in PostgreSQL

Foreign data wrapper is a very useful feature in PostgreSQL. It allows you create a foreign table that accesses data stored in the external sources. So when you query data from this foreign table, it retrieves data from the external data sources and returns as if the data is actually coming from the foreign table.

There are two different built-in foreign data wrappers in PostgreSQL:

file_fdw to create foreign tables to access data stored in flat files
postgres_fdw to create foreign tables to access data stored in other PostgreSQL servers.

In this post, we will learn how to create foreign table to access data from external PostgreSQL server using postgres_fdw extension:

1. Install the postgres_fdw extension: 

CREATE EXTENSION postgres_fdw;

2. Create a server:

CREATE SERVER server_name 
FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS (host 'host_ip', dbname 'db_name', port 'port_number');

3. Create USER MAPPING:

CREATE USER MAPPING FOR CURRENT_USER
SERVER server_name
OPTIONS (user 'user_name', password 'password');

 4. Then create a schema in the working database to proxy data from the external PostgreSQL Server:

CREATE SCHEMA schema_name;

5. Import server schema:

IMPORT FOREIGN SCHEMA external_schema
FROM SERVER external_server
INTO schema_name;

And now you can be able to query data from the external PostgreSQL server through the foreign tables in the newly created schema:

SELECT * FROM schema_name.table_name;

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.