10 PostgreSQL 表级复制-物化视图篇, 支持异地,异构如 Oracle 到 pg 的物化视图|学习笔记-阿里云开发者社区

开发者学堂课程【PostgreSQL 快速入门：10 PostgreSQL 表级复制-物化视图篇,支持异地,异构如 Oracle 到 pg 的物化视图】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/16/detail/69

10 PostgreSQL 表级复制-物化视图篇,支持异地,异构如 Oracle 到 pg 的物化视图

物化视图也可以用做 PostgreSQL 的表级复制。

如图，上节课讲了通过触发器来实现 Postgre 的两个数据库之间的数据复制，使触发器的场景可以支持同步或者异步的复制。数据量比较大的情况或者变更频繁使用触发器来做这种复制就不太合理，因为如果使用同步的触发器来做复制的话，大的、高并发的这变更插入、删除或者更新会对复制会产生比较大的延迟，因为每笔操作都需要实时同步到远端的节点。物化视图对数据的实时性要求不高的复制场景可以选择使用。Postgre 从9.3版本开始加入了物化识图的功能，目前9.3版本支持全量刷新，当我们创建完物化视图后，如果组控那边发生变更，在物化视图这个节点，就要把此变更劝取过来，等于做一次全量的刷新，但是刷新的时候会阻断所有的定量操作，包括 select 的操作，所以在9.3版本物化视图的还不是非常成熟，实际应用还需要等待一段时间。比如说到9.4版本之后可能支持并行的刷新，就是在刷新的时候同时可以去读取物化视图的数据。

此图是9.3版本物化视图的语法，首先是创建物化视图，物化视图可以指定存储参数，然后指定存放的表空间，因为物化视图就是存储数据，不是普通的视图。普通的视图存在 Circle语句，在查询的时候已经写好了 Circle 语句，但是物化视图是这个数据已经写出 Circle 语句，然后把它接过来存放到指定的表里面。所以说物化视图的存储是单独的。

指定表空间的是 AS query，举例：物化视图的查询语句。我们在创建物化视图时，如果是 WITH DATA，就在创建的时候把数据取过来;WITH NO DATA 就是创建的时候只把物化视图的结构创造好，但是不去取数据。

图下部是刷新物化视图的语法，就是 REFRESH MATERIALIZED VIEW（物化视图的名称），其中 WITH DATA 就是把数据发过来。

在9.4版本里面增加了物化存储的刷新过程，要求基表必须要有唯一间值，包括物化视图的基表也要建唯一间值，因此能通过唯一间值去加速比较和刷新。以9.4版本为例（9.3版本不支持增量刷新，只是全量刷新）。

首先要安装 PostgreSQL9.4

软件包可以到 git.postgresql.org 去下载，老师下载的是图示中最新的镜像，把它传到服务器上去。基表什么版本都可以，例如在9.3版本上面做基表就可以。

后面会讲到基于外部表的异地物化视图，先看本地的物化视图。等代码上传好之后我们先安装 postgreSQL9.4。

上图为传完代码图片，接下来进行解压安装。

在编译过的版本能够看到它的编译参数，上图标注为我们使用的编译参数来安装 postgreSQL9.45（老师之前安装过9.4版本）。然后端口要进行修改，应用未监听的端口。

图示，其中5433还没有被监听，我们应用5433。

接下来根据图示指定 block大小，打开 gmake 和 debug，方便后续做 debug 的操作。

图示为安装好 gmake&&gmake install 的代码。接下来到 contrib 下面去做 gmake，把所有 contrib 编译好，方便后续应用。这个是必不可少的，如果是9.3版本都可以用 gmake install 简化 word 的方式一次性安装好。

图示为安装好的代码。

