PostgreSQL 物化视图(Oracle同步到PG,PG同步到PG) - by pgsnapshot (plperlu trigger) (支持类似Oracle的mvlog fast complete force刷新)

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: 标签PostgreSQL , 物化视图 , 增量刷新 , mvlog , Oracle 同步到 PG , PG 同步到 PG背景PostgreSQL自身的物化视图没有MVLOG,也就是说,刷新的时候是VIEW定义产生的记录与MV已刷新的记录进行比对,进行增量更新的过程。

标签

PostgreSQL , 物化视图 , 增量刷新 , mvlog , Oracle 同步到 PG , PG 同步到 PG


背景

PostgreSQL自身的物化视图没有MVLOG,也就是说,刷新的时候是VIEW定义产生的记录与MV已刷新的记录进行比对,进行增量更新的过程。

PG的物化视图增量刷新,在MV记录数较少,并且更新频繁时,刷新效率更高。

而Oracle的mv,创建后,支持增量刷新是通过上游的变更量(mvlog),进行回放,所以在MV记录数非常多,并且更新(写入)频率较低时,效率更高。

PG通过pgsnapshot插件,在发布端对变更表创建plperlu的触发器,记录变更量到MVLOG中,可以实现与ORACLE类似的增量刷新机制。

实际上pg 的 londiste3插件也是类似的功效(基于触发器的mq增量)

pgsnapshot的使用

1、下载

https://www.postgresql.org/ftp/projects/pgFoundry/snapshot/snapshot/

wget https://ftp.postgresql.org/pub/projects/pgFoundry/snapshot/snapshot/Pgsnapshot-0.4.x/pgsnapshot-0.4.0.tgz  
  
tar -zxvf pgsnapshot-0.4.0.tgz  

2、依赖什么

1、PostgreSQL 8.0以上版本,

2、plperlu 数据库函数语言,

3、Perl 5.8.5或更高版本,

4、Make sure that DBI is installed on your Perl system and that the DBD of
the database you choose is also installed;

3、配置pgsnapshot

配置Makefile.sh,生成SQL文件。

1、Edit the Makefile.sh file and change the KEY variable to a better "secret"
value and the BASE_SCHEMA variable to where the base(internal) Pg::Snapshot tables should be placed.
Also remember to setup the remaining variables like SUPERUSER.

#!/bin/sh  
  
# You may change the following variables to best suit your needs  
  
##  
# KEY is the communication key between any two servers   
# or between a DBA workstation and a server  
##  
## 配置密钥(用于通讯)  
KEY=Digoalpwd123321PGDB_2018  
  
##  
# BASE_SCHEMA is the schema where all base(internal) tables of Pg::Snapshot will be placed  
##  
## 配置pgsnapshot的元数据、函数的SCHEMA  
BASE_SCHEMA=_pgmvlog  
  
##  
# LOCAL postgresql server superuser  
##  
## 配置订阅端的数据库超级用户名  
SUPERUSER=postgres  
  
# You may not need to change anything beyond this line  
if [ "$1" == "clean" ]; then  
        echo "Removing pgsnapshots.sql..."  
        rm -f pgsnapshots.sql  
        echo "Removing previously generated SQL drivers..."  
        find drivers -name snapshot.sql | xargs rm -f  
        echo "Cleaned."  
        exit 0  
fi  
  
function apply {  
        cat $1 | awk '/^INCLUDE .*$/ { system("cat src/pl/"$2"")} !/^INCLUDE .*$/ {print}' | sed "s/%BASE_SCHEMA%/$BASE_SCHEMA/g" | sed "s/%COMMUNICATION_KEY%/$KEY/g" | sed "s/%SUPERUSER%/$SUPERUSER/g"  
}  
  
IFS=' '  
SQLS='pgsnapshots_tables.sql pgsnapshots_dblink.sql pgsnapshots_create_snapshot.sql pgsnapshots_drop_snapshot.sql pgsnapshots_refresh_snapshot.sql pgsnapshots_snapshotlog.sql'  
  
rm -f pgsnapshots.sql  
for F in $SQLS; do  
        #echo $F  
        apply src/sql/$F >> pgsnapshots.sql  
done  
IFS=$'\n\t '  
for F in `find drivers -name snapshot.template.sql`; do  
        OUTFILE=`echo "$F" | sed "s/\.template\.sql/.sql/"`  
        cat $F | sed "s/%BASE_SCHEMA%/$BASE_SCHEMA/g" | sed "s/%COMMUNICATION_KEY%/$KEY/g" > $OUTFILE  
done  
echo "Done."  

2、执行Makefile.sh ,生成SQL文件。

4、发布端

1、需要在发布端(例如你在源库的postgres库,有一些表要发布)创建plperlu函数语言,

postgres@pg11-test-> psql  
psql (11.1)  
Type "help" for help.  
  
db1=# create language plperlu;  
CREATE LANGUAGE  

2、执行Makefile.sh生成的两个SQL文件。(在发布端(例如你在源库的postgres库,有一些表要发布))

