在postgresql 14中,利用sql触发器实现简单读写分离的核心做法是:在主库的表上创建before insert/update触发器,将数据实时同步到只读副本(通常通过逻辑复制槽或流复制);同时,应用层需配置读写分离路由,将写操作指向主库,读操作指向从库。具体而言,需在主库启用逻辑复制,并配置从库作为只读节点,触发器本身不直接处理网络传输,而是确保数据变更能被复制协议捕获。

如何配置PostgreSQL 14的主从流复制环境?
要实现读写分离,首先必须搭建主从架构。在PostgreSQL 14中,推荐使用物理流复制(Streaming Replication)作为基础。主库负责所有写操作,从库通过WAL日志同步保持数据一致,并设置为只读模式以承担读请求。配置步骤包括:在主库的postgresql.conf中设置wal_level=logical(或replica),max_wal_senders和max_replication_slots;在pg_hba.conf中添加replication用户权限;使用pg_basebackup初始化从库,并配置standby.signal和primary_conninfo。

-- 主库 postgresql.conf 关键配置 wal_level = logical max_wal_senders = 3 max_replication_slots = 3 listen_addresses = '*' port = 5432 -- pg_hba.conf 添加复制连接 host replication replicator 0.0.0.0/0 md5 -- 从库 postgresql.conf hot_standby = on port = 5433 -- 从库 standby.signal 文件内容 primary_conninfo = 'host=master_ip port=5432 user=replicator password=secret'
上述配置完成后,重启主从实例,从库将开始接收WAL流。此时,应用层可通过连接串区分主从端口,实现基本的读写分离。注意,物理复制延迟可能导致短暂的数据不一致,适用于对强一致性要求不高的场景。
如何在主库创建触发器以增强数据同步可靠性?
虽然物理流复制已能同步数据,但在某些业务场景下,可能需要更细粒度的控制或审计。可以在主库的关键表上创建BEFORE INSERT/UPDATE触发器,记录变更日志或执行额外校验。例如,创建一个触发器函数来记录每次写入的时间戳和操作类型,并存入审计表。这不会干扰流复制,反而能提供额外的可追溯性。

-- 创建审计表
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
table_name TEXT,
operation TEXT,
changed_at TIMESTAMP DEFAULT NOW()
);
-- 创建触发器函数
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO audit_log (table_name, operation) VALUES (TG_TABLE_NAME, 'INSERT');
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO audit_log (table_name, operation) VALUES (TG_TABLE_NAME, 'UPDATE');
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 绑定触发器到目标表
CREATE TRIGGER trg_audit_after_insert_update
AFTER INSERT OR UPDATE ON your_table
FOR EACH ROW EXECUTE FUNCTION log_changes();
该触发器在每次插入或更新后记录日志,不影响主从复制流程。从库仍可通过流复制获取数据变更,而审计信息仅保留在主库,便于本地分析。若需审计也同步到从库,可将audit_log表也设为复制对象,但需注意避免循环复制。
当从库发生读取超时或连接断开时,系统如何处理故障转移?
在读写分离架构中,从库可能因负载过高或网络问题导致读取超时或连接断开。PostgreSQL 14本身不提供自动故障转移,需依赖外部工具如Patroni、PgBouncer或应用层重试机制。当从库不可用时,应用应检测到异常并重试连接到主库,或切换到备用从库。对于高可用需求,建议结合Patroni管理集群,实现自动主从切换。此外,应用层应设置合理的连接超时和重试策略,避免因单点故障影响整体可用性。

















