os:centos 6.8
db:postgresql 9.6

fdw：foreign data wrapper，postgresql的外部数据包装器。
postgres_fdw 是用于postgresql的数据库之间连接，原因是postgresql不能直接跨库访问，被设计用来替代dblink。
这么口语化的描述，大家应该一下子就清楚了。

还有个问题，使用dblink时必须把用户密码写进去，导致几乎没有安全性。postgres_fdw 对使用者而言，是看不到密码的。只有superuser才能看到密码。安全性大大提高。

postgres=# select * from pg_available_extensions p where 1=1 and p.name = 'postgres_fdw';

     name     | default_version | installed_version |                      comment                       
--------------+-----------------+-------------------+----------------------------------------------------
 postgres_fdw | 1.0             |                   | foreign-data wrapper for remote PostgreSQL servers
(1 row)

postgres=> select * from pg_foreign_data_wrapper;

 fdwname | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions 
---------+----------+------------+--------------+--------+------------
(0 rows)

create extension

创建扩展

postgres=# create extension postgres_fdw;
CREATE EXTENSION

postgres=# select * from pg_extension p where 1=1;

   extname    | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
--------------+----------+--------------+----------------+------------+-----------+--------------
 plpgsql      |       10 |           11 | f              | 1.0        |           | 
 postgres_fdw |       10 |         2200 | t              | 1.0        |           | 
(2 rows)

postgres=> select * from pg_foreign_data_wrapper;

   fdwname    | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions 
--------------+----------+------------+--------------+--------+------------
 postgres_fdw |       10 |    2284382 |      2284383 |        | 
(1 row)

create server

创建外部服务器

postgres=# \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' [, ... ] ) ]
    
postgres=# create server abc_postgresql_fdw_server foreign data wrapper postgres_fdw options (host '127.0.0.1', port '5432', dbname 'abc');
CREATE SERVER

create user mapping

创建用户映射

postgres=# \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' [ , ... ] ) ]

postgres=# create user mapping for peiyb server abc_postgresql_fdw_server options (user 'peiyb', password 'peiybpeiyb');

create user mapping for 后面那个用户名是本地的用户名.

superuser 可以灵活使用 fdw,但是对于普通账号的用户,需要授权

postgres=# grant usage on foreign data wrapper postgres_fdw to peiyb;

postgres=# grant usage on foreign server abc_postgresql_fdw_server to peiyb;

create foreign table

创建外部表

postgres=# create foreign table tmp_peiyb_20180509 (
        userid int8
        )
        server abc_postgresql_fdw_server
        options (schema_name 'public', table_name 'tmp_t0');
        
postgres=# select * from tmp_peiyb_20180509 limit 10;

可以查询下列的表:

select * from pg_foreign_table;
select * from pg_user_mapping;
select * from pg_foreign_server;
select * from pg_foreign_data_wrapper;
select * from pg_extension;

删除

drop foreign table tmp_peiyb_20180509;
drop user mapping for peiyb server abc_postgresql_fdw_server;
drop server abc_postgresql_fdw_server;
drop extension postgres_fdw;