psql -d <database> -h <host> -U <user> -f ./drivers/pg/snapshot.sql  
psql -d <database> -h <host> -U <user> -f pgsnapshots.sql  

对于ORACLE的发布端来说,需要执行如下

SQL> @./drivers/oracle/snapshot.sql  

3、配置pg_hba.conf,允许订阅端访问发布端数据库。

vi $PGDATA/pg_hba.conf  
  
host all all 0.0.0.0/0 md5  
  
pg_ctl reload -D $PGDATA  

4、发布要被订阅的表(注意表需要主键)。

在发布端的目标库执行((在发布端(例如你在源库的postgres库,有一些表要发布))),允许订阅端连接到发布端

select _pgmvlog.snapshot_do('<key>', 'ALLOW', '<masterschema>', '<mastername>', '<ip>');  

对于ORACLE的发布端来说,需要执行如下

SQL> begin  
         snapshot_do('<key>', 'ALLOW', '<masterschema>', '<mastername>', '<ip>');  
         end;  
         /  
    Where:  
        <key> is the "secret" value placed on the KEY variable inside the Makefile.sh file.  Makefile.sh中配置的密码,用于物化视图通讯   
        <masterschema> is the schema name of the master table you wish to allow access to   
        <mastername> is the name of the master table you wish to allow access to   
        <ip> is the IP address of your workstation/server to whom you wish to give access  订阅端的出口IP地址   

