要實(shí)現(xiàn)一個 PG 庫的表數(shù)據(jù)變化（增刪改），實(shí)時 / 定時同步到另一個獨(dú)立 PG 庫，豆老師給整理了生產(chǎn)環(huán)境最常用、最穩(wěn)定的 4 種方案。

一、最簡單：觸發(fā)器 + 外部表（dblink/foreign table）

適合小數(shù)據(jù)量、單表同步、實(shí)時性要求高的場景，無需額外組件，純 PG 原生實(shí)現(xiàn)。

實(shí)現(xiàn)原理

1、源庫表創(chuàng)建觸發(fā)器，數(shù)據(jù)增刪改時觸發(fā)

2、觸發(fā)器調(diào)用函數(shù)，通過 dblink 連接目標(biāo)庫

3、自動執(zhí)行同步 SQL，實(shí)時寫入目標(biāo)庫

核心步驟

1、源庫安裝擴(kuò)展

-- 源庫執(zhí)行

CREATE EXTENSION IF NOT EXISTS dblink;

2、創(chuàng)建同步函數(shù)（源庫）

CREATE OR REPLACE FUNCTION sync_table_func()

RETURNS TRIGGER AS $$

BEGIN

? -- 連接目標(biāo)庫：替換為你的目標(biāo)庫信息

? PERFORM dblink_connect(

? ? 'dbname=目標(biāo)庫名 host=目標(biāo)IP port=5432 user=賬號 password=密碼'

? );

-- INSERT 同步

? IF (TG_OP = 'INSERT') THEN

? ? PERFORM dblink_exec(

? ? ? 'INSERT INTO 目標(biāo)表名 VALUES ($1.*)', NEW

? ? );

? -- UPDATE 同步

? ELSIF (TG_OP = 'UPDATE') THEN

? ? PERFORM dblink_exec(

? ? ? 'UPDATE 目標(biāo)表名 SET 字段1=$1,字段2=$2 WHERE id=$3',

? ? ? NEW.字段1, NEW.字段2, OLD.id

? ? );

? -- DELETE 同步

? ELSIF (TG_OP = 'DELETE') THEN

? ? PERFORM dblink_exec(

? ? ? 'DELETE FROM 目標(biāo)表名 WHERE id=$1', OLD.id

? ? );

? END IF;

PERFORM dblink_disconnect();

? RETURN COALESCE(NEW, OLD);

END;

$$ LANGUAGE plpgsql;

3、綁定觸發(fā)器到源表

CREATE TRIGGER trigger_sync_table

AFTER INSERT OR UPDATE OR DELETE ON 源表名

FOR EACH ROW EXECUTE FUNCTION sync_table_func();

優(yōu)點(diǎn)

①純 PG 原生，零部署、零學(xué)習(xí)成本

②實(shí)時同步，延遲極低

③單表配置快速

缺點(diǎn)

①大并發(fā)、大數(shù)據(jù)量會影響源庫性能

②目標(biāo)庫不可用時，源庫會報錯

③不適合批量操作、分表同步

二、最常用：邏輯復(fù)制（Logical Replication）

PG 10+ 原生自帶，生產(chǎn)標(biāo)準(zhǔn)方案，適合實(shí)時同步、多表、大數(shù)據(jù)量。

核心優(yōu)勢

①不影響源庫性能

②目標(biāo)庫斷開重連后自動續(xù)傳，不丟數(shù)據(jù)

③支持整庫 / 多表 / 指定表同步

④官方支持，穩(wěn)定可靠

最簡配置（3 步）

1、源庫配置（postgresql.conf）

wal_level = logical? # 必須修改
max_replication_slots = 10
max_wal_senders = 10

重啟 PG 生效。

2、源庫創(chuàng)建發(fā)布端

-- 創(chuàng)建發(fā)布（同步指定表）

CREATE PUBLICATION pub_target FOR TABLE 表1, 表2;

-- 授權(quán)復(fù)制權(quán)限

ALTER ROLE 賬號 REPLICATION;

3、目標(biāo)庫創(chuàng)建訂閱端

sql

-- 創(chuàng)建訂閱，自動同步數(shù)據(jù)

CREATE SUBSCRIPTION sub_source

CONNECTION 'host=源IP port=5432 dbname=源庫 user=賬號 password=密碼'

PUBLICATION pub_target;

? 完成：源表任何變化都會實(shí)時同步到目標(biāo)庫。

三、企業(yè)級：Debezium + Kafka（CDC 方案）

適合高并發(fā)、分布式、多系統(tǒng)訂閱同一份數(shù)據(jù)的場景（微服務(wù)架構(gòu)）。

原理

①Debezium 監(jiān)聽 PG WAL 日志

②把數(shù)據(jù)變化發(fā)送到 Kafka

③目標(biāo)庫 / 其他系統(tǒng)消費(fèi)消息同步

優(yōu)點(diǎn)

①完全解耦，不影響業(yè)務(wù)庫

②支持多目標(biāo)同步

③可回溯數(shù)據(jù)變更歷史

④大數(shù)據(jù)架構(gòu)標(biāo)配

缺點(diǎn)

①部署復(fù)雜（需要 Kafka、Debezium）

②小項(xiàng)目沒必要

四、定時同步：pg_dump + cron /pg_clogSync

適合非實(shí)時、每天 / 每小時同步、對延遲不敏感的場景。

方案 1：定時全量同步（簡單）

源庫導(dǎo)出

pg_dump -h 源IP -U 用戶 -d 庫 -t 表 -F c -f dumpfile

目標(biāo)庫恢復(fù)

pg_restore -h 目標(biāo)IP -U 用戶 -d 庫 -t 表 dumpfile

配合 Linux crontab 定時執(zhí)行。

方案 2：增量同步（pg_clogSync）

只同步變化的數(shù)據(jù)，效率高，適合大表。

方案選擇建議

最推薦：邏輯復(fù)制（生產(chǎn) 90% 場景用它）

優(yōu)點(diǎn)碾壓其他方案：

①官方原生、穩(wěn)定

②實(shí)時、不丟數(shù)據(jù)

③對源庫幾乎無性能損耗

④配置 5 分鐘搞定

四、總結(jié)

1、新手 / 小項(xiàng)目：直接用 觸發(fā)器 + dblink，最快上線

2、生產(chǎn)環(huán)境：優(yōu)先 邏輯復(fù)制，穩(wěn)定、高效、零侵入

3、分布式架構(gòu)：用 Debezium CDC

4、非實(shí)時同步：用 定時 pg_dump

以上就是PostgreSQL實(shí)現(xiàn)數(shù)據(jù)表跨庫同步的四種方案的詳細(xì)內(nèi)容，更多關(guān)于PostgreSQL數(shù)據(jù)表跨庫同步的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論