文档章节

Postgres的日志实用功能

kenyon_君羊
 kenyon_君羊
发布于 2013/03/29 15:37
字数 1866
阅读 2.4W
收藏 31

3 月,跳不动了?>>>

不得不说,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);
             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秒的那个SQL,而没有记录1秒的SQL。

3.监控数据库的checkpoint
当数据库进行一项大更新操作时,如果参数设置不当,会在日志里留下大量的告警信息,频繁的做checkpoint会导致系统变慢,如:
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"".",,,,,,,""
但是不会记录系统正常的checkpoint,如果你想看系统一天之类发生了多少次checkpoint,以及每次checkpoint的一些详细信息,比如buffer,sync等,就可以通过设置log_checkpoints,该参数默认值是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.监控数据库的锁
数据库的锁通常可以在pg_locks这个系统表里找,但这只是当前的锁表/行信息,如果你想看一天内有多少个超过死锁时间的锁发生,可以在日志里设置并查看,log_lock_waits 默认是off,可以设置开启。这个可以区分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

--另一个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 64535.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: 64536.118 ms  statement: delete from t_ken_yon;",,,,,,,,,"psql"
还有一些Debug功能,适合修改源码调试,一般的的系统上并不需要,暂时比较关注的就这些。

© 著作权归作者所有

kenyon_君羊
粉丝 503
博文 173
码字总数 125197
作品 0
杭州
其他
私信 提问
加载中

评论(3)

ITGeGe在线教育社区
ITGeGe在线教育社区
2016年10月26、27日,上海浦东,Postgres中国用户大会2016(PG大象会)已经圆满落幕了,大会现场大咖分享视频已经正式上线(含嘉宾演讲PPT),你可直接微信搜索公众号“IT大咖说”(公众号ID:itdakashuo)观看视频。
PG中国社区的发展,需要你的成长与实践,好好努力吧,也可转发朋友圈进行分享,让更多的朋友来了解和学习postgres,为postgreSQL在中国的成长贡献一份力量!谢谢!
kenyon_君羊
kenyon_君羊 博主

引用来自“mark35”的评论

这是我开调试时相关设置值

log_line_prefix = '<%m (%p-%c-%x)>'
log_error_verbosity = verbose
debug_pretty_print = on
log_checkpoints = on
log_statement = 'none' # none, ddl, mod, all
log_temp_files = 3500 # log temporary files equal or larger
log_min_duration_statement = 0

#log_min_messages = info
#log_min_messages = notice
log_min_messages = error
#log_min_error_statement = debug5
log_min_error_statement = info

厉害的
mark35
mark35
这是我开调试时相关设置值

log_line_prefix = '<%m (%p-%c-%x)>'
log_error_verbosity = verbose
debug_pretty_print = on
log_checkpoints = on
log_statement = 'none' # none, ddl, mod, all
log_temp_files = 3500 # log temporary files equal or larger
log_min_duration_statement = 0

#log_min_messages = info
#log_min_messages = notice
log_min_messages = error
#log_min_error_statement = debug5
log_min_error_statement = info
PostgreSQL的日志类型

刚开始学习postgres的时候,可能对PostgreSQL中的日志概念比较模糊,到底有多少种日志,哪些日志是能删除的,各自又记录什么样的功能。 PostgreSQL中有三种日志,pglog,pgxlog和pgclog。 一...

kenyon_君羊
2012/12/21
4.7K
0
如何在 Debian 10上安装 PostgreSQL 数据库

如何在 Debian 10上安装 PostgreSQL 数据库 首先我们对PostgreSQL进行一个大致的介绍。 PostgreSQL,通常简称为Postgres,是一种开放源代码的通用对象关系数据库管理系统。它具有许多强大的功...

等会再说
2019/10/29
0
0
Postgresql在Linux下的配置与C程序应用

这些入门的常识实在不敢去CU上发,不过觉得确实很实用,对于初学者也很重要,所以只好在自己的博客备份一下。 (1)从www.postgresql.org下载pgadmin3-1.6.2.zip和postgresql-8.2.3.tar.gz前者...

红薯
2009/05/07
899
0
如何在Debian 9上安装PostgreSQL

PostgreSQL,通常简称为Postgres,是一种开源的通用对象 - 关系数据库管理系统。 PostgreSQL具有许多高级功能,如在线备份,即时恢复,嵌套事务,SQL和JSON查询,多版本并发控制(MVCC),异...

dragon_tech
2018/11/24
166
0
PostgreSQL Hot Standby

一、简介 PostgreSQL数据库提供了类似Oracle的standby数据库的功能。PostgreSQL9.0 standby数据库在应用WAL日志的同时,也可以提供只读服务,这是PostgreSQL9.0中最激动人心的功能,这个功能...

长平狐
2012/08/27
271
0

没有更多内容

加载失败,请刷新页面

加载更多

【Java系列002】正确使用@Transactional注解

你好,我是miniluo,今天我和你聊聊Spring声明式事务不生效的坑。 下面就让我和你一起学习有哪些几种情况下Spring声明式事务不生效的坑。 没有正确理解@Transactional注解 你是否曾经写过和下...

littleluoron
15分钟前
13
0
c++ boost Exector

前言 在应用开发中经常要执行一些异步的函数,有些是没有返回结果,有些是有返回结果,甚至有些是定时的任务,本文在boost io_service基础上搭建一个Exector来执行这些任务 代码 #include <...

青黑
19分钟前
14
0
MySQL5.7.25解压版安装教程

1.下载对应的zip包 2.选择合适的安装路径,并记录解压的路径 本人的是 D:\\MySQL\\mysql-5.7.25-winx64 3.配置环境变量,添加变量名path,并在下方的变量值框中输入mysql bin根路径 我的是 ...

jxlgzwh
29分钟前
14
0
闲置笔记本做服务器

最近研究树莓派,想起家里有一台10多年前的笔记本,本着不浪费的原则,拿出来充当树莓派,还自带UPS。安装了xubuntu18.04,只要不用浏览器上网,感觉也还行。下面记录一下几个要点。 替换apt...

propagator
今天
20
0
O(log n)究竟意味着什么? - What does O(log n) mean exactly?

问题: I am learning about Big O Notation running times and amortized times. 我正在学习Big O Notation运行时间和摊销时间。 I understand the notion of O(n) linear time, meaning t......

javail
今天
13
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部