Data is not always in just one location. More often than not, data is spread all over the infrastructure, and it may be that data residing in various places has to be integrated.

The solution to this problem is a foreign data wrapper, as defined by the SQL/MED standard.

In this section, the postgres_fdw extension will be discussed. It is a module that allows us to dynamically fetch data from a PostgreSQL data source. The first thing we need to do is deploy the foreign data wrapper:

test=# h CREATE FOREIGN DATA WRAPPER 
Command: CREATE FOREIGN DATA WRAPPER
Description: define a new foreign-data wrapper
Syntax:
CREATE FOREIGN DATA WRAPPER name
     [ HANDLER handler_function | NO HANDLER ]
     [ VALIDATOR validator_function | NO VALIDATOR ]  
     OPTIONS ( option 'value' [, ... ] ) ]

Fortunately, the CREATE FOREIGN DATA WRAPPER command is hidden inside an extension; it can easily be installed using the normal process, as follows:

test=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION

Now, a virtual server has to be defined. It will point to the other host and tell PostgreSQL where to get the data. At the end of the data, PostgreSQL has to build a complete connect string—the server data is the first thing PostgreSQL has to know about. User information will be added later on. The server will only contain the host, port, and so on. Following is the syntax of CREATE SERVER:

test=# h CREATE SERVER    
Command:     CREATE SERVER
Description: define a new foreign server
Syntax:
CREATE SERVER [ IF NOT EXISTS ] server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ]
    FOREIGN DATA WRAPPER fdw_name
    [ OPTIONS ( option 'value' [, ... ] ) ]

URL: https://www.postgresql.org/docs/12/sql-createserver.html

To understand how this works, we will create a second database on the same host and create a server:

[hs@zenbook~]$ createdb customer
[hs@zenbook~]$ psql customer
customer=# CREATE TABLE t_customer (id int, name text);
CREATE TABLE
customer=# CREATE TABLE t_company (
    country      text, 
    name         text, 
    active       text
);
CREATE TABLE

customer=# d
List of relations
 Schema    | Name       | Type   | Owner
-----------+------------+--------+------- 
 public    | t_company  | table  | 
 hs public | t_customer | table  | hs

(2 rows)

Now, the server should be added to the standard test database:

test=# CREATE SERVER customer_server 
          FOREIGN DATA WRAPPER postgres_fdw 
          OPTIONS (host 'localhost', dbname 'customer', port '5432');
CREATE SERVER

Note that all the important information is stored as an OPTIONS clause. This is somewhat important because it gives users a lot of flexibility. There are many different foreign data wrappers, and each of them will need different options.

Once the server has been defined, it is time to map users. If we connect from one server to the other, we may not have the same user in both locations. Therefore, foreign data wrappers require people to define the actual user mapping:

test=# h CREATE USER MAPPING     
Command:     CREATE USER MAPPING
Description: define a new mapping of a user to a foreign server
Syntax:
CREATE USER MAPPING [ IF NOT EXISTS ] FOR { user_name | USER | CURRENT_USER | PUBLIC }
    SERVER server_name
    [ OPTIONS ( option 'value' [ , ... ] ) ]
URL: https://www.postgresql.org/docs/12/sql-createusermapping.html

The syntax is pretty simple and can be used easily:

test=# CREATE USER MAPPING 
          FOR CURRENT_USER SERVER customer_server 
          OPTIONS (user 'hs', password 'abc');
CREATE USER MAPPING

Again, all of the important information is hidden in the OPTIONS clause. Depending on the type of foreign data wrapper, the list of options will differ. Note that we have to use proper user data here, which will work for our setup. In this case, we will simply use local users.

Once the infrastructure is in place, we can create foreign tables. The syntax to create a foreign table is pretty similar to how we would create a normal local table. All of the columns have to be listed, including their data types:

test=# CREATE FOREIGN TABLE f_customer (id int, name text) 
              SERVER customer_server 
              OPTIONS (schema_name 'public', table_name 't_customer');
CREATE FOREIGN TABLE

All of the columns are listed, just like in the case of a normal CREATE TABLE clause. The special thing here is that the foreign table points to a table on the remote side. The name of the schema and the name of the table have to be specified in the OPTIONS clause.

Once it has been created, the table can be used:

test=# SELECT * FROM f_customer ;
 id  | name
-----+------
(0 rows)

To check what PostgreSQL does internally, it is a good idea to run the EXPLAIN clause with the analyze parameter. It will reveal some information about what's really going on in the server:

test=# EXPLAIN (analyze true, verbose true)
        SELECT * FROM f_customer ;
                              QUERY PLAN 
-----------------------------------------------------------------------
 Foreign Scan on public.f_customer 
     (cost=100.00..150.95 rows=1365 width=36) 
     (actual time=0.221..0.221 rows=0 loops=1) 
   Output: id, name 
   Remote SQL: SELECT id, name FROM public.t_customer 
 Planning time: 0.067 ms 
 Execution time: 0.451 ms 
(5 rows) 

The important part here is Remote SQL. The foreign data wrapper will send a query to the other side and fetch as little data as possible, since as many restrictions as possible are executed on the remote side to ensure that not much data is processed locally. Filter conditions, joins, and even aggregates can be performed remotely (as of PostgreSQL 10.0).

While the CREATE FOREIGN TABLE clause is surely a nice thing to use, it can be quite cumbersome to list all of those columns over and over again.

The solution to this problem is the IMPORT clause. This allows us to quickly and easily import entire schemas onto a local database, as well as create foreign tables:

test=# h IMPORT             
Command:     IMPORT FOREIGN SCHEMA
Description: import table definitions from a foreign server
Syntax:
IMPORT FOREIGN SCHEMA remote_schema
    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
    FROM SERVER server_name
   INTO local_schema
    [ OPTIONS ( option 'value' [, ... ] ) ]

URL: https://www.postgresql.org/docs/12/sql-importforeignschema.html

IMPORT allows us to link large sets of tables easily. It also reduces the chances of typos and mistakes since all of the information is fetched directly from the remote data source.

The following is how it works:

test=# IMPORT FOREIGN SCHEMA public 
       FROM SERVER customer_server INTO public;
IMPORT FOREIGN SCHEMA

In this case, all of the tables that were created previously in the public schema are linked directly. As we can see, all of the remote tables are now available:

test=# det
List of foreign tables
 Schema  | Table      | Server
---------+------------+-----------------
 public  | f_customer | customer_server 
 public  | t_company  | customer_server 
 public  | t_customer | customer_server

(3 rows)