PostgreSQL Foreign Data Wrapper tutorial

A PostgreSQL Foreign Data Wrapper (FDW) allows you to write queries against a remote database.

Considerations

While some FDW tradeoffs are not immediately apparent, at least a few come to mind more readily.

Schema drift

The remote schema may change. However, the FDW schema is generated at a single point in time (based on the remote schema). This may lead to the FDW schema being out of sync with the remote schema.

One solution to this issue would be to re-generate the FDW schema on a periodic basis.

User permissions

Each database user needs an individual user mapping for the remote database server. This may not be an issue on a single-user system, but can be a bit more annoying in a multi-user environment.

Queries

The following sections outline the queries necessary to connect to a remote database, create a mirror schema, and assign user permissions to the foreign data source.

Create FDW extension

Make sure PostgreSQL has the FDW extension enabled.

create extension if not exists postgres_fdw;

Create FDW server

Create the FDW server by giving it a local name and remote connection details.

create server <fdw_server_name>
foreign data wrapper postgres_fdw
options (host 'dbms.example.com', dbname 'database_name');

Create mirror schema

Create an empty schema in the local database and populate it with matching table definitions from the remote database schema.

create schema <local_schema_name>;

import foreign schema <remote_schema_name>
from server <fdw_server_name>
into <local_schema_name>;

Create FDW user mapping

For each user who needs to interact with the foreign server, we need to create a user mapping.

The local user will have the same permissions on the FDW server as the remote user defined in the user mapping.

create user mapping for <user_name>
server <fdw_server_name>
options (user 'remote_user_name', password 'remote_user_password');

References

• PostgreSQL (current) documentation - postgres_fdw module
• thoughtbot - PostgreSQL's Foreign Data Wrapper