centos 6.8 + postgresql 9.6 + postgres_fdw

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 
--------------+----------+------------+--------------+--------+------------
 postgres_fdw |       10 |    2284382 |      2284383 |        | 
(1 row)

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)

create server

创建外部服务器

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=# create user mapping for peiyb server abc_postgresql_fdw_server options (user 'peiyb', password 'peiybpeiyb');

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 zlfund server abc_postgresql_fdw_server;
drop server abc_postgresql_fdw_server;
drop extension postgres_fdw;