PostgreSQL foreign data wrapper with postgres_fdw

Setting up postgres_fdw

Using postgres_fdw requires 4 steps:

• Creating the extension
• Creating the server
• Creating a user mapping
• Importing the tables

Before setting up the foreign data wrapper connection, you should test the connection to the remote server from the server used for the foreign data wrapper:

$ psql -h remote-server.com -U remote_user my_database
Password for user remote_user: 
psql (10.3 (Debian 10.3-1.pgdg90+1))

my_database=> 

Once you have ensured that the connection is working correctly, you can create the extension, the server and the user mapping:

-- Create extension
CREATE EXTENSION postgres_fdw;

-- Create server
CREATE SERVER fdw_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote-server.com', dbname 'my_database');

-- Create user mapping
CREATE USER MAPPING FOR postgres -- or another user!
  SERVER fdw_server
  OPTIONS (user 'remote_user', password 'my_password');

Creating a test table

Now, let's create a test table on our remote server:

-- Create table
CREATE TABLE remote_table (
    id serial primary key,
    num int,
    date timestamp with time zone
);

-- Add indexes
CREATE INDEX ON remote_table (num);
CREATE INDEX ON remote_table (date);

-- Populate with some data
INSERT INTO remote_table (num, date)
SELECT floor(random() * 10 + 1)::int, generate_series(NOW() - INTERVAL '1000 days', NOW(), '1 day'::interval);

-- Grant SELECT privileges to "remote_user"
GRANT SELECT ON ALL TABLES IN SCHEMA public TO remote_user;

Creating foreign tables

Automatic import

To import all tables from the remote server:

-- Create new schema
CREATE SCHEMA fdw;

-- Import the schema
IMPORT FOREIGN SCHEMA public
  FROM SERVER fdw_server
  INTO fdw;

-- Test query
SELECT * FROM fdw.remote_table LIMIT 2;
 id | num |             date              
----+-----+-------------------------------
  1 |   5 | 2015-08-13 18:32:15.549739+04
  2 |  10 | 2015-08-14 18:32:15.549739+04
(2 rows)

You can also limit the import to specific tables:

IMPORT FOREIGN SCHEMA public LIMIT TO (remote_table, remote_table_2)
  FROM SERVER fdw_server
  INTO fdw;

Creating the schema manually

If you don't want to automate the schema import, you can re-declare the table schema:

-- Create new schema
CREATE SCHEMA fdw;

-- Create foreign table
CREATE FOREIGN TABLE fdw.remote_table (
    id serial, -- no "primary key" here!
    num int,
    date timestamp with time zone
)
SERVER fdw_server
OPTIONS (schema_name 'public', table_name 'remote_table');

-- Test query
SELECT * FROM fdw.remote_table LIMIT 2;
 id | num |             date              
----+-----+-------------------------------
  1 |   5 | 2015-08-13 18:32:15.549739+04
  2 |  10 | 2015-08-14 18:32:15.549739+04
(2 rows)