PostgreSQL教程FG159-PG触发器实战：数据变更日志记录

内容大纲

Part01-基础概念与理论知识
Part02-生产环境规划与建议
Part03-生产环境项目实施方案
Part04-生产案例与实战讲解
Part05-风哥经验总结与分享

内容简介

本篇文章介绍如何使用PostgreSQL触发器实现数据变更日志记录，风哥教程参考PostgreSQL官方文档Server Administration、SQL Language等相关内容。通过本文的学习，读者将能够实现完整的数据变更审计系统，记录所有对数据库的修改操作。

Part01-基础概念与理论知识

1.1 数据变更日志记录的重要性

数据变更日志记录在企业级应用中具有重要意义：

审计与合规：满足行业监管要求，如金融、医疗等行业
数据追溯：追踪数据的变更历史，了解数据的来龙去脉
故障排查：当数据出现问题时，可以快速定位问题原因
安全监控：监控异常的数据访问和修改操作
业务分析：分析数据变更趋势，为业务决策提供参考

1.2 PostgreSQL触发器的工作原理

PostgreSQL触发器是一种特殊的存储过程，在特定事件发生时自动执行：

触发时机：可以在事件前后执行（BEFORE/AFTER）
触发级别：可以在语句级或行级执行（STATEMENT/ROW）
触发事件：可以响应INSERT、UPDATE、DELETE操作
数据访问：可以访问OLD（修改前）和NEW（修改后）记录

1.3 日志记录的设计原则

设计数据变更日志记录系统时应遵循以下原则：

完整性：记录所有重要的数据变更操作
准确性：确保日志记录的信息准确无误
一致性：保持日志记录的格式和内容一致
性能：最小化对系统性能的影响
可扩展性：便于后续功能扩展和查询

Part02-生产环境规划与建议

2.1 日志表设计

日志表设计应包含以下字段：

日志ID：唯一标识符
表名：被修改的表名
操作类型：INSERT、UPDATE、DELETE
操作时间：操作发生的时间
操作用户：执行操作的用户
修改前数据：修改前的记录（JSONB格式）
修改后数据：修改后的记录（JSONB格式）
IP地址：执行操作的客户端IP地址
应用程序：执行操作的应用程序名称

2.2 触发器设计

触发器设计应考虑以下因素：

触发时机：通常使用AFTER触发器，避免影响主操作
触发级别：通常使用ROW级触发器，记录每一行的变更
触发事件：根据需要响应INSERT、UPDATE、DELETE操作
函数逻辑：确保触发器函数逻辑简单高效
错误处理：处理可能的错误，避免影响主操作

2.3 性能考虑

实现数据变更日志记录时需要考虑以下性能因素：

日志表大小：定期清理或归档旧日志
触发器开销：优化触发器函数，减少执行时间
索引设计：为日志表添加适当的索引，提高查询性能
批量操作：对于批量操作，考虑使用语句级触发器
异步处理：考虑使用异步方式处理日志记录

Part03-生产环境项目实施方案

3.1 实施步骤

实施数据变更日志记录的步骤：

创建日志表
创建触发器函数
为目标表创建触发器
测试触发器功能
部署到生产环境

3.2 测试与验证

测试与验证的方法：

测试各种数据变更操作
验证日志记录的完整性和准确性
测试批量操作的性能
验证错误处理机制

3.3 维护与管理

维护与管理数据变更日志的方法：

日志清理：定期清理或归档旧日志
性能监控：监控触发器执行性能
日志分析：定期分析日志数据，发现异常
备份与恢复：确保日志数据的备份与恢复

Part04-生产案例与实战讲解

4.1 基本数据变更日志记录

创建基本的数据变更日志记录系统：

# 创建日志表

CREATE TABLE fgedu_audit_log (
id SERIAL PRIMARY KEY,
table_name VARCHAR(100) NOT NULL,
operation VARCHAR(10) NOT NULL,
old_data JSONB,
new_data JSONB,
fgedu_name VARCHAR(100) NOT NULL,
operation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
client_ip VARCHAR(50),
fgapplication VARCHAR(100)
);

— 创建索引
CREATE INDEX fgedu_audit_log_table_name_idx ON fgedu_audit_log (table_name);
CREATE INDEX fgedu_audit_log_operation_time_idx ON fgedu_audit_log (operation_time);
CREATE INDEX fgedu_audit_log_fgedu_name_idx ON fgedu_audit_log (fgedu_name);

# 创建触发器函数

