在Postgresql中跟踪SQL语句

文章说明

本文主要说明如何在Postgresql中跟踪实际执行的SQL语句。

本文内容有如下应用场景：

在系统中执行了一系列的操作，需要将这些操作转化成SQL在正式环境上一次性执行，省下重新执行一遍的操作时间。
最近系统数据被人或被程序修改导致出错，领导想要查出是谁或者具体程序执行的时间。

解决方案

通过Postgresql自带的SQL日志记录功能

说明：Postgresql自带SQL日志记录功能，开启之后，可以在日志文件查看实际执行的SQL语句

操作步骤

修改Postgresql数据库配置文件：postgresql.conf
1. 这个文件一般位于Postgresql安装目录下的data目录中，例如：/home/PLM/postgresql2/data
2. 如果您是通过docker安装的Postgresql，这个目录一般也会映射到宿主机上
在配置文件中找到下面的配置项，开启日志记录功能。

# 日志记录总开关
logging_collector = on# 日志文件夹。注意：如果不指定全路径则是相对于data目录的地址。
log_directory = 'pg_log'# 日志文件格式，一搬保持默认即可
log_filename = 'postgres-%Y-%m-%d_%H%M%S.log'# 日志要记录的sql语句类型，一般选 mod。可选值：none, ddl, mod, all
# ddl: 仅记录数据定义语言（DDL）命令，比如创建、修改或删除表。
# mod: 记录所有 DDL 以及数据修改语言（DML）命令，比如插入、更新和删除。
log_statement = 'mod'#  启用或禁用记录 SQL 参数
log_parameters = on# 上面的参数如果没找到就该这个，参数的最大大小，-1代表无限制
log_parameter_max_length = 1024# 控制日志文件的自动轮换时间。1d表示1天。如果为0，则禁用轮换。
log_rotation_age = 1d# 设置日志文件的大小阈值。达到此大小时，日志文件将被自动轮换
log_rotation_size = 20MB# 是否记录每个 SQL 查询执行的持续时间
log_duration = off# 定义日志行的前缀格式。%d: 数据库名称，%m: 当前时间戳。 %h: 客户端主机名
log_line_prefix = '%m %d [%h] '
# 是否在日志中记录客户端的主机名
log_hostname = off

注意：如果你连的Postgresql是有多个环境连接不同的数据库的，那可以通过log_line_prefix来打印并区分出数据库名称。
修改好之后重启Postgresql。
1. 如果您的Postgresql装在Linux上：sudo systemctl restart postgresql
2. 如果您的Postgresql装在docker中：重启对应的docker服务即可
重启之后，只要系统有操作，pg_log应该就能看到对应的日志文件了。
1. 例如：postgresql-2025-06-16_103025.log
建议跟踪SQL之前可以清空日志文件排除干扰。
SQL语句格式如下：一般是先打印sql，再打印参数。需要人工将参数填到SQL中。（笔者亲测，也可以让AI帮你完成这个工作）

2025-06-16 13:02:44.782 UTC [129] LOG:  execute S_3: INSERT INTO sys_log  ( id,description,log_type,method,params,request_ip,time,username,address,browser,req_uri,create_by,create_time,update_by,update_time,del_flag,depart_code )  VALUES  ( $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17 )
2025-06-16 13:02:44.782 UTC [129] DETAIL:  parameters: $1 = '0899afa2e34f206730bc5b16be3f2988', $2 = '根据对象主键更新创建是否可见', $3 = 'INFO', $4 = 'com.purvar.plm.doc.controller.BaseObjectsAttributeController.updateCreateVisible()', $5 = '{ id: 311bb8fdc75a67dd40571deeed9a7876 visible: 1 }', $6 = '10.244.30.145', $7 = '1750078964782', $8 = 'admin', $9 = '内网IP', $10 = 'Edge', $11 = '/plm-doc/plmsys/api/baseObjectsAttribute/updateCreateVisible', $12 = 'admin', $13 = '2025-06-16 21:02:44.784', $14 = 'admin', $15 = '2025-06-16', $16 = '1', $17 = '001001004002'
2025-06-16 13:02:59.324 UTC [129] LOG:  execute S_237: UPDATE base_objects_attribute  SET object_id=$1,

数据库比对

说明：准备好2个数据库，在一个数据库上进行操作，然后进行数据库比对。

操作步骤

给你即将要操作的数据库，做一个备份。
1. 例如：你要操作test1数据库，你可以备份出来一个test-bak。
2. 通过navicat创建数据库的时候，可以将目标数据库做为模版。
3. 或者通过navicat的备份还原功能。
尽量选择一个使用人数较少的系统环境上进行操作。
使用navicat premium的数据同步可以比对出目标表和源表之间的数据差异并生成sql语句。

方案优缺点

优点：可以直接根据比对结果自动生成SQL语句，无须人工加工。

缺点：随着数据库中的表和数据不断增长，这个方案的性能会越来越低。并且仅适用于dml语句。