例子(发布src表,允许订阅端访问,假设订阅端的IP为192.168.3.198

db1=# create table src (id int primary key, info text, crt_time timestamp);  
CREATE TABLE  
  
  
db1=# select _pgmvlog.snapshot_do('Digoalpwd123321PGDB_2018', 'ALLOW', 'public', 'src', '192.168.3.198');  
 snapshot_do   
-------------  
 t  
(1 row)  

5、创建一个角色,赋予发布表的读权限给这个用户,这个用户需要在订阅端使用,用来连接发布端,复制发布表的数据。

create role mv_ro login encrypted password 'abcdefg';  
  
grant select on public.src to mv_ro;  

5、订阅端

1、创建plperlu 函数语言

postgres@pg11-test-> psql  
psql (11.1)  
Type "help" for help.  
  
db2=# create language plperlu;  
CREATE LANGUAGE  

2、创建SERVER,取个名字,比如server1(代表发布端连接方式,(假设发布端的IP为192.168.3.199,数据库监听端口为1921))。

db2=# SELECT _pgmvlog.create_dblink('server2', 'dbi:Pg:dbname=db1;host=192.168.3.199;port=1921', 'mv_ro', 'abcdefg', '{AutoCommit => 0}');  
 create_dblink   
---------------  
 t  
(1 row)  
  
db2=# select * from _pgmvlog.pg_dblinks;  
 dblinkid | dblinkname |                 datasource                 |   username   | password |    attributes     |           ctime              
----------+------------+--------------------------------------------+--------------+----------+-------------------+----------------------------  
        1 | server2    | dbi:Pg:dbname=db1;host=192.168.3.199;port=1921 | mv_ro        | abcdefg  | {AutoCommit => 0} | 2018-12-17 21:54:29.55201  
(1 rows)  

3、创建物化视图。全量刷新。(有三种刷新方法COMPLETE , FORCE , FAST)

-- 创建物化视图  
SELECT _pgmvlog.create_snapshot('public', 'mv_tbl_src', 'select * from public.src', 'server2', 'COMPLETE', null);  
  
-- 刷新物化视图  
SELECT _pgmvlog.refresh_snapshot('public', 'mv_tbl_src');  

4、创建物化视图。增量刷新。

select _pgmvlog.drop_snapshot('public','mv_tbl_src'); -- 删除物化视图  

发布端(创建MVLOG)

select _pgmvlog.create_snapshot_log('public','src', 'PRIMARY KEY');   
  
db1=# \dp+  
                                    Access privileges  
 Schema |     Name     | Type  |     Access privileges     | Column privileges | Policies   
--------+--------------+-------+---------------------------+-------------------+----------  
 public | mlog$_src    | table | postgres=arwdDxt/postgres+|                   |   
        |              |       | mv_ro=r/postgres          |                   |   
 public | src          | table | postgres=arwdDxt/postgres+|                   |   
        |              |       | mv_ro=r/postgres          |                   |   
(4 rows)  

订阅端(创建增量刷新物化视图)

-- 创建物化视图  
SELECT _pgmvlog.create_snapshot('public', 'mv_tbl_src', 'select * from public.src', 'server2', 'FAST', null);  
  
-- 或  
  
SELECT _pgmvlog.create_snapshot('public', 'mv_tbl_src', 'select * from public.src', 'server2', 'FORCE', null);  -- 全清MVLOG  
  
-- 创建物化视图索引  
db2=# create unique index idx_mv_tbl_src_1 on mv_tbl_src (id);  
CREATE INDEX  
  
-- 增量刷新物化视图  
SELECT _pgmvlog.refresh_snapshot('public', 'mv_tbl_src');  

刷新速度

db1=# insert into src select generate_series(40002,400000), md5(random()::text), now();  
INSERT 0 359999  
  
db1=# \c db2  
You are now connected to database "db2" as user "postgres".  
db2=# SELECT _pgmvlog.refresh_snapshot('public', 'mv_tbl_src');  
NOTICE:  Refreshed 359999 records in 16 seconds.  
 refresh_snapshot   
------------------  
 t  
(1 row)  

Oracle 增量同步到PG (在PG中创建ORACLE的物化视图)

类似。

pgsnapshot手册

SAMPLES

USAGE.txt

USAGE.html

注意

版本比较老,生产就不要用了,不过有兴趣的同学可以借鉴这个思路把这个插件再维护一下。

可从mysql, oracle, pg等增量同步到PG。

参考

《使用Londiste3 增量同步 线下PostgreSQL 到 阿里云RDS PG》

《londiste3 copy table' snapshot & PostgreSQL logical replication's snapshot 不同之处》

《Londiste 3 replicate case - 1 下节》

《Londiste 3 replicate case - 1 上节》

《Londiste3 Install》

https://www.postgresql.org/ftp/projects/pgFoundry/snapshot/snapshot/

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
2月前
|
关系型数据库 分布式数据库 数据库
PolarDB PostgreSQL版:Oracle兼容的高性能数据库
PolarDB PostgreSQL版是一款高性能的数据库,具有与Oracle兼容的特性。它采用了分布式架构,可以轻松处理大量的数据,同时还支持多种数据类型和函数,具有高可用性和可扩展性。它还提供了丰富的管理工具和性能优化功能,为企业提供了可靠的数据存储和处理解决方案。PolarDB PostgreSQL版在数据库领域具有很高的竞争力,可以满足各种企业的需求。
|
2月前
|
人工智能 Oracle 关系型数据库
一篇文章弄懂Oracle和PostgreSQL的Database Link
一篇文章弄懂Oracle和PostgreSQL的Database Link
|
2月前
|
SQL Oracle 关系型数据库
常用数据库的分页语句(mySQL、oracle、PostgreSQL、SQL Server)
常用数据库的分页语句(mySQL、oracle、PostgreSQL、SQL Server)
|
7月前
|
SQL Oracle 关系型数据库
Oracle,Postgresql等数据库使用
Oracle,Postgresql等数据库简单使用
145 0
Oracle,Postgresql等数据库使用
|
10月前
|
SQL Oracle 关系型数据库
Polar DB-O (兼容 Oracle 语法版本)和Polar DB PostgreSQL 版本概述(二)
Polar DB-O (兼容 Oracle 语法版本)和Polar DB PostgreSQL 版本概述(二)
1174 0
|
12月前
|
SQL Cloud Native 关系型数据库
ADBPG(AnalyticDB for PostgreSQL)是阿里云提供的一种云原生的大数据分析型数据库
ADBPG(AnalyticDB for PostgreSQL)是阿里云提供的一种云原生的大数据分析型数据库
990 1
|
12月前
|
数据可视化 关系型数据库 MySQL
将 PostgreSQL 迁移到 MySQL 数据库
将 PostgreSQL 迁移到 MySQL 数据库
1341 2
|
SQL 关系型数据库 Linux
【PostgreSQL】基于CentOS系统安装PostgreSQL数据库
【PostgreSQL】基于CentOS系统安装PostgreSQL数据库
677 0
|
11月前
|
SQL 存储 自然语言处理
玩转阿里云RDS PostgreSQL数据库通过pg_jieba插件进行分词
在当今社交媒体的时代,人们通过各种平台分享自己的生活、观点和情感。然而,对于平台管理员和品牌经营者来说,了解用户的情感和意见变得至关重要。为了帮助他们更好地了解用户的情感倾向,我们可以使用PostgreSQL中的pg_jieba插件对这些发帖进行分词和情感分析,来构建一个社交媒体情感分析系统,系统将根据用户的发帖内容,自动判断其情感倾向是积极、消极还是中性,并将结果存储在数据库中。
玩转阿里云RDS PostgreSQL数据库通过pg_jieba插件进行分词
|
11月前
|
关系型数据库 测试技术 分布式数据库
PolarDB | PostgreSQL 高并发队列处理业务的数据库性能优化实践
在电商业务中可能涉及这样的场景, 由于有上下游关系的存在, 1、用户下单后, 上下游厂商会在自己系统中生成一笔订单记录并反馈给对方, 2、在收到反馈订单后, 本地会先缓存反馈的订单记录队列, 3、然后后台再从缓存取出订单并进行处理. 如果是高并发的处理, 因为大家都按一个顺序获取, 容易产生热点, 可能遇到取出队列遇到锁冲突瓶颈、IO扫描浪费、CPU计算浪费的瓶颈. 以及在清除已处理订单后, 索引版本未及时清理导致的回表版本判断带来的IO浪费和CPU运算浪费瓶颈等. 本文将给出“队列处理业务的数据库性能优化”优化方法和demo演示. 性能提升10到20倍.
688 4

相关产品

  • 云原生数据库 PolarDB
  • 推荐镜像

    更多