CREATE OR REPLACE FUNCTION fgedu_audit_trigger()
RETURNS TRIGGER AS $$
DECLARE
v_client_ip VARCHAR(50);
v_fgapplication VARCHAR(100);
BEGIN
— 获取客户端IP和应用程序信息
SELECT current_setting(‘fgapplication_name’, true) INTO v_fgapplication;

— 记录日志
IF TG_OP = ‘INSERT’ THEN
INSERT INTO fgedu_audit_log (
table_name, operation, new_data, fgedu_name, client_ip, fgapplication
) VALUES (
TG_TABLE_NAME, TG_OP, row_to_json(NEW), current_fgedu, v_client_ip, v_fgapplication
);
RETURN NEW;
ELSIF TG_OP = ‘UPDATE’ THEN
INSERT INTO fgedu_audit_log (
table_name, operation, old_data, new_data, fgedu_name, client_ip, fgapplication
) VALUES (
TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW), current_fgedu, v_client_ip, v_fgapplication
);
RETURN NEW;
ELSIF TG_OP = ‘DELETE’ THEN
INSERT INTO fgedu_audit_log (
table_name, operation, old_data, fgedu_name, client_ip, fgapplication
) VALUES (
TG_TABLE_NAME, TG_OP, row_to_json(OLD), current_fgedu, v_client_ip, v_fgapplication
);
RETURN OLD;
END IF;
END;
$$ LANGUAGE plpgsql;

# 为目标表创建触发器

— 创建测试表
CREATE TABLE fgedu_employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(100) NOT NULL,
salary NUMERIC(10,2) NOT NULL
);

— 创建触发器
CREATE TRIGGER fgedu_employees_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON fgedu_employees
FOR EACH ROW
EXECUTE FUNCTION fgedu_audit_trigger();

# 测试触发器

— 插入数据
INSERT INTO fgedu_employees (name, department, salary) VALUES (‘风哥1号’, ‘技术部’, 5000);

— 更新数据
UPDATE fgedu_employees SET salary = 6000 WHERE name = ‘风哥1号’;

— 删除数据
DELETE FROM fgedu_employees WHERE name = ‘风哥1号’;

— 查看日志
SELECT * FROM fgedu_audit_log ORDER BY operation_time DESC;
id | table_name | operation | old_data | new_data | fgedu_name | operation_time | client_ip | fgapplication
—-+——————+———–+——————————————+——————————————+———–+————————-+———–+————-
3 | fgedu_employees | DELETE | {“id”: 1, “name”: “风哥1号”, “department”: “技术部”, “salary”: 6000} | | fgedu | 2026-04-02 10:00:00 | | psql
2 | fgedu_employees | UPDATE | {“id”: 1, “name”: “风哥1号”, “department”: “技术部”, “salary”: 5000} | {“id”: 1, “name”: “风哥1号”, “department”: “技术部”, “salary”: 6000} | fgedu | 2026-04-02 10:00:00 | | psql
1 | fgedu_employees | INSERT | | {“id”: 1, “name”: “风哥1号”, “department”: “技术部”, “salary”: 5000} | fgedu | 2026-04-02 10:00:00 | | psql

4.2 高级数据变更日志记录

创建高级的数据变更日志记录系统，包括字段级变更记录：

# 创建字段级变更日志表

CREATE TABLE fgedu_audit_field_log (
id SERIAL PRIMARY KEY,
audit_log_id INTEGER NOT NULL REFERENCES fgedu_audit_log(id),
field_name VARCHAR(100) NOT NULL,
old_value TEXT,
new_value TEXT
);

— 创建索引
CREATE INDEX fgedu_audit_field_log_audit_log_id_idx ON fgedu_audit_field_log (audit_log_id);

# 创建高级触发器函数

CREATE OR REPLACE FUNCTION fgedu_audit_field_trigger()
RETURNS TRIGGER AS $$
DECLARE
v_client_ip VARCHAR(50);
v_fgapplication VARCHAR(100);
v_audit_log_id INTEGER;
v_field_name TEXT;
v_old_value TEXT;
v_new_value TEXT;
v_record_old JSONB;
v_record_new JSONB;
v_keys TEXT[];
v_key TEXT;
BEGIN
— 获取客户端IP和应用程序信息
SELECT current_setting(‘fgapplication_name’, true) INTO v_fgapplication;

