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')