Home > Documentation > PostgreSQL

Foreign Data Wrappers

Some database management systems allow to query data from different databases. PostgreSQL implements this capability via the feature known as Foreign Data Wrapper. It allows create "foreign tables" in a PostgreSQL databas that actually are proxies for external data sources. When foreign table is included in a query, Foreign Data Wrapper locates the correspoding data source, extracts the required data and return result as it would come from the current database table.

PostgreSQL offers two foreign data wrappers:

This is how to use a Foreign Data Wrapper on example of postgres_fdw:

  1. Install the postgres_fdw extension
    CREATE EXTENSION postgres_fdw;
  2. Grant privileges to user in the destination (dest_user is a user registered in the destination database server)
    GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw to dest_user;
    
  3. Create a server
    CREATE SERVER app_database_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (dbname 'database_name', host 'server_name', port '5432');
    
  4. Create user mapping from destination user to source user "src_user"
    CREATE USER MAPPING for dest_user
    SERVER server_name
    OPTIONS (user 'src_user', password 'some_password');
    
  5. Create foreign table definition in the destination server with the same structure as the source table, but with OPTIONS specifying schema_name and table_name
    CREATE FOREIGN TABLE tbl1(id f1, f2 character varying(20))
    SERVER server_name
    OPTIONS (schema_name 'public', table_name 'tbl1');
    

Now all necessary things are set up and it is reasonable to check if we can query the foreign table:

SELECT * FROM tbl1;