RDS PostgreSQL提供log_fdw插件，可以查询数据库日志。
前提条件
该插件仅支持RDS PostgreSQL 11，如果无法创建插件，请参见升级内核小版本。背景信息
log_fdw插件提供如下两个函数，帮助您查询数据库日志。list_postgres_log_files()：列出所有的csvlog文件。create_foreign_table_for_log_file(IN table_name text, IN log_server text, IN log_file text)：创建一个与特定csvlog文件相对应的外部表。
操作步骤
- 创建log_fdw插件。
postgres=> create extension log_fdw; CREATE EXTENSION - 创建log服务器定义。
postgres=> create server <server名称> foreign data wrapper log_fdw;示例
postgres=> create server log_server foreign data wrapper log_fdw; CREATE SERVER - 调用
list_postgres_log_files()函数，列出所有的csvlog文件。postgres=> select * from list_postgres_log_files() order by 1; file_name | file_size_bytes ----------------------------------+----------------- postgresql-2020-01-10_095546.csv | 3794 postgresql-2020-01-10_100336.csv | 318318 postgresql-2020-01-11_000000.csv | 198437 postgresql-2020-01-11_083546.csv | 4775 postgresql-2020-01-13_030618.csv | 3347 - 调用
create_foreign_table_for_log_file(IN table_name text, IN log_server text, IN log_file text)函数，创建一个与特定csvlog文件相对应的外部表。postgres=> select create_foreign_table_for_log_file('<外部表名称>', '<log服务器名称>', '<csvlog文件名称>');示例
postgres=> select create_foreign_table_for_log_file('ft1', 'log_server', 'postgresql-2020-01-13_030618.csv'); create_foreign_table_for_log_file ----------------------------------- t (1 row) - 查询外部表即可查询到日志内容。
postgres=> select log_time, message from <外部表名称> order by log_time desc limit 2;示例
postgres=> select log_time, message from ft1 order by log_time desc limit 2; log_time | message ----------------------------+------------------------------------------------------------------- 2020-01-13 03:35:00.003+00 | cron job 1 completed: INSERT 0 1 1 2020-01-13 03:35:00+00 | cron job 1 starting: INSERT INTO cron_test VALUES ('Hello World') (2 rows)
外部表结构
postgres=> \d+ ft1
Foreign table "public.ft1"
Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
------------------------+-----------------------------+-----------+----------+---------+-------------+----------+--------------+-------------
log_time | timestamp(3) with time zone | | | | | plain | |
user_name | text | | | | | extended | |
database_name | text | | | | | extended | |
process_id | integer | | | | | plain | |
connection_from | text | | | | | extended | |
session_id | text | | | | | extended | |
session_line_num | bigint | | | | | plain | |
command_tag | text | | | | | extended | |
session_start_time | timestamp with time zone | | | | | plain | |
virtual_transaction_id | text | | | | | extended | |
transaction_id | bigint | | | | | plain | |
error_severity | text | | | | | extended | |
sql_state_code | text | | | | | extended | |
message | text | | | | | extended | |
detail | text | | | | | extended | |
hint | text | | | | | extended | |
internal_query | text | | | | | extended | |
internal_query_pos | integer | | | | | plain | |
context | text | | | | | extended | |
query | text | | | | | extended | |
query_pos | integer | | | | | plain | |
location | text | | | | | extended | |
application_name | text | | | | | extended | |
Server: log_server
FDW options: (filename 'postgresql-2020-01-13_030618.csv')