Postgres的日志實(shí)用功能 不得不說，Postgres的日志(pg_log，類似oracle的alter文件，非pg_xlog)確實(shí)是很靈活，功能也很豐富的，下面是借用postgres的日志來實(shí)現(xiàn)一些管理功能，下面涉及的參數(shù)都在文件$PGDATA/postgresql.conf里面。 OS:CentOS 6.2 DB:Postgre

Postgres的日志實(shí)用功能

不得不說，Postgres的日志(pg_log，類似oracle的alter文件，非pg_xlog)確實(shí)是很靈活，功能也很豐富的，下面是借用postgres的日志來實(shí)現(xiàn)一些管理功能，下面涉及的參數(shù)都在文件$PGDATA/postgresql.conf里面。

OS:CentOS 6.2

DB:Postgres 9.2.3

1.日志審計(jì)

審計(jì)是值記錄用戶的登陸退出以及登陸后在數(shù)據(jù)庫里的行為操作，可以根據(jù)安全等級(jí)不一樣設(shè)置不一樣級(jí)別的審計(jì)，

此處涉及的參數(shù)文件有：

logging_collector --是否開啟日志收集開關(guān)，默認(rèn)off，開啟要重啟DB

log_destination --日志記錄類型，默認(rèn)是stderr，只記錄錯(cuò)誤輸出

log_directory --日志路徑，默認(rèn)是$PGDATA/pg_log

log_filename --日志名稱，默認(rèn)是postgresql-%Y-%m-%d_%H%M%S.log

log_connections --用戶session登陸時(shí)是否寫入日志，默認(rèn)off

log_disconnections --用戶session退出時(shí)是否寫入日志，默認(rèn)off

log_rotation_age --保留單個(gè)文件的最大時(shí)長,默認(rèn)是1d,也有1h,1min,1s,個(gè)人覺得不實(shí)用

log_rotation_size --保留單個(gè)文件的最大尺寸，默認(rèn)是10MB

配置值：

logging_collector = on

log_destination = 'csvlog'

log_directory = '/home/postgres/pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_connections = on

log_disconnections = on

log_rotation_age = 1d

log_rotation_size = 20MB

配置完重啟DB，檢查日志情況

[postgres@localhost pg_log]$ ls -l

total 4

-rw-------. 1 postgres postgres 672 Mar 29 08:25 postgresql-2013-03-29_000000.csv

-rw-------. 1 postgres postgres 0 Mar 29 00:00 postgresql-2013-03-29_000000.log

[postgres@localhost pg_log]$

--登陸并退出，日志內(nèi)容有訪問的IP(local),訪問用戶，登陸和退出時(shí)間等信息，對檢查超級(jí)用戶的登陸退出是很有效的

[postgres@localhost pg_log]$ psql

psql (9.2.3)

Type "help" for help.

postgres=# \q

[postgres@localhost pg_log]$ tail -f postgresql-2013-03-29_000000.csv

2013-03-29 10:38:36.934 PDT,,,2236,"",5155d19c.8bc,1,"",2013-03-29 10:38:36 PDT,,0,LOG,00000,"connection received: host=[local]",,,,,,,,,""

2013-03-29 10:38:36.938 PDT,"postgres","postgres",2236,"[local]",5155d19c.8bc,2,"authentication",2013-03-29 10:38:36 PDT,2/11858,0,LOG,00000,"connection authorized: user=postgres database=postgres",,,,,,,,,""

2013-03-29 10:38:42.365 PDT,"postgres","postgres",2236,"[local]",5155d19c.8bc,3,"idle",2013-03-29 10:38:36 PDT,,0,LOG,00000,"disconnection: session time: 0:00:05.431 user=postgres database=postgres host=[local]",,,,,,,,,"psql"

記錄用戶登陸數(shù)據(jù)庫后的各種操作，postgres日志里分成了3類，通過參數(shù)pg_statement來控制，默認(rèn)的pg_statement參數(shù)值是none，即不記錄，可以設(shè)置ddl(記錄create,drop和alter)、mod(記錄ddl+insert,delete,update和truncate)和all(mod+select)。

示例：

[postgres@localhost ~]$ vi $PGDATA/postgresql.conf

log_statement = ddl

postgres=# show log_statement;

log_statement

---------------

ddl

(1 row)

postgres=# create table t_ken_yon(id int);

CREATE TABLE

postgres=# drop table t_ken_yon ;

DROP TABLE

postgres=#

[postgres@localhost pg_log]$ tail -f postgresql-2013-03-29_000000.csv

2013-03-29 11:01:29.048 PDT,"postgres","postgres",2324,"[local]",5155d681.914,3,"idle",2013-03-29 10:59:29 PDT,2/11945,0,LOG,00000,"statement: create table t_ken_yon(id int);",,,,,,,,,"psql"