然后到pg 94这里修改它的配置文件和监听端口,将5432改成5433，因为上面监听的是5433端口。把目录中的内容删除，initdb 指定 PGDATA，初始化完成后，修改pg_hba，改成 host all all 0.0.0.0/0 md5，因为是测试，我们就全部放开。接下来，监听要全部打开，将 unix_socket 改成本地目录，跟测试没太大关系的不需要改动，可以将运行内存改2048MB（老师内存为96GB）；共享内存的类型是 posix，修改成 sysv，用其他也可以，但 sysv 要修改参数；stderr 修改成 csvlog；

按照图示启动数据库，9.4版本安装完毕。

然后创建一个本地表tbl，往里面插入10万条测试数据；创建物化视图，按照id<1000作为 mv，from tbl where id<1000 with no data,此时不能查询它，因为还没有数字化，所以必须要 refresh materialized view_tbl;

此时会出现999条数据。那么物化视图的结构是在系统视图里面。

此图能看见物化视图结构的定义，现在物化视图没有创建索引，现在给它创建一个索引，比如说可以给它加一个约束：alter materialized view add constraint pk_mv_tbl primary key<id>;它不能在之后添加约束，所以在创建它的时候就把约束加好。

看一下9.4版本的语法。

接下来刷新，创建唯一索引，create unique index idx_mv_tbl_id on mv_tbl <id>;

再查询物化视图定义时就会出现索引。PostgreSQL9.4支持增量刷新，数据查询时跟刷新无冲突,只要加上CONCURRENTLY 这个选项就可以了，这样就是做了一次增量刷新；把时间打开，就能够看到增量刷新和全量刷新的区别。

如图重新建一个物化视图，把10万条记录全部加载过来，然后把时间打开刷新物化视图。先把索引建起来，一会增量刷新要用到。

现在是全量刷新物化视图，刷新两次，记录时间。如果是加上并行，主表 tbl 没有任何变化，所以全量刷新时会做一个一对一的比较，就是 tbl 和 mv_tbl1。

如图是增量刷新物化视图，刷新两次，记录时间。

一对一进行比较，相对将10万条记录直接拷贝过来增量刷新是慢的，因为它相当于去做了一个查询。所以说，数据量大的情况下，增量刷新没有占到任何时间优势。但是如果 mv_tbl1表有高并发的查询，使用并行查询的结果是不会阻断你的查询，而全量刷新查询物化视图是会被阻断的。

异地物化视图，刚刚建的物化视图是在本地表建的，但实际上很多跨库、甚至是跨机房去建物化视图。

对于数据库是两个数据库。首先创建一个 Foreign rap,此图显示的是支持外部表的一些 rap。PostgreSQL 自从9.1版本开始引入 SQL 技术以来，越来越多的贡献者分享出来外部表插件。创建 Oracle 的外部表，在 postgreSQL 里能够直接访问到其数据，然后通过这个外部表再去创建一个物化视图，可以简化跨库操作，而直接把 Oracle 里面的数据当成物化视图使用。

接下来是 PostgreSQL 外部表和外部表的创建，首先要在创建物化视图的主机上创建一个 database link 模块。

这里要用到如图 fdw 插件去创建 Postgre 和 Postgre 的外部。

创建完成之后，会出现 handler 函数。

接下来先创建 extension 然后再创 server。

如图是创建 server 的语法。

Sever 要指定数据库名、端口名等，那么就命名为 pg39，然后指定 FOREIGN DATA WRAPPER,它就叫 postgres_fdw,再指定 OPTIONS <hostaddr ‘172.16.3.39’,dbname ‘digoal’.port’5432’>

server 创建完成。

接下来创建 user mapping，现在这个节点只有 postgres 用户，那就给它映射一个直接连接到 sever 的用户，CREAT USER MAPPING postgres server pg39 options <user’postgres>;当然也可以不用它，比如创建一个用户加digoal，密码也是 digoal，<user’digoal’,password’digoal’>;

user mapping 创建完毕。

