f 如何用SQL 語句查詢Aurora Postgres Log ~ 迪貝之家

Pages

如何用SQL 語句查詢Aurora Postgres Log

是透過Nagios來進行自動化
當然得寫成Plugin來處理
基本原理應該是透過postgres_fdw
把系統紀錄檔轉化為foreign 表格
AWS 工程師有寫了一篇專文來介紹
Working with RDS and Aurora PostgreSQL logs: Part 2
我要分享的是我寫的程式碼
code是抓取最近的兩個檔案來掃log
SET client_min_messages TO NOTICE;
do $$
declare
  version_check    boolean;
  r1               record;
  outputinfo       varchar(80) := 'N';
  chk_interval     interval := '00:05';
  cnt              int;
  chk              text;

begin


  for r1 in SELECT file_name FROM list_postgres_log_files() WHERE file_name LIKE 'postgresql.log.%.csv' ORDER BY 1 DESC LIMIT 2
     loop
     SELECT create_foreign_table_for_log_file('pglog', 'log_server', r1.file_name) into chk;
select count(*) into cnt
from pglog
where error_severity in ('FATAL','PANIC','WARNNING')
and   current_timestamp - log_time <= chk_interval;
         drop foreign table pglog;
    if cnt > 0
          then
             raise notice '%',r1.file_name;
    end if ;
end loop;
end$$;
#2020/07/27
看了
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-serverless.how-it-works.html#aurora-serverless.parameter-groups
內的這兩段的文章後
The DB instances in an Aurora Serverless cluster only have associated DB cluster parameter groups, not DB parameter groups. Serverless clusters rely on DB cluster parameter groups because DB instances are not permanently associated with Aurora Serverless clusters. Aurora scales the associated DB instance automatically as needed. The scaling operation involves modifying parameter values to be suitable for the larger or smaller capacity.
To customize configuration settings for an Aurora Serverless cluster, you can define your own DB cluster parameter group and modify the parameters it contains. You can modify both cluster-level parameters, and parameters that apply at the instance level in other kinds of Aurora clusters. However, when you modify a DB cluster parameter group that's associated with an Aurora Serverless DB cluster, modifications apply differently than for other DB cluster parameter groups
以及這篇blog文章
https://aws.amazon.com/tw/premiumsupport/knowledge-center/aurora-serverless-logs-enable-view/
By design, Aurora Serverless connects to a proxy fleet of DB instances that scales automatically. Because there isn't a direct DB instance to access and host the log files, you can't view the logs directly from the Amazon Relational Database Service (Amazon RDS) console. However, you can view and download logs that are sent to the CloudWatch console.
我的理解是
serverless FQDN連接的db instance應該是會變的
所以看log的話除了上傳到cloudwatch的資料外
當下正在運作的db instance所產出的系統記錄
應該還是可以透過sql 介面來撈取
但如果serverless如果因為沒有活動而關閉
之前的log應該就看不到了
所以serverless 得online 來watch