A PostgreSQL Foreign Data Wrapper (FDW) allows you to write queries against a remote database.
While some FDW tradeoffs are not immediately apparent, at least a few come to mind more readily.
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.
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.
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.
Make sure PostgreSQL has the FDW extension enabled.
create extension if not exists postgres_fdw;
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 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>;
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');