点击蓝字关注我们
本文我们将介绍如何将 Postgres 日志文件内容写入数据库表中。
有时我们必须创建 Postgres 的日志信息的视图或报告，为此，我们可以将日志信息放入表内以备以后可以进一步分析。默认情况下，Postgres 中没有可以将日志文件信息记录到库内的表。一旦发生可疑情况，调试日志最快的方案是查询日志，而不是查看大量记录。实现这一目标的可能方法如下：
表审计
使用 csv 日志格式并将整个日志文件一次拉入表中
使用 file_fdw 扩展实时从日志文件获取信息导入到表中
在表审计的情况下，我们需要在表上创建触发器，然后将主表中的每个事件记录到审计表中。但这种请况无法像数据库日志文件中看到的那样捕获每个日志，但是可以记录插入、更新、删除等事件。本文我们重点介绍捕获 Postgres 日志的第二种和第三种方法。
一、使用 CSV 格式的日志输出
目的的第一步是将 log_destination 更改为 csvlog。打开 postgresql.conf 文件并添加如下更改并重新加载数据库以使之生效。
#log_destination = 'stderr'log_destination = 'csvlog'
重启服务后，查看日志格式变化,日志输出变为了csv格式：
-rw-------. 1 enterprisedb enterprisedb 2326 12月 24 03:25 enterprisedb-2024-12-24_025458.log-rw-------. 1 enterprisedb enterprisedb 2591 12月 30 17:24 enterprisedb-2024-12-30_160104.log-rw-------. 1 enterprisedb enterprisedb 1454 12月 30 17:24 enterprisedb-2024-12-30_172445.csv-rw-------. 1 enterprisedb enterprisedb 144 12月 30 17:24 enterprisedb-2024-12-30_172445.log
我们创建一个名为postgres_log的表：
CREATE TABLE postgres_log(log_time timestamp(3) with time zone,user_name text,database_name text,process_id integer,connection_from text,session_id text,session_line_num bigint,command_tag text,session_start_time timestamp with time zone,virtual_transaction_id text,transaction_id bigint,error_severity text,sql_state_code text,message text,detail text,hint text,internal_query text,internal_query_pos integer,context text,query text,query_pos integer,location text,application_name text,backend_type text,leader_pid integer,query_id bigint,PRIMARY KEY (session_id, session_line_num));
enterprisedb=# \dt关联列表架构模式 | 名称 | 类型 | 拥有者----------+--------------+--------+--------------public | foo | 数据表 | enterprisedbpublic | foo_audit | 数据表 | enterprisedbpublic | postgres_log | 数据表 | enterprisedb(3 行记录)
enterprisedb=# \d postgres_log数据表 "public.postgres_log"栏位 | 类型 | 校对规则 | 可空的 | 预设------------------------+-----------------------------+----------+----------+------log_time | timestamp(3) with time zone | | |user_name | text | | |database_name | text | | |process_id | integer | | |connection_from | text | | |session_id | text | | not null |session_line_num | bigint | | not null |command_tag | text | | |session_start_time | timestamp with time zone | | |virtual_transaction_id | text | | |transaction_id | bigint | | |error_severity | text | | |sql_state_code | text | | |message | text | | |detail | text | | |hint | text | | |internal_query | text | | |internal_query_pos | integer | | |context | text | | |query | text | | |query_pos | integer | | |location | text | | |application_name | text | | |backend_type | text | | |leader_pid | integer | | |query_id | bigint | | |索引："postgres_log_pkey" PRIMARY KEY, btree (session_id, session_line_num)
通过copy from命令将日志文件导入该表中：
enterprisedb=# \copy postgres_log FROM '/var/lib/edb/as16/data/log/enterprisedb-2024-12-30_172445.csv' WITH csv;COPY 13
enterprisedb=# select * from postgres_log limit 5;
log_time | 31-DEC-24 07:24:45.62 +08:00user_name |database_name |process_id | 4057connection_from |session_id | 677266dd.fd9session_line_num | 1command_tag |session_start_time | 31-DEC-24 07:24:45 +08:00virtual_transaction_id |transaction_id | 0error_severity | LOGsql_state_code | 00000message | ending log output to stderrdetail |hint | Future log output will go to log destination "csvlog".internal_query |internal_query_pos |context |query |query_pos |location |application_name |backend_type | postmasterleader_pid |query_id | 0----------------------------------------------------log_time | 31-DEC-24 07:24:45.62 +08:00user_name |database_name |process_id | 4057connection_from |session_id | 677266dd.fd9session_line_num | 2command_tag |session_start_time | 31-DEC-24 07:24:45 +08:00virtual_transaction_id |transaction_id | 0error_severity | LOGsql_state_code | 00000message | starting PostgreSQL 16.3 (EnterpriseDB Advanced Server 16.3.0) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bitdetail |hint |internal_query |internal_query_pos |context |query |query_pos |location |application_name |backend_type | postmasterleader_pid |query_id | 0
从日志文件中查看，确认信息导入到表中
[enterprisedb@epas-1 log]$ head -2 enterprisedb-2024-12-30_172445.csv2024-12-30 17:24:45.620 CST,,,4057,,677266dd.fd9,1,,2024-12-30 17:24:45 CST,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,,"","postmaster",,02024-12-30 17:24:45.620 CST,,,4057,,677266dd.fd9,2,,2024-12-30 17:24:45 CST,,0,LOG,00000,"starting PostgreSQL 16.3 (EnterpriseDB Advanced Server 16.3.0) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit",,,,,,,,,"","postmaster",,0
二、使用file_fdw
可以使用file_fdw 模块将文件作为外部表进行访问。执行以下操作来简化CSV日志文件的导入：
设置log_filename和log_rotation_age为日志文件提供一致、可预测的命名方案。这可以预测文件名，并了解单个日志文件何时完成并准备好导入。
将log_rotation_size设置为0以禁用基于大小的日志轮换，因为这会使日志文件名难以预测。
将log_truncate_on_rotation设置为on，以便旧日志数据不会与同一文件中的新数据混合。
上面的表定义包括主键规范。这对于防止意外重复导入相同信息。
修改配置信息如下：
log_destination = 'csvlog'log_filename = 'latestlogfilename'log_rotation_age = 1dlog_rotation_size = 0log_truncate_on_rotation = on
file_fdw 的用途之一是使 PostgreSQL 活动日志可用作查询表。为此，首先，您必须登录到CSV文件，我们将在下面的命令中看到somename.csv。
1. 首先，安装file_fdw作为扩展：
enterprisedb=# create extension file_fdw;CREATE EXTENSION
2. 创建外部服务器
enterprisedb=# CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
3. 使用 CREATE FOREIGN TABLE 命令
CREATE FOREIGN TABLE pglog (log_time timestamp(3) with time zone,user_name text,database_name text,process_id integer,connection_from text,session_id text,session_line_num bigint,command_tag text,session_start_time timestamp with time zone,virtual_transaction_id text,transaction_id bigint,error_severity text,sql_state_code text,message text,detail text,hint text,internal_query text,internal_query_pos integer,context text,query text,query_pos integer,location text,application_name text,backend_type text,leader_pid integer,query_id bigint) SERVER pglogOPTIONS ( filename 'log/latestlogfilename.csv', format 'csv' );
完成后，现在可以直接查询日志了。当然，在生产中，您需要定义某种方法来处理日志轮换。
enterprisedb=# \dE[S+]关联列表架构模式 | 名称 | 类型 | 拥有者 | 持续的 | 大小 | 描述----------+-------+--------------+--------------+--------+---------+------public | pglog | 所引用的外表 | enterprisedb | 永久的 | 0 bytes |(1 行记录)
enterprisedb=# \detr引用表列表架构模式 | 数据表 | 服务器----------+--------+--------public | pglog | pglog(1 行记录)
通过select查询外部表。
enterprisedb=# select * from pglog limit 2;-[ RECORD 1 ]----------+----------------------------------------------------------------log_time | 31-DEC-24 08:38:36.736 +08:00user_name |database_name |process_id | 6481connection_from |session_id | 6772782c.1951session_line_num | 1command_tag |session_start_time | 31-DEC-24 08:38:36 +08:00virtual_transaction_id |transaction_id | 0error_severity | LOGsql_state_code | 00000message | ending log output to stderrdetail |hint | Future log output will go to log destination "csvlog".internal_query |internal_query_pos |context |query |query_pos |location |application_name |backend_type | postmasterleader_pid |query_id | 0-[ RECORD 2 ]----------+----------------------------------------------------------------log_time | 31-DEC-24 08:38:36.736 +08:00user_name |database_name |process_id | 6481connection_from |session_id | 6772782c.1951session_line_num | 2command_tag |session_start_time | 31-DEC-24 08:38:36 +08:00virtual_transaction_id |transaction_id | 0error_severity | LOGsql_state_code | 00000message | starting PostgreSQL 16.3 (EnterpriseDB Advanced Server 16.3.0) on x86_64-pc-linux-gnu, compiled by gcc (GCC)8.5.0 20210514 (Red Hat 8.5.0-20), 64-bitdetail |hint |internal_query |internal_query_pos |context |query |query_pos |location |application_name |backend_type | postmasterleader_pid |query_id | 0
三、比较上面两种方法
外部表可以像普通表一样用于查询，但外部表在 PostgreSQL 服务器中没有存储。每当使用它时，PostgreSQL 都会要求外部数据包装器从外部源获取实时更新的数据，或者在更新命令的情况下将数据传输到外部源。
在第二种方法中查看数据运行时，随时可以使用 file_fdw，而在第一种方法中，如果想查看最新日志，则必须删除该表并再次创建它，以使用最新日志文件加载 COPY 命令。
关于公司
感谢您关注新智锦绣科技（北京）有限公司！作为 Elastic 的 Elite 合作伙伴及 EnterpriseDB 在国内的唯一代理和服务合作伙伴，我们始终致力于技术创新和优质服务，帮助企业客户实现数据平台的高效构建与智能化管理。无论您是关注 Elastic 生态系统，还是需要 EnterpriseDB 的支持，我们都将为您提供专业的技术支持和量身定制的解决方案。
欢迎关注我们，获取更多技术资讯和数字化转型方案，共创美好未来！
 |  |
Elastic 微信群 | EDB 微信群 |
发现“分享”和“赞”了吗，戳我看看吧
文章转载自新智锦绣，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
