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

  注意，不论使用PG的哪种外部数据包装器，尽可能的保证两端的表中字段的数量、类型和顺序一致，否则可能导致很多问题。

下面我们来体验一下该功能：

在远程数据库上创建新的数据库musician，并在库里创建表man，插入测试数据：

postgres=#

CREATE DATABASE

music=#

您现在已经连线到数据库 "musician",用户 "eric".

musician=>

CREATE TABLE

musician=>

INSERT 0 8000

musician=>

 count 

-------

  8000

(1 行记录)

musician=>

             关联列表

 架构模式 | 名称 |  型别  | 拥有者 

----------+------+--------+--------

 public   | man  | 资料表 | eric

(1 行记录)

postgres=#

CREATE EXTENSION

music=>

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

本地用户：eric

外部服务器对象：musician_fdw_server

远程数据库用户名密码：eric,gao

music=#  

CREATE USER MAPPING

外部表在本库的名称：manid

外部服务器：musician_fdw_server

外部表名：man

music=>

You are now connected to database "music" as user "postgres".

music=#

CREATE FOREIGN TABLE

注意：

  在远程数据库的pg_hba.conf中修改一下相关的配置：

最终这样修改的：

# IPv4 local connections:

host    all           all            192.168.1.0/24              md5

因为远程连接的话，PG要求是需要有密码验证的，设置成trust的话会报错。

music=>

You are now connected to database "music" as user "postgres".

music=#

 count 

-------

  8000

(1 row)

在远程数据库本地执行语句：

musician=>

                                                 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:

(4 行记录)

在本地数据库本地执行语句：

music=>

                                                     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:

(4 rows)

看起来差别不是太大，但是测试的数据量和类型也不复杂，那我们接下来换一条语句：

远程数据库本地执行语句：

musician=>

                                              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:

(3 行记录)

本地数据库本地执行语句：

music=>

                                                  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:

(3 rows)

看起来差别还是比较明显的，更别提用到量大且复杂的生产环境中了。如果是该查询用的不频繁并且查询的量不大不复杂，客户也可以忍受响应速度，那这样就OK。

物化视图可以理解为是对目标表格的一个副本，可能是一模一样的，也可能是经过筛选的。本次咱们为了改善性能，简单的创建一个跟远程数据库表格一模一样的物化视图：

物化视图名称为：mv_manid，通过该视图保存manid表能查到的数据的实体：

music=>

SELECT 8000   ---数据条目跟刚才一样为8千条

查看一下物化视图的性能如何：

music=>

                                                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:

(3 rows)

music=>

                                                   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:

(4 rows)

比manid的强不少吧？~~~

在远程数据库表里插入新数据：

musician=>

INSERT 0 2000

musician=>

 count 

-------

 10000

(1 行记录)

本地库查询发现还是8千条数据：

music=>

 count 

-------

  8000

(1 row)

刷新一下本地的物化视图即可看到新进来的数据：

music=>

REFRESH MATERIALIZED VIEW

music=>

 count 

-------

 10000

(1 row)