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