PostgreSQL的远程数据操作---postgres_fdw

pengjunling

  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=> 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!~