2013-03-29 11:01:36.087 PDT,"postgres","postgres",2324,"[local]",5155d681.914,4,"idle",2013-03-29 10:59:29 PDT,2/11948,0,LOG,00000,"statement: drop table t_ken_yon ;",,,,,,,,,"psql"

--修改為mod級(jí)別，并reload

postgres=# show log_statement;

log_statement

---------------

mod

(1 row)

postgres=# insert into t_ken_yon values(1),(2);

INSERT 0 2

postgres=# delete from t_ken_yon where id =1;

DELETE 1

[postgres@localhost pg_log]$ tail -f postgresql-2013-03-29_000000.csv

2013-03-29 11:04:08.148 PDT,,,55,,514933a6.15b2,42,,2013-03-19 20:57:26 PDT,,0,LOG,00000,"received SIGHUP, reloading configuration files",,,,,,,,,""

2013-03-29 11:04:08.151 PDT,,,55,,514933a6.15b2,43,,2013-03-19 20:57:26 PDT,,0,LOG,00000,"parameter ""log_statement"" changed to ""mod""",,,,,,,,,""

2013-03-29 11:05:33.346 PDT,"postgres","postgres",2324,"[local]",5155d681.914,6,"idle",2013-03-29 10:59:29 PDT,2/11952,0,LOG,00000,"statement: insert into t_ken_yon values(1),(2);",,,,,,,,,"psql"

2013-03-29 11:05:52.033 PDT,"postgres","postgres",2324,"[local]",5155d681.914,7,"idle",2013-03-29 10:59:29 PDT,2/11953,0,LOG,00000,"statement: delete from t_ken_yon where id =1;",,,,,,,,,"psql"

--修改為all級(jí)別，并reload

postgres=# show log_statement;

log_statement

---------------

all

(1 row)

postgres=# select * from t_ken_yon;

id

----

2

(1 row)

[postgres@localhost pg_log]$ tail -f postgresql-2013-03-29_000000.csv

2013-03-29 11:07:14.820 PDT,,,55,,514933a6.15b2,44,,2013-03-19 20:57:26 PDT,,0,LOG,00000,"received SIGHUP, reloading configuration files",,,,,,,,,""

2013-03-29 11:07:14.821 PDT,,,55,,514933a6.15b2,45,,2013-03-19 20:57:26 PDT,,0,LOG,00000,"parameter ""log_statement"" changed to ""all""",,,,,,,,,""

2013-03-29 11:07:19.784 PDT,"postgres","postgres",2324,"[local]",5155d681.914,8,"idle",2013-03-29 10:59:29 PDT,2/119,0,LOG,00000,"statement: show log_statement;",,,,,,,,,"psql"

2013-03-29 11:07:28.631 PDT,"postgres","postgres",2324,"[local]",5155d681.914,9,"idle",2013-03-29 10:59:29 PDT,2/11955,0,LOG,00000,"statement: select * from t_ken_yon;",,,,,,,,,"psql"

一般的OLTP系統(tǒng)審計(jì)級(jí)別設(shè)置為ddl就夠了，因?yàn)橛涗?/script>輸出各種SQL對性能的影響還是蠻大的，安全級(jí)別高一點(diǎn)的也可以設(shè)置mod模式，有條件也可以不在數(shù)據(jù)庫層面做，而是購買設(shè)備放在網(wǎng)絡(luò)層監(jiān)控解析。

2.定位慢查詢SQL

可以設(shè)置一定時(shí)長的參數(shù)(log_min_duration_statement)，來記錄超過該時(shí)長的所有SQL，對找出當(dāng)前數(shù)據(jù)庫的慢查詢很有效。 比如log_min_duration_statement = 2s,記錄超過2秒的SQL，改完需要reload

示例:

postgres=# show log_min_duration_statement ;

log_min_duration_statement

----------------------------

2s

(1 row)

postgres=# \timing

Timing is on.

postgres=# select now(),pg_sleep(1);

now | pg_sleep

------------------------------+----------

2013-03-29 12:36:48.13353-07 |

(1 row)

Time: 1001.844 ms

postgres=# select now(),pg_sleep(4);

now | pg_sleep

-------------------------------+----------

2013-03-29 12:36:28.309595-07 |

(1 row)

Time: 4002.273 ms

[postgres@localhost pg_log]$ tail -f postgresql-2013-03-29_000000.csv

2013-03-29 12:36:19.265 PDT,"postgres","postgres",2324,"[local]",5155d681.914,10,"SELECT",2013-03-29 10:59:29 PDT,2/0,0,LOG,00000,"duration: 4027.183 ms statement: select now(),pg_sleep(4);",,,,,,,,,"psql"

