标簽
PostgreSQL , 物化視圖 , 增量重新整理 , mvlog , Oracle 同步到 PG , PG 同步到 PG
https://github.com/digoal/blog/blob/master/201812/20181217_01.md#%E8%83%8C%E6%99%AF 背景
PostgreSQL自身的物化視圖沒有MVLOG，也就是說，重新整理的時候是VIEW定義産生的記錄與MV已重新整理的記錄進行比對，進行增量更新的過程。
PG的物化視圖增量重新整理，在MV記錄數較少，并且更新頻繁時，重新整理效率更高。
而Oracle的mv，建立後，支援增量重新整理是通過上遊的變更量(mvlog)，進行回放，是以在MV記錄數非常多，并且更新（寫入）頻率較低時，效率更高。
PG通過pgsnapshot插件，在釋出端對變更表建立plperlu的觸發器，記錄變更量到MVLOG中，可以實作與ORACLE類似的增量重新整理機制。
實際上pg 的 londiste3插件也是類似的功效（基于觸發器的mq增量）
https://github.com/digoal/blog/blob/master/201812/20181217_01.md#pgsnapshot%E7%9A%84%E4%BD%BF%E7%94%A8 pgsnapshot的使用
https://github.com/digoal/blog/blob/master/201812/20181217_01.md#1%E4%B8%8B%E8%BD%BD 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
https://github.com/digoal/blog/blob/master/201812/20181217_01.md#2%E4%BE%9D%E8%B5%96%E4%BB%80%E4%B9%88 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;
https://github.com/digoal/blog/blob/master/201812/20181217_01.md#3%E9%85%8D%E7%BD%AEpgsnapshot 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檔案。
https://github.com/digoal/blog/blob/master/201812/20181217_01.md#4%E5%8F%91%E5%B8%83%E7%AB%AF 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>');
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;
https://github.com/digoal/blog/blob/master/201812/20181217_01.md#5%E8%AE%A2%E9%98%85%E7%AB%AF 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)
https://github.com/digoal/blog/blob/master/201812/20181217_01.md#oracle-%E5%A2%9E%E9%87%8F%E5%90%8C%E6%AD%A5%E5%88%B0pg-%E5%9C%A8pg%E4%B8%AD%E5%88%9B%E5%BB%BAoracle%E7%9A%84%E7%89%A9%E5%8C%96%E8%A7%86%E5%9B%BE Oracle 增量同步到PG (在PG中建立ORACLE的物化視圖)
類似。
https://github.com/digoal/blog/blob/master/201812/20181217_01.md#pgsnapshot%E6%89%8B%E5%86%8C pgsnapshot手冊
SAMPLES USAGE.txt USAGE.htmlhttps://github.com/digoal/blog/blob/master/201812/20181217_01.md#%E6%B3%A8%E6%84%8F 注意
版本比較老，生産就不要用了，不過有興趣的同學可以借鑒這個思路把這個插件再維護一下。
可從mysql, oracle, pg等增量同步到PG。
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)