PostgreSQL表变更同步Redis缓存失败问题求助

问题背景 #

需要实现PostgreSQL 15中表的INSERT/UPDATE/DELETE操作触发Windows Docker容器内Redis缓存的同步更新，尝试多种数据库内方案均失败：

• 编写PL/pgSQL触发器存储过程无法连接Redis
• plpython3u因版本兼容问题无法创建存储过程
• pg_redis扩展安装失败

原有方案错误分析 #

方案1（PL/pgSQL直接调用Redis） #

脚本中直接使用redis.StrictRedis完全错误，PL/pgSQL不支持Python第三方库的语法和对象，因此函数创建阶段就会失败。

方案2（dblink连接Redis） #

dblink是PostgreSQL用于跨实例连接其他PostgreSQL的工具，不支持Redis协议，根本无法实现Redis操作；同时dblink_connect的参数不符合函数签名，触发时直接报错。

方案3（pg_redis扩展） #

脚本依赖redis.add_server等pg_redis扩展函数，但你未成功安装该扩展（Windows环境下pg_redis预编译包稀缺，安装难度大），因此函数不存在导致触发报错。

可行解决方案 #

方案1：LISTEN/NOTIFY + 外部Python脚本（推荐） #

通过PostgreSQL的LISTEN/NOTIFY机制发送操作事件，外部Python脚本监听事件并更新Redis，避免数据库内部依赖问题，性能更稳定。

步骤1：创建触发器发送NOTIFY事件

-- 创建触发函数，将操作事件发送到指定频道
CREATE OR REPLACE FUNCTION notify_redis_sync() RETURNS TRIGGER AS $$
DECLARE
    payload JSON;
BEGIN
    IF TG_OP = 'DELETE' THEN
        payload := json_build_object(
            'op', TG_OP,
            'table', TG_TABLE_NAME,
            'id', OLD.id
        );
    ELSE
        payload := json_build_object(
            'op', TG_OP,
            'table', TG_TABLE_NAME,
            'data', row_to_json(NEW)
        );
    END IF;
    PERFORM pg_notify('redis_sync_channel', payload::TEXT);
    RETURN CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
END;
$$ LANGUAGE plpgsql;

-- 给目标表绑定触发器（示例为users表，替换为你的实际表名）
CREATE TRIGGER trigger_users_redis_sync
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION notify_redis_sync();

步骤2：编写Python监听脚本

import psycopg2
import psycopg2.extensions
import redis
import json

# PostgreSQL连接配置，替换为你的实际信息
PG_CONFIG = {
    'dbname': 'your_database',
    'user': 'your_user',
    'password': 'your_password',
    'host': 'localhost',
    'port': '5432'
}

# Redis连接配置，Windows下Docker容器的host用host.docker.internal
REDIS_CONFIG = {
    'host': 'host.docker.internal',
    'port': 32768,
    'password': 'redispw',
    'db': 0
}

def main():
    # 初始化Redis连接
    r = redis.StrictRedis(**REDIS_CONFIG)
    
    # 连接PostgreSQL并开启监听
    conn = psycopg2.connect(**PG_CONFIG)
    conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
    cur = conn.cursor()
    cur.execute("LISTEN redis_sync_channel;")
    
    print("开始监听PostgreSQL表操作事件...")
    while True:
        conn.poll()
        while conn.notifies:
            notify = conn.notifies.pop(0)
            payload = json.loads(notify.payload)
            op = payload['op']
            table = payload['table']
            
            if op == 'DELETE':
                r.hdel(table, payload['id'])
                print(f"已删除Redis缓存：{table}:{payload['id']}")
            elif op in ['INSERT', 'UPDATE']:
                data = payload['data']
                r.hset(table, data['id'], json.dumps(data))
                print(f"已更新Redis缓存：{table}:{data['id']}")

if __name__ == '__main__':
    main()

运行脚本

将脚本保存为redis_sync_listener.py，运行后保持后台运行，表操作事件会实时同步到Redis。

方案2：修复plpython3u环境（数据库内实现） #

如果一定要在数据库内部实现，需解决plpython3u的版本兼容问题：

1. 安装plpython3u扩展：确保PostgreSQL 15安装时勾选了plpython3u组件（若未安装，重新运行PostgreSQL安装程序添加）。
2. 安装Redis库：找到PostgreSQL自带的Python环境（路径通常为C:\Program Files\PostgreSQL\15\python\Scripts），执行：

cd "C:\Program Files\PostgreSQL\15\python\Scripts"
pip install redis

3. 创建plpython3u触发函数：

CREATE EXTENSION IF NOT EXISTS plpython3u;

CREATE OR REPLACE FUNCTION update_redis_plpython() RETURNS TRIGGER AS $$
import redis
import json

# 配置Redis连接信息
r = redis.StrictRedis(
    host='host.docker.internal',
    port=32768,
    password='redispw',
    db=0
)

if TG_OP == 'DELETE':
    r.hdel(TG_TABLE_NAME, OLD['id'])
else:
    r.hset(TG_TABLE_NAME, NEW['id'], json.dumps(NEW))

return OLD if TG_OP == 'DELETE' else NEW
$$ LANGUAGE plpython3u;

# 绑定触发器到目标表（替换为你的实际表名）
CREATE TRIGGER trigger_users_redis_sync
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION update_redis_plpython();

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