然后创建 foreign table,首先在这里要有个基表，访问 userinfo 表，往里面插入测试记录，假设这里插入10万条记录，再创建一个外部表，表名叫 ft_userinfo,列是<id int primary key,info text,crt_time timestamp>,再指定一个sever pg39，就是上面创建好的server；列名和顺序要匹配，options 只需指定 schema_name 和 table_name,<scheme_name’digoal’,table_name’userinfo’>,不允许创建约束。

foreign table 创建完毕。

查询一下数据可以查询到，如果要创建约束，就要给 FOREIGN TABLE 创建一个索引，物化视图的增量刷新要建唯一组件，不能在 foreign table 上创建唯一索引，所以只能在外部表上面做全量刷新。

现在基于ft_userinfo去创建一个外部表，然后创建物化视图 creat materializeed view mv_userinfo as select ’’ from ft_userinfo with no data;然后 refresh materialized view CONCURRENTLY mv_userinfo with data;这时候会告诉你不支持用它，因为它不支持用 CONCURRENTLY ，接下来先全量刷新，再增量刷新。

增量刷新时会提示去创建唯一索引，不能并行刷物化视图，因为在物化视图上面没有唯一索引，所以在它上面创建唯一索引 creat unique index idx_mv_userinfo_id on mv_userinfo with data;增量刷新只是判断物化视图上有没有唯一索引，基表上有个主键，把主键去掉看一下 drop constraint tbl_pkey;创建完成之后，再利用增量进行刷新 mv_tbl1。此时可以进行增量刷新,postgre 增量刷新是判断物化视图上面有没有唯一索引，而基表上是否存在唯一索引它并不关注。

现在已经实现了跨库的物化视图增量或全量刷新。

现在来看 Oracle 下面增量的物化视图，这里是一台需要测试的 Oracle 节点，先要能连到这个数据库上来。

安装 Oracle_fdw，首先将它下载，可以去 pgxn.org 里面下载，直接下载到 contrib，然后解压。Oracle_fdw 的安装依赖了 Oracle 的外部库，所以需要再安装一个 Oracle（老师从另一台机器上直接拷贝过来）。

修改 Oracle 的环境 home，把环境变量拷贝过来，只要两个目录结构一样就可以，然后把 Oracle 的 lib 库加进去，

查看 Oracle 的用户权限，多个用户去共用，至少改成777的权限，修改成755。然后可以安装应用它，看是否已经生效，目前一切正常。

现在已经把 oracle_fdw.so 拷贝到 pgsql9.4devel 里面去了，到9.4里面重启数据库，在重启数据库时一定要确保Oracle_home 和 LD_LIBRARY 已经生效，否则后续不能使用。

创建 extension，创建之后就多了 oracle_fdw_validator,

接下来创建 server，将其连接到 Oracle 的一些配置，CREAT SERVER ora67 FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver ’//172.16.3.67:1621/ORADB’>;sever 的权限不用赋给普通用户，一会用超级用户去连接

接下来创建 user mapping，也是用 postgre 用户去连接 postgre SVRVER ora67 OPTIONS <user’orauser’,password’orapwd’>;这里创建一个用户，老师的密码叫 test，用户名也叫 test。

接下来创建外部表，userinfo <id int primary key,info varchar2<64>,写入一些测试记录 userinfo select row_number， 'hello skynobi', sysdate fron dual connect level <=100000;

100000 rows created.插入十万条记录。

创建 Oracle 的外部表，命名为 ft_ora_userinfo,内容<id int, info text.,crt_time tinestamp<0>> SERUER ora67 OPTIONS <schema 'TEST', table 'USERINFO'>;在 Oracle 里面都是大写，所以这里要注意一下。

oracle 外部表创建完成，现在去查询能查到十万条数据，

然后创建 Oracle 下的物化视图，如果要并行刷新，在 mv_ora_userinfo 上面创建一个唯一索引就可以 create unique index uk_mv_ora_userinfo_id on mv_ora_userinfo<id>;但是要对整个的物化视图是一个完整唯一索引才可以。当前没有任何技术的情况不能做并刷新，第一次刷新必须是全量刷新，然后就可以做并行刷新。