如何实现PostgreSQL对某一张表的WAL日志进行记录

PostgreSQL 没有内置的 binlog（binary log）机制像 MySQL 那样。它使用 Write-Ahead Logging (WAL) 来记录数据库的变更。要将这些变更记录到某张表中，通常可以使用逻辑复制（Logical Replication）和触发器（Triggers）来实现。以下是一个实现的思路：

1. 启用逻辑复制

首先，确保 PostgreSQL 已启用逻辑复制：
在 postgresql.conf 文件中，设置以下参数：

# 启用逻辑复制
wal_level = logical# 设置允许的最大同步槽数量
max_replication_slots = 4# 设置允许的最大订阅数量
max_wal_senders = 4

然后重启 PostgreSQL 服务：

sudo systemctl restart postgresql

2. 创建发布和订阅

1. 创建发布

在数据库中，创建一个发布：

CREATE PUBLICATION my_publication FOR TABLE my_table;

这样，my_table 的变更将会被记录并可以被订阅。

2. 创建订阅

在另一个数据库中，创建一个订阅：

CREATE SUBSCRIPTION my_subscription
CONNECTION 'dbname=mydb host=localhost user=myuser password=mypassword'
PUBLICATION my_publication;

这样，my_table 的变更将会被传输到本地的订阅数据库。

3. 使用触发器记录变更

如果你希望将变更记录到特定的表中，你可以创建触发器。以下是一个简单的示例：

1. 创建日志表

CREATE TABLE my_table_log (id SERIAL PRIMARY KEY,operation TEXT,old_data JSONB,new_data JSONB,changed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

2. 创建触发器函数

CREATE OR REPLACE FUNCTION log_my_table_changes() RETURNS TRIGGER AS $$
BEGININSERT INTO my_table_log (operation, old_data, new_data)VALUES (TG_OP,row_to_json(OLD),row_to_json(NEW));RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 创建触发器

CREATE TRIGGER my_table_changes
AFTER INSERT OR UPDATE OR DELETE ON my_table
FOR EACH ROW
EXECUTE FUNCTION log_my_table_changes();

这样，每当 my_table 表中的数据发生变化时，触发器会将这些变更记录到 my_table_log 表中。这种方法可以记录数据库表中的变更并进行审计。