可以看到只記錄了4秒的那個(gè)SQL，而沒有記錄1秒的SQL。

3.監(jiān)控?cái)?shù)據(jù)庫的checkpoint

當(dāng)數(shù)據(jù)庫進(jìn)行一項(xiàng)大更新操作時(shí)，如果參數(shù)設(shè)置不當(dāng)，會(huì)在日志里留下大量的告警信息，頻繁的做checkpoint會(huì)導(dǎo)致系統(tǒng)變慢，如:

2013-03-28 17:01:39.523 CST,,,10350,,50bd676b.286e,1,,2012-12-04 11:00:59 CST,,0,LOG,00000,"checkpoints are occurring too frequently (8 seconds apart)",,"Consider increasing the configuration parameter ""checkpoint_segments"".",,,,,,,""

2013-03-28 17:01:50.427 CST,,,10350,,50bd676b.286e,2,,2012-12-04 11:00:59 CST,,0,LOG,00000,"checkpoints are occurring too frequently (11 seconds apart)",,"Consider increasing the configuration parameter ""checkpoint_segments"".",,,,,,,""

但是不會(huì)記錄系統(tǒng)正常的checkpoint，如果你想看系統(tǒng)一天之類發(fā)生了多少次checkpoint，以及每次checkpoint的一些詳細(xì)信息，比如buffer,sync等，就可以通過設(shè)置log_checkpoints，該參數(shù)默認(rèn)值是off，修改log_checkpoints = on 示例：

postgres=# show log_checkpoints ;

log_checkpoints

-----------------

on

(1 row)

postgres=# checkpoint;

CHECKPOINT

postgres=#

[postgres@localhost pg_log]$ tail -f postgresql-2013-03-29_000000.csv

2013-03-29 12:43:38.900 PDT,,,5557,,514933a7.15b5,45,,2013-03-19 20:57:27 PDT,,0,LOG,00000,"checkpoint starting: immediate force wait",,,,,,,,,""

2013-03-29 12:43:38.941 PDT,,,5557,,514933a7.15b5,46,,2013-03-19 20:57:27 PDT,,0,LOG,00000,"checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.009 s, sync=0.000 s, total=0.040 s; sync files=0, longest=0.000 s, average=0.000 s",,,,,,,,,""

4.監(jiān)控?cái)?shù)據(jù)庫的鎖

數(shù)據(jù)庫的鎖通常可以在pg_locks這個(gè)系統(tǒng)表里找，但這只是當(dāng)前的鎖表/行信息，如果你想看一天內(nèi)有多少個(gè)超過死鎖時(shí)間的鎖發(fā)生，可以在日志里設(shè)置并查看，log_lock_waits 默認(rèn)是off，可以設(shè)置開啟。這個(gè)可以區(qū)分SQL慢是資源緊張還是鎖等待的問題。 示例:

postgres=# show log_lock_waits ;

log_lock_waits

----------------

on

(1 row)

postgres=# show deadlock_timeout ;

deadlock_timeout

------------------

1s

(1 row)

--模擬鎖

postgres=# begin;

BEGIN

postgres=# SELECT * FROM t_ken_yon ;

id

----

11

(1 row)

postgres=# delete from t_ken_yon ;

DELETE 1

--另一個(gè)session

postgres=# begin;

BEGIN

postgres=# delete from t_ken_yon;

--查看日志

[postgres@localhost pg_log]$ tail -f postgresql-2013-03-29_000000.csv

2013-03-29 14:01:02.673 PDT,"postgres","postgres",3056,"[local]",5155f4d9.bf0,6,"DELETE waiting",2013-03-29 13:08:57 PDT,5/12502,2659,LOG,00000,"process 3056 still waiting for ShareLock on transaction 2658 after 1000.398 ms",,,,,,"delete from t_ken_yon;",,,"psql"

2013-03-29 14:02:06.208 PDT,"postgres","postgres",3056,"[local]",5155f4d9.bf0,7,"DELETE waiting",2013-03-29 13:08:57 PDT,5/12502,2659,LOG,00000,"process 3056 acquired ShareLock on transaction 2658 after 535.339 ms",,,,,,"delete from t_ken_yon;",,,"psql"

2013-03-29 14:02:06.209 PDT,"postgres","postgres",3056,"[local]",5155f4d9.bf0,8,"DELETE",2013-03-29 13:08:57 PDT,5/12502,2659,LOG,00000,"duration: 536.118 ms statement: delete from t_ken_yon;",,,,,,,,,"psql"

還有一些Debug功能，適合修改源碼調(diào)試，一般的的系統(tǒng)上并不需要，暫時(shí)比較關(guān)注的就這些。

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com