該怎麼進行Postgres 最基本的例行檢核工作

Postgres 是一個幾乎把所有訊息都寫到系統記錄檔的RDBMS，所以在日常維運上，不透過工具來分析log內容，我還真不知道該怎麼管理我手上的postgres。我用的工具是pgbager-3.5 ，有興趣的，去問一下谷大哥，它一定會告訴你相關資訊。打開pgbager報表後，最上頭就是可供讀取的報表

pgBadger: PostgreSQL log analyzer

Overall statistics |
Hourly statistics | Queries by type |
Most frequent waiting queries (N) |
Queries that waited the most |

Queries generating the most temporary files (N) |
Queries generating the largest temporary files |

Slowest queries |
Queries that took up the most time (N) |
Slowest queries (N) |
Most frequent queries (N) | Locks by type | Sessions per database | Sessions per user | Sessions per host | | Logs per type

Normalized reports are marked with a "(N)".

每份報表都是用pipe符號來切開，不太可能日檢時查看每個部份的報表，就個人的經驗，只會看底下這幾個報表的資訊 :

•  Hourly statistics
•  Most frequent waiting queries (N)
•  Queries that waited the most
•  Slowest queries
•  Most frequent events (N) 

礙於報表列的部份用截圖的方式像素在這裡太難表達出我想傳達的意念，所以我把上述幾個報表的html code直接抓下來修改後貼上，你用桌機的螢幕看pbagder報表時，當然不會看到這麼鱉的畫面。

我們來看報表吧~~ 很直覺啦 ~~

Most frequent events (N) ^

Rank	Times reported	Error
1	5,761 Day Hour Count Feb 07 11 5,761	FATAL: sorry, too many clients already FATAL: sorry, too many clients already
2	395 Day Hour Count Feb 08 02 379 04 16	ERROR: could not serialize access due to concurrent update ERROR: could not serialize access due to concurrent update Statement: update public.pmm_account_bal set accum_redeem=$1, accum_topup=$2, alter_time=$3, alter_user=$4, balance=$5, card_category=$6, issuer_id=$7, last_redeem_date=$8, last_topup_date=$9, negative_amt=$10, status=$11, transfer_date=$12 where account_id=$13 and exp_date=$14 and wallet_close_date=$15 and wallet_id=$16 and wallet_start_date=$17
3	343 Day Hour Count Feb 07 10 1 13 1 Feb 08 02 333 04 8	ERROR: duplicate key value violates unique constraint "..." ERROR: duplicate key value violates unique constraint "pk_pts_trans_20140901" Detail: Key (term_id, rrn)=(14454401, 39020710134633) already exists. Statement: insert into public.pts_trans (account_id, alter_time, alter_user, auth_code, batch_no, branch_id, card_category, card_nbr, credit_auth_amt, credit_card_nbr, cut_date, cut_result, discount_amt, discount_status, exp_date, invoice_no, issuer_id, manual_batch_no, member_id, merchant_campaign_id, merchant_id, orig_rrn, orig_term_id, process_code, promotion_amt, repair_status, resp_code, reversal_date, reversal_rrn, reversal_stan, short_amt, staff_status, stan, status, term_settl_date, term_settl_status, trans_amt, trans_date, trans_wallet_qty, rrn, term_id) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41) ERROR: duplicate key value violates unique constraint "pk_pmm_account_bal_record" Detail: Key (trans_date, account_id, exp_date, wallet_id, wallet_start_date, wallet_close_date)=(2019-02-06 00:00:00, 000000459956, 9999-12-31 23:59:59, 20000001, 2019-01-01 00:00:00, 2020-12-31 23:59:59) already exists. Statement: insert into public.pmm_account_bal_record (accum_redeem, accum_topup, alter_time, alter_user, balance, card_category, day_accum_redeem, day_accum_topup, issuer_id, account_id, exp_date, trans_date, wallet_close_date, wallet_id, wallet_start_date) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15) ERROR: duplicate key value violates unique constraint "pk_pmm_account_bal" Detail: Key (account_id, exp_date, wallet_id, wallet_start_date, wallet_close_date)=(1Rt3BZ00BjVD, 9999-12-31 23:59:59, 20000001, 2019-01-01 00:00:00, 2020-12-31 23:59:59) already exists. Statement: insert into public.pmm_account_bal (accum_redeem, accum_topup, alter_time, alter_user, balance, card_category, issuer_id, last_redeem_date, last_topup_date, negative_amt, status, transfer_date, account_id, exp_date, wallet_close_date, wallet_id, wallet_start_date) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)

Most frequent waiting queries (N)^

Rank	Count	Total wait time (s)	Min/Max/Avg duration (s)	Query
1	2	16.695s	8.321s/8.374s/4.187s	INSERT into public.trans_log (card_nbr,header_termino,merchant_id,message_type,process_code,prog_name,receive_date,resp_code,rrn,system_ip,term_id,trans_date,id ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$10,$10,$10);

Queries that waited the most^

Rank	Wait time (s)	Query
1	8.374s	INSERT into public.trans_log (card_nbr,header_termino,merchant_id,message_type,process_code,prog_name,receive_date,resp_code,rrn,system_ip,term_id,trans_date,id ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$10,$10,$10);
2	8.321s	INSERT into public.trans_log (card_nbr,header_termino,merchant_id,message_type,process_code,prog_name,receive_date,resp_code,rrn,system_ip,term_id,trans_date,id ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$10,$10,$10);

Slowest queries ^

Rank	Duration (s)	Query
1	14.598s	SELECT count (* ) FROM ( SELECT DISTINCT s_time, e_time, request_id, STATUS FROM common_status ) WHERE STATUS = 'Success' AND to_char(s_time,'yyyy/MM/dd' ) = to_char(now) - interval'1 days','yyyy/MM/dd' );
2	14.397s	SELECT fun_id, count (fun_id ) FROM ( SELECT fun_id, s_time FROM ( SELECT DISTINCT s_time, e_time, request_id, fun_id FROM common_status ) WHERE to_char (s_time,'yyyy/MM/dd' ) = to_char (now () - interval '1 days', 'yyyy/MM/dd' ) ) GROUP BY fun_id;

Hourly Statistics

我日檢時只看checkpoint sync的數據，因為它跟整體I/O有關，圖我修改過，完整報表不會這麼精簡

這是我丟在系統cron跑的一段shell script，為的是自動跑出前一天08:00~今天08:00間的報表,上班的時候就能看系統前一天的運作概況了

LOG_DIR=/data/pg_log
BADGER_HOME=~/steven/ma/pgbadger-3.5
LOG_DATE=`date +%Y%m%d`

LOG_BEGIN="$(date --date='1 days ago' +%Y-%m-%d) 08:00:00"
LOG_END="$(date  +%Y-%m-%d) 08:00:00"
PREV_DATE=$(date --date='1 days ago' +%Y-%m-%d)
CURR_DATE=`date +%Y-%m-%d`
$BADGER_HOME/pgbadger -f stderr -b "$LOG_BEGIN" -e "$LOG_END" -G  -o ~/steven/log/YOURDB."$LOG_DATE".html $LOG_DIR/*$PREV_DATE*.log $LOG_DIR/*$CURR_DATE*.log

cd ~/steven/log/
HOST=your ftp host
USER=your ftp account
PASSWD=your ftp account password
/usr/bin/ftp -nv «!x
open $HOST
user $USER $PASSWD
prompt
cd pgbager_parse
binary
mput YOURDB.$LOG_DATE.html
bye
!x

Posted in: 資料庫,監控,DB,pgbager,Postgres,RDBMS