当前位置:数据库 > Postgres >>

Postgres的日志实用功能

Postgres的日志实用功能
 
不得不说,Postgres的日志(pg_log,类似oracle的alter文件,非pg_xlog)确实是很灵活,功能也很丰富的,下面是借用postgres的日志来实现一些管理功能,下面涉及的参数都在文件$PGDATA/postgresql.conf里面。 
 
OS:CentOS 6.2 
DB:Postgres 9.2.3 
 
1.日志审计 
审计是值记录用户的登陆退出以及登陆后在数据库里的行为操作,可以根据安全等级不一样设置不一样级别的审计, 
此处涉及的参数文件有:
logging_collector      --是否开启日志收集开关,默认off,开启要重启DB
log_destination    --日志记录类型,默认是stderr,只记录错误输出
log_directory      --日志路径,默认是$PGDATA/pg_log
log_filename       --日志名称,默认是postgresql-%Y-%m-%d_%H%M%S.log
log_connections    --用户session登陆时是否写入日志,默认off
log_disconnections --用户session退出时是否写入日志,默认off
log_rotation_age   --保留单个文件的最大时长,默认是1d,也有1h,1min,1s,个人觉得不实用
log_rotation_size  --保留单个文件的最大尺寸,默认是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]$ 
 
--登陆并退出,日志内容有访问的IP(local),访问用户,登陆和退出时间等信息,对检查超级用户的登陆退出是很有效的
[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"
记录用户登陆数据库后的各种操作,postgres日志里分成了3类,通过参数pg_statement来控制,默认的pg_statement参数值是none,即不记录,可以设置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级别,并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,,,5554,,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,,,5554,,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级别,并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,,,5554,,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,,,5554,,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/11954,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系统审计级别设置为ddl就够了,因为记录输出各种SQL对性能的影响还是蛮大的,安全级别高一点的也可以设置mod模式,有条件也可以不在数据库层面做,而是购买设备放在网络层监控解析。 
 
  2.定位慢查询SQL 
可以设置一定时长的参数(log_min_duration_statement),来记录超过该时长的所有SQL,对找出当前数据库的慢查询很有效。 比如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);
   
Oracle
MySQL
Access
SQLServer
DB2
Excel
SQLite
SYBASE
Postgres
如果你遇到数据库难题:
请访问www.zzzyk.com 试试
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,