— 记录基本日志
IF TG_OP = ‘INSERT’ THEN
INSERT INTO fgedu_audit_log (
table_name, operation, new_data, fgedu_name, client_ip, fgapplication
) VALUES (
TG_TABLE_NAME, TG_OP, row_to_json(NEW), current_fgedu, v_client_ip, v_fgapplication
) RETURNING id INTO v_audit_log_id;

— 记录字段级变更
v_record_new := row_to_json(NEW);
v_keys := ARRAY(SELECT jsonb_object_keys(v_record_new));

FOREACH v_key IN ARRAY v_keys LOOP
v_field_name := v_key;
v_new_value := v_record_new->>v_key;

INSERT INTO fgedu_audit_field_log (
audit_log_id, field_name, new_value
) VALUES (
v_audit_log_id, v_field_name, v_new_value
);
END LOOP;

RETURN NEW;
ELSIF TG_OP = ‘UPDATE’ THEN
INSERT INTO fgedu_audit_log (
table_name, operation, old_data, new_data, fgedu_name, client_ip, fgapplication
) VALUES (
TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW), current_fgedu, v_client_ip, v_fgapplication
) RETURNING id INTO v_audit_log_id;

— 记录字段级变更
v_record_old := row_to_json(OLD);
v_record_new := row_to_json(NEW);
v_keys := ARRAY(SELECT jsonb_object_keys(v_record_old));

FOREACH v_key IN ARRAY v_keys LOOP
v_field_name := v_key;
v_old_value := v_record_old->>v_key;
v_new_value := v_record_new->>v_key;

— 只记录发生变化的字段
IF v_old_value IS DISTINCT FROM v_new_value THEN
INSERT INTO fgedu_audit_field_log (
audit_log_id, field_name, old_value, new_value
) VALUES (
v_audit_log_id, v_field_name, v_old_value, v_new_value
);
END IF;
END LOOP;

RETURN NEW;
ELSIF TG_OP = ‘DELETE’ THEN
INSERT INTO fgedu_audit_log (
table_name, operation, old_data, fgedu_name, client_ip, fgapplication
) VALUES (
TG_TABLE_NAME, TG_OP, row_to_json(OLD), current_fgedu, v_client_ip, v_fgapplication
) RETURNING id INTO v_audit_log_id;

— 记录字段级变更
v_record_old := row_to_json(OLD);
v_keys := ARRAY(SELECT jsonb_object_keys(v_record_old));

FOREACH v_key IN ARRAY v_keys LOOP
v_field_name := v_key;
v_old_value := v_record_old->>v_key;

INSERT INTO fgedu_audit_field_log (
audit_log_id, field_name, old_value
) VALUES (
v_audit_log_id, v_field_name, v_old_value
);
END LOOP;

RETURN OLD;
END IF;
END;
$$ LANGUAGE plpgsql;

# 为目标表创建高级触发器

— 先删除旧触发器
DROP TRIGGER IF EXISTS fgedu_employees_audit_trigger ON fgedu_employees;

— 创建新触发器
CREATE TRIGGER fgedu_employees_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON fgedu_employees
FOR EACH ROW
EXECUTE FUNCTION fgedu_audit_field_trigger();

# 测试高级触发器

— 插入数据
INSERT INTO fgedu_employees (name, department, salary) VALUES (‘风哥2号’, ‘市场部’, 7000);

— 更新数据
UPDATE fgedu_employees SET salary = 8000, department = ‘销售部’ WHERE name = ‘风哥2号’;

— 查看日志
SELECT * FROM fgedu_audit_log ORDER BY operation_time DESC;
id | table_name | operation | old_data | new_data | fgedu_name | operation_time | client_ip | fgapplication
—-+——————+———–+——————————————+——————————————+———–+————————-+———–+————-
5 | fgedu_employees | UPDATE | {“id”: 2, “name”: “风哥2号”, “department”: “市场部”, “salary”: 7000} | {“id”: 2, “name”: “风哥2号”, “department”: “销售部”, “salary”: 8000} | fgedu | 2026-04-02 10:00:00 | | psql
4 | fgedu_employees | INSERT | | {“id”: 2, “name”: “风哥2号”, “department”: “市场部”, “salary”: 7000} | fgedu | 2026-04-02 10:00:00 | | psql

— 查看字段级变更日志
SELECT * FROM fgedu_audit_field_log WHERE audit_log_id = 5;
id | audit_log_id | field_name | old_value | new_value
—-+————–+————+———–+———–
7 | 5 | department | 市场部 | 销售部
8 | 5 | salary | 7000 | 8000

