PostgreSQL跨数据库表更新日志写入的替代优化方案咨询

当前你用硬编码连接串的dblink存储过程实现主表更新时同步日志到异库，存在连接串硬编码、维护麻烦的问题，下面给你几个更优的替代方案：

方案1：用数据库服务名替代硬编码连接串（dblink + pg_service.conf） #

把连接信息配置到pg_service.conf里，用服务名代替明文连接串，既安全又方便维护。

操作步骤： #

1. 编辑pg_service.conf（默认路径是~/.pg_service.conf或$PGDATA/pg_service.conf），添加主库和日志库的服务配置：

# 主库服务配置
[main_db]
host=192.168.1.3
port=5432
user=postgres
password=admin
dbname=main_db

# 日志库服务配置
[log_db]
host=192.168.1.3
port=5432
user=postgres
password=admin
dbname=db_log

注意：把这个文件权限设为600，避免敏感信息泄露。

2. 修改存储过程，用服务名替换硬编码连接串，同时支持传参数指定要同步的行：

CREATE OR REPLACE PROCEDURE sp_insert_or_update_to_test_log(p_test_id BIGINT) AS $$
DECLARE
    insert_statement TEXT;
BEGIN
    insert_statement = $Q1$ INSERT INTO tbl_test_log (fk_test_id, vhr_test_value)
                            SELECT pk_test_id, vhr_test_value
                            FROM dblink('main_db',
                                        'SELECT pk_test_id, vhr_test_value FROM tbl_test WHERE pk_test_id=' || p_test_id)
                                 AS ct(pk_test_id BIGINT, vhr_test_value VARCHAR) $Q1$;

    dblink_exec('log_db', insert_statement, true);
    RETURN;
END;
$$ LANGUAGE plpgsql;

调用时直接传ID：

CALL sp_insert_or_update_to_test_log(1);

方案2：触发器自动触发同步（结合服务名） #

不用手动调用存储过程，主表发生INSERT/UPDATE时自动同步日志，更省心。

操作步骤： #

1. 先按方案1配置好pg_service.conf。

2. 创建触发器函数，直接用变更后的行数据同步：

CREATE OR REPLACE FUNCTION fn_sync_test_to_log()
RETURNS TRIGGER AS $$
BEGIN
    -- 用NEW对象直接获取刚插入/更新的行数据，不用再查主库
    PERFORM dblink_exec('log_db',
        format('INSERT INTO tbl_test_log (fk_test_id, vhr_test_value) VALUES (%L, %L)',
               NEW.pk_test_id, NEW.vhr_test_value),
        true);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 给主表绑定触发器：

CREATE TRIGGER trg_sync_test_log
AFTER INSERT OR UPDATE ON tbl_test
FOR EACH ROW
EXECUTE FUNCTION fn_sync_test_to_log();

之后主表每插入或更新一行，都会自动把数据同步到日志库的tbl_test_log里，完全不用手动干预。

方案3：逻辑复制（PostgreSQL原生功能） #

如果日志表只需要同步主表的INSERT/UPDATE变更，用逻辑复制是最稳定高效的方案，不用写任何存储过程。

操作步骤： #

1. 主库配置：修改postgresql.conf，设置wal_level = logical，然后重启数据库。接着创建发布：

-- 创建发布，指定要同步的表和操作类型
CREATE PUBLICATION pub_test_log FOR TABLE tbl_test
WITH (publish = 'insert, update');

2. 日志库配置：创建订阅，连接到主库的发布（用服务名简化连接）：

-- 创建订阅，同步主库的发布内容
CREATE SUBSCRIPTION sub_test_log
CONNECTION 'service=main_db'
PUBLICATION pub_test_log;

3. 适配日志表结构：
   如果日志表和主表结构不一致，在日志库创建一个和主表结构一致的中间表，再用触发器把数据转发到tbl_test_log：

-- 日志库创建和主表结构一致的表
CREATE TABLE tbl_test (
        pk_test_id BIGSERIAL PRIMARY KEY,
        vhr_test_value VARCHAR(200) DEFAULT ''
);

-- 创建转发触发器函数
CREATE OR REPLACE FUNCTION fn_forward_to_log()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO tbl_test_log (fk_test_id, vhr_test_value)
    VALUES (NEW.pk_test_id, NEW.vhr_test_value);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器
CREATE TRIGGER trg_forward_test_log
AFTER INSERT OR UPDATE ON tbl_test
FOR EACH ROW
EXECUTE FUNCTION fn_forward_to_log();

如果不需要历史数据，创建订阅时可以加WITH (copy_data = false)，只同步后续的变更。

方案对比 #

• dblink+服务名：代码改动小，适合小批量手动触发的场景。
• 触发器+服务名：自动触发，实时同步单条变更，逻辑简单。
• 逻辑复制：原生功能，性能好、稳定性高，适合大规模高并发的同步场景。

内容的提问来源于stack exchange，提问作者JUNAID M