PostgreSQL提供了外部数据包装器postgres_fdw,作用跟dblink相同，即查询远程数据库中的数据信息，但是 postgres_fdw比dblink在某些场景更稳定、更方便。同时PostgreSQL也提供对其他数据库如Oracle和MySQL等数据库的外 部数据包装器:oracle_fdw和mysql_fdw，可查询Oracle和MySQL数据库中的相关表信息。
注意，不论使用PG的哪种外部数据包装器，尽可能的保证两端的表中字段的数量、类型和顺序一致，否则可能导致很多问题。
下面我们来体验一下该功能：
测试环境准备：
在远程数据库上创建新的数据库musician，并在库里创建表man，插入测试数据：
postgres=#
create database musician;
CREATE DATABASE
music=#
\c musician eric
您现在已经连线到数据库 "musician",用户 "eric".
musician=>
create table man(id bigint);
CREATE TABLE
musician=>
insert into man select * from generate_series(1,8000);
INSERT 0 8000
musician=>
select count(*) from man;
count
-------
8000
(1 行记录)
musician=>
\d
关联列表
架构模式 | 名称 | 型别 | 拥有者
----------+------+--------+--------
public | man | 资料表 | eric
(1 行记录)
在本地测试库安装插件postgres_fdw：
postgres=#
create extension postgres_fdw;
CREATE EXTENSION
music=>
\c music postgres
You are now connected to database "music" as user "postgres".
创建外部服务器对象，需要指定相关信息：
对象名称：musician_fdw_server
包装器类型：postgres_fdw，如果要连接Oracle或者MySQL数据库的话，可用oracle_fdw或mysql_fdw
主机IP:192.168.1.129
数据库名称：musician（刚刚创建的数据库名）
端口号：5432
music=#
create server musician_fdw_server foreign data wrapper postgres_fdw options (host '192.168.1.129',dbname 'musician',port '5432');
CREATE SERVER
创建用户映射，相关信息：
本地用户：eric
外部服务器对象：musician_fdw_server
远程数据库用户名密码：eric,gao
music=#
create user mapping for eric server musician_fdw_server options (user 'eric',password 'gao');
CREATE USER MAPPING
配置外部表,相关信息：
外部表在本库的名称：manid
外部服务器：musician_fdw_server
外部表名：man
music=>
\c music postgres
You are now connected to database "music" as user "postgres".
music=#
create foreign table manid(id bigint) server musician_fdw_server options(table_name 'man');
CREATE FOREIGN TABLE
注意：
在远程数据库的pg_hba.conf中修改一下相关的配置：
最终这样修改的：
# IPv4 local connections:
host all all 192.168.1.0/24 md5
因为远程连接的话，PG要求是需要有密码验证的，设置成trust的话会报错。
设置完成之后验证一下查询效果：
music=>
\c music eric
You are now connected to database "music" as user "postgres".
music=#
select count(*) from manid;
count
-------
8000
(1 row)
验证一下删除和插入操作：
从本地删除远程数据库musician中表man的所有数据：
music=>
\c music postgres
You are now connected to database "music" as user "postgres".
music=#
delete from manid;
DELETE 10000
在远程数据库执行查询数据条目：
musician=>
select count(*) from man;
count
-------
0
(1 行记录)
数据已全部清除。
从本地向远程数据库musician中的表man插入1万条数据：
music=#
insert into manid select * from generate_series(1,10000);
INSERT 0 10000
在远程数据库中看到1万条数据已入账：
musician=>
select count(*) from man;
count
-------
10000
(1 行记录)
数据是可以看到了，性能如何呢？我们来测试一下：
在远程数据库本地执行语句：
musician=>
explain analyze select count(*) from man;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Aggregate (cost=136.00..136.01 rows=1 width=0) (actual time=26.128..26.129 rows=1 loops=1)
-> Seq Scan on man (cost=0.00..116.00 rows=8000 width=0) (actual time=0.014..13.068 rows=8000 loops=1)
Planning time: 0.045 ms
Execution time:
26.189 ms
(4 行记录)
在本地数据库本地执行语句：
music=>
explain analyze select count(*) from manid;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Aggregate (cost=220.92..220.93 rows=1 width=0) (actual time=42.804..42.804 rows=1 loops=1)
-> Foreign Scan on manid (cost=100.00..212.39 rows=3413 width=0) (actual time=2.264..41.813 rows=8000 loops=1)
Planning time: 0.067 ms
Execution time:
44.411 ms
(4 rows)
看起来差别不是太大，但是测试的数据量和类型也不复杂，那我们接下来换一条语句：
远程数据库本地执行语句：
musician=>
explain analyze select * from man;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on man (cost=0.00..116.00 rows=8000 width=8) (actual time=0.012..10.277 rows=8000 loops=1)
Planning time: 0.036 ms
Execution time:
18.758 ms
(3 行记录)
本地数据库本地执行语句：
music=>
explain analyze select * from manid;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Foreign Scan on manid (cost=100.00..186.80 rows=2560 width=8) (actual time=14.445..60.194 rows=8000 loops=1)
Planning time: 12.400 ms
Execution time:
64.936 ms
(3 rows)
看起来差别还是比较明显的，更别提用到量大且复杂的生产环境中了。如果是该查询用的不频繁并且查询的量不大不复杂，客户也可以忍受响应速度，那这样就OK。
如果对响应速度有相对较高的要求，则需要使用另一种武器：物化视图。
物化视图可以理解为是对目标表格的一个副本，可能是一模一样的，也可能是经过筛选的。本次咱们为了改善性能，简单的创建一个跟远程数据库表格一模一样的物化视图：
在本地数据库创建物化视图：
物化视图名称为：mv_manid，通过该视图保存manid表能查到的数据的实体：
music=>
create materialized view mv_manid as select * from manid;
SELECT 8000 ---数据条目跟刚才一样为8千条
查看一下物化视图的性能如何：
music=>
explain analyze select * from mv_manid;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on mv_manid (cost=0.00..113.04 rows=7704 width=8) (actual time=0.024..1.823 rows=8000 loops=1)
Planning time: 0.254 ms
Execution time:
2.864 ms
(3 rows)
music=>
explain analyze select count(*) from mv_manid;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Aggregate (cost=132.30..132.31 rows=1 width=0) (actual time=1.336..1.336 rows=1 loops=1)
-> Seq Scan on mv_manid (cost=0.00..113.04 rows=7704 width=0) (actual time=0.010..0.738 rows=8000 loops=1)
Planning time: 0.032 ms
Execution time:
1.363 ms
(4 rows)
比manid的强不少吧？~~~
物化视图需要对表进行刷新才能同步远程表的数据：
在远程数据库表里插入新数据：
musician=>
insert into man select * from generate_series(8001,10000);
INSERT 0 2000
musician=>
select count(*) from man;
count
-------
10000
(1 行记录)
本地库查询发现还是8千条数据：
music=>
select count(*) from mv_manid;
count
-------
8000
(1 row)
刷新一下本地的物化视图即可看到新进来的数据：
music=>
refresh materialized view mv_manid;
REFRESH MATERIALIZED VIEW
music=>
select count(*) from mv_manid;
count
-------
10000
(1 row)
OK!~