4.3 日志查询与分析

查询和分析数据变更日志：

# 查询特定表的变更日志

SELECT * FROM fgedu_audit_log
WHERE table_name = ‘fgedu_employees’
ORDER BY operation_time DESC;
id | table_name | operation | old_data | new_data | fgedu_name | operation_time | client_ip | fgapplication
—-+——————+———–+——————————————+——————————————+———–+————————-+———–+————-
5 | fgedu_employees | UPDATE | {“id”: 2, “name”: “风哥2号”, “department”: “市场部”, “salary”: 7000} | {“id”: 2, “name”: “风哥2号”, “department”: “销售部”, “salary”: 8000} | fgedu | 2026-04-02 10:00:00 | | psql
4 | fgedu_employees | INSERT | | {“id”: 2, “name”: “风哥2号”, “department”: “市场部”, “salary”: 7000} | fgedu | 2026-04-02 10:00:00 | | psql

# 查询特定用户的操作日志

SELECT * FROM fgedu_audit_log
WHERE fgedu_name = ‘fgedu’
ORDER BY operation_time DESC;
id | table_name | operation | old_data | new_data | fgedu_name | operation_time | client_ip | fgapplication
—-+——————+———–+——————————————+——————————————+———–+————————-+———–+————-
5 | fgedu_employees | UPDATE | {“id”: 2, “name”: “风哥2号”, “department”: “市场部”, “salary”: 7000} | {“id”: 2, “name”: “风哥2号”, “department”: “销售部”, “salary”: 8000} | fgedu | 2026-04-02 10:00:00 | | psql
4 | fgedu_employees | INSERT | | {“id”: 2, “name”: “风哥2号”, “department”: “市场部”, “salary”: 7000} | fgedu | 2026-04-02 10:00:00 | | psql

# 分析数据变更趋势

# 创建日志清理脚本

#!/bin/bash
# audit_log_cleanup.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
# web: `http://www.fgedu.net.cn`
# web: `http://www.fgedu.net.cn`
# web: `http://www.fgedu.net.cn`

# 保留最近30天的日志
RETENTION_DAYS=30

# 执行清理
psql -d fgedudb -c “DELETE FROM fgedu_audit_field_log WHERE audit_log_id IN (SELECT id FROM fgedu_audit_log WHERE operation_time < NOW() - INTERVAL '$RETENTION_DAYS days');" psql -d fgedudb -c "DELETE FROM fgedu_audit_log WHERE operation_time < NOW() - INTERVAL '$RETENTION_DAYS days';" # 执行VACUUM psql -d fgedudb -c "VACUUM ANALYZE fgedu_audit_log;" psql -d fgedudb -c "VACUUM ANALYZE fgedu_audit_field_log;" echo "Audit log cleanup completed successfully."

Part05-风哥经验总结与分享

5.1 常见问题与解决方案

数据变更日志记录的常见问题及解决方案：

问题：日志表增长过快
解决方案：定期清理或归档旧日志，设置合理的保留期限
问题：触发器影响主操作性能
解决方案：优化触发器函数，考虑使用异步处理
问题：日志记录不完整
解决方案：确保触发器覆盖所有必要的操作类型和表
问题：日志查询性能差
解决方案：为日志表添加适当的索引，优化查询语句
问题：触发器错误影响主操作
解决方案：在触发器中添加错误处理，确保即使触发器出错也不影响主操作

5.2 性能优化技巧

数据变更日志记录的性能优化技巧：

批量操作优化：对于批量操作，考虑使用语句级触发器或批量插入日志
索引优化：为日志表的常用查询字段添加索引
分区表：对日志表使用分区，按时间或表名分区
异步处理：使用消息队列或其他异步机制处理日志记录
批量提交：对于大量日志记录，使用批量提交减少事务开销

5.3 企业级应用建议

企业级应用中的数据变更日志记录建议：

全面覆盖：确保所有重要表都有日志记录
安全存储：确保日志数据的安全存储和备份
合规性：确保日志记录符合行业监管要求
监控与告警：监控日志记录的异常情况
集成分析：将日志数据集成到分析系统中，提供业务洞察

更多视频教程www.fgedu.net.cn

学习交流加群风哥微信: itpux-com

学习交流加群风哥QQ113257174

风哥提示：数据变更日志记录是企业级应用的重要组成部分，建议在设计阶段就考虑完整的审计方案。

更多学习教程公众号风哥教程itpux_com

from PostgreSQL视频:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html