1003
MySQL,Oracle,SQL Server等準實時同步到PostgreSQL的方案之一 - FDW外部訪問接口
標簽
PostgreSQL , MySQL , Oracle , SQL Server , DRDS , 數據同步 , FDW , 調度 , 邏輯刪除 , 空洞 , 事務時間
背景
PostgreSQL曆經幾十年的發展，厚積薄發，每年一個大版本，每年都有驚喜，2017年10月推出的10版本，包含重大重磅特性。PostgreSQL在逐漸成為企業的核心庫。
企業數據源可能眾多，為了實現數據的同步，方法有很多，較為常見的是這樣的方法，將數據變更匯總到消息隊列，通過消息訂閱的方式，傳播數據。消息隊列可以承擔持久化存儲、冪等消費、數據清洗、流式計算等工作。當然，為了提高消息隊列的吞吐率，越簡單越好。就像路由器和交換機一樣，各司其職。
在阿裏雲的PostgreSQL生態中，還可以有這樣的方法(數據實時寫入PG，經過PG的清洗，通過OSS流入HDB PG。實現OLTP和OLAP的數據流動。)：
除了以上方法，實際上PostgreSQL還有一個殺手級功能，FDW，通過FDW，可以連接各種數據源，目前已有的接口如下（看樣子包含了地球上所有的數據源）：
https://wiki.postgresql.org/wiki/Fdw
下麵我們來看一下，如何基於FDW構建一個簡單的準實時同步方案。
FDW是什麼
FDW是PostgreSQL的一個外部數據訪問接口規範，用戶可以自定義任何數據源的FDW handler，這樣就能訪問你要訪問的數據接口了。
https://www.postgresql.org/docs/10/static/fdwhandler.html
上遊數據源設計規範
為了適應使用FDW來實現準實時同步的需求，上遊數據源必須要符合一定的規約。
1、被同步的表必須有主鍵。
2、被同步的表必須有修改時間（非空），修改時間字段的初始值為記錄創建時間，每次更新記錄，更新為當前時間。
3、被同步的表必須有邏輯刪除標記（不要使用delete直接刪除數據）
或者DELETE的操作與DDL操作一樣，采用調度（業務係統與PG係統同時操作的模式）。
4、被同步的表，修改時間字段，必須有索引。
邏輯刪除
簡單介紹一下邏輯刪除。
create table tbl(
id int primary key, -- 主鍵
info text,
state char(1), -- 邏輯刪除標記
crt_time timestamp not null, -- 寫入時間
mod_time timestamp not null -- 記錄被修改的時間，記錄創建時，設置mod_time為創建時間。
);
邏輯刪除時，並非調用delete，而是更新state這個標記。
update tbl set state='d' , mod_time=now() where id=?;
PostgreSQL支持使用RULE，將DELETE操作轉換為UPDATE操作，例子
postgres=# create table log(id serial8 primary key, info text, state char(1), crt_time timestamp, mod_time timestamp);
CREATE TABLE
postgres=# create rule r1 as on delete to log do instead update log set state='d',mod_time=now() where id=OLD.id;
CREATE RULE
postgres=# insert into log (info,crt_time,mod_time) values ('abc',now(),now());
INSERT 0 1
postgres=# select * from log;
id | info | state | crt_time | mod_time
----+------+-------+----------------------------+----------------------------
1 | abc | | 2017-10-27 15:15:44.577036 | 2017-10-27 15:15:44.577036
(1 row)
postgres=# delete from log where id=1;
DELETE 0
postgres=# select * from log;
id | info | state | crt_time | mod_time
----+------+-------+----------------------------+----------------------------
1 | abc | d | 2017-10-27 15:15:44.577036 | 2017-10-27 15:15:54.367215
(1 row)
postgres=# update log set info='test';
UPDATE 1
postgres=# select * from log;
id | info | state | crt_time | mod_time
----+------+-------+----------------------------+----------------------------
1 | test | d | 2017-10-27 15:15:44.577036 | 2017-10-27 15:15:54.367215
(1 row)
下遊同步時，就可以同步到這樣的操作。
如果業務上一定要DELETE，那麼不適合本方案。請使用binlog的同步方式。例如rds_dbsync這個工具。
https://github.com/aliyun/rds_dbsync/
《MySQL準實時同步到PostgreSQL, Greenplum的方案之一 - rds_dbsync》
調度設計與例子
設計
1、創建外部表
2、創建本地表，結構與外部表一致
3、創建同步函數
4、同步函數邏輯
設置柵欄，對於一張外部表，同一時刻隻允許開啟一個任務，防止重複拖數據(雖然insert on conflict會冪等處理，但是也浪費資源)。
設置上一次同步的截止時間戳
從上一次截止的時間戳開始同步，直到N分鍾前的數據（可調），目的是防止空洞（比如一些老事務未提交，時間戳還是老的，會成為空洞）。
5、使用linux crontab，創建定時任務
例子
環境部署，MySQL fdw的使用，詳見：
《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(適合新用戶)》
假設mysql_fdw foreign data wrapper, foreign server, user mapping, 權限 都已經配置好了。
1、外部表
CREATE FOREIGN TABLE remote_table1(
id int,
info text,
state char(1),
crt_time timestamp,
mod_time timestamp
)
SERVER mysql_server1
OPTIONS (dbname 'db1', table_name 'remote_table1');
2、本地表(與遠程表定義一致)
create table local_table1(
id int primary key, -- 主鍵
info text,
state char(1), -- 邏輯刪除標記
crt_time timestamp not null, -- 寫入時間
mod_time timestamp not null -- 記錄被修改的時間，記錄創建時，設置mod_time為創建時間。
);
3、同步函數1
create or replace function sync_tbl1(rmt_table name) returns void as $$ -- 每個表配置一個獨立的advisory ID，防止單個表有多個任務同時跑。
declare
v_mod_time timestamp; -- 起始時間
v_upper_mod_time timestamp; -- 截止時間
begin
if not pg_try_advisory_lock(hashtext(rmt_table)) then return; end if; -- 保護，確保同一個表，隻有單個任務在同步。不同的表不會幹擾。
select max(mod_time) into v_mod_time from local_table1 ;
if v_mod_time is null then
v_mod_time='0001-01-01'::timestamp;
end if;
v_upper_mod_time := now()-interval '5 min'; -- 最多同步到5分鍾前，防止空洞（比如一些老事務未提交，時間戳還是老的，會成為空洞）。
insert into local_table1
(
select * from remote_table1 where mod_time > v_mod_time and mod_time <= v_upper_mod_time;
)
on conflict (id) do update set info=excluded.info,state=excluded.state,crt_time=excluded.crt_time,mod_time=excluded.mod_time ; -- 每個表的主鍵，以及字段可能不一樣，需要修改一下。
return;
end;
$$ language plpgsql strict;
4、調度
假設每分鍾調度一次。
crontab -e
* * * * * psql -c "select sync_tbl1('remote_table1');"
對於很大數據量的數據，PostgreSQL可以使用分區表。
《PostgreSQL 10.0 preview 功能增強 - 內置分區表》
注意
空洞問題。
最多同步到5分鍾前，防止空洞（比如一些老事務未提交，時間戳還是老的，會成為空洞）。
參考
《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(適合新用戶)》
《PostgreSQL 10.0 preview 功能增強 - 內置分區表》
最後更新：2017-10-29 00:04:38