從 Postgres 外部表查看即時的資料庫 Log

 聽說 Oracle 資料庫可以從資料庫裡面用表格查看資料庫 Alert Log（難道是把資料庫的 log 存放在資料庫裡面？這樣關掉資料庫不就看不到 log。。。？）

但是 EDB 跟 PGSQL 跟普通的程式一樣，寫檔案到 log 檔案。沒辦法直接用表格查詢查看（這是 SQL 控。。。。！？），對於只想要用 SQL 界面解決大小事的人們不太方便～

不過 PGSQL 有一個外部表模組，可以讀取 csv 檔案～

而 file_fdw 手冊頁面有一個雛型，可以直接查看 csv log 檔案內容；不過實際的 Log 會定時切換檔案，這篇筆記紀錄這樣的內容：透過 file_fdw 查看最目前的 DB log 檔案內容。

在這之前要解決的事情，就是叫資料庫吐 CSV 格式的 log 檔案。這可以利用以下方式啟用：

[enterprisedb@epas ~]$ psql -qtAX -d edb << EOF
ALTER SYSTEM SET log_destination ='stderr,csvlog';
SELECT pg_reload_conf();
EOF
[enterprisedb@epas ~]$ 

通常 log 檔會設定 log rotation 定時/定量切換檔案，甚至是指向外部目錄。因此按照 file_fdw 手冊範例寫死檔案讀取不是一個好主意。。不過 file_fdw 支援 PROGRAM 選項，可以讓我們呼叫指令或 Script 讀取一些輸出（STDOUT）為 CSV 格式的結果（聽說這種功能在 Oracle 稱作 External Table）。因此這邊用 PROGRAM 決定今日 log 內容。

在 Postgres 裡面有兩個方式可以確定當前 log 檔案：

1) 有一個 $PGDATA/current_logfile 的檔案會記載現在 Log 檔案。

2) 也能從函數直接查詢 pg_current_logfile('csvlog')

這邊決定呼叫 SQL 的方式取出 log 檔案路徑，先湊出需要的指令：

[enterprisedb@epas ~]$ tail -f $PGDATA/$(psql -qtAX -d edb -c "select pg_current_logfile('stderr');")

接著就可以試著弄出查看每日紀錄 Today log 的外部表了。

先來啟用外掛

[enterprisedb@epas ~]$ psql -qtAX -d edb << EOF
CREATE EXTENSION file_fdw;
CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
EOF
[enterprisedb@epas ~]$ 

然後仿照手冊，湊一個外部表來查詢看看：需要注意欄位定義，請實際參照對應版本的 file_fdw 手冊範例。

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
) SERVER pglog
OPTIONS (
   program $$cat $PGDATA/$(psql -d edb -qtAX -c "select pg_current_logfile('csvlog')")$$,
   format 'csv'
);

來試著查詢看看：

edb=# select * from pglog limit 1;
          log_time          | user_name | database_name | process_id | connection_from |  session_id  | session_line_num | command_tag |   session_start_time   | virtual_transaction_id | transaction_id | error_severity | sql_state_code |                    message                     | detail |  hint  | internal_query | internal_query_pos | context | query  | query_pos | location | application_name | backend_type 
----------------------------+-----------+---------------+------------+-----------------+--------------+------------------+-------------+------------------------+------------------------+----------------+----------------+----------------+------------------------------------------------+--------+--------+----------------+--------------------+---------+--------+-----------+----------+------------------+--------------
 2021-03-06 14:19:47.071+08 | (NULL)    | (NULL)        |        434 | (NULL)          | 602159e5.1b2 |                5 | (NULL)      | 2021-02-09 13:33:57+08 | (NULL)                 |              0 | LOG            | 00000          | received SIGHUP, reloading configuration files | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | (NULL) |    (NULL) | (NULL)   |                  | postmaster
(1 row)

Time: 17.327 ms
edb=# 

查詢成功～

如果有遇到失敗的狀況如下，原因應該是出在 psql 環境變數沒設定好：例如以下沒有指定妥當 $PGDATABASE 但 psql 指令又找不到，就會有 exit code 1 的情形。

edb=# select * from pglog limit 1;
ERROR:  program "tail -100 $PGDATA/$(psql  -qtAX -c "select pg_current_logfile('csvlog')")" failed
DETAIL:  child process exited with exit code 1
Time: 16.322 ms
edb=# 

參考資料
PostgreSQL file_fdw详解及使用实践(使用SQL查询数据库日志)_Foucus的博客
Exposing PostgreSQL server logs to users via SQL - Cybertec
PgSQL · 应用案例 · 使用SQL查询数据库日志 - 数据库内核月报 － 2019 / 07
PostgreSQL - docs - Create a Foreign Table for PostgreSQL CSV Logs
PG file_fdw -从PG外部表读取文件/系统信息 - 知乎

file_fdw_program: backport of PROGRAM option for file_fdw / PostgreSQL Extension Network
Allow contrib/file_fdw to read from a program, like COPY FROM PROGRAM. (8e91e12b) · Commits · etersoft / postgresql · GitLab
Postgres: CSV file as a table using FDW
file_fdw - pgPedia - a PostgreSQL Encyclopedia