解读年度数据库性能：PostgreSQL的日志文件和数据加载

于 2019-08-14 18:01:43 发布
阅读量227 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Enmotech/article/details/99619049
版权

墨墨导读：本文主要介绍PostgreSQL的日志文件参数及注意事项，从csv日志中载入数据库。通过灵活的数据加载方式，让SQL在处理很多问题上更加简捷便利。


运行日志参数


1.1 运行日志主要参数


运行日志主要相关的参数如下，默认没有开启的话没有log目录，开启后会自动生成。



参数

可选值/说明

log_destination = 'csvlog'  

# stderr, csvlog, syslog, and eventlog ,csvlog requires logging_collector to be on

一般选择这个，可以将csv日志导入数据库中查看

logging_collector = on                        

# Enable capturing of stderr and csvlog into log files

选scv格式的日志必须设置这个参数on，修改需要重启

log_directory = 'log'

日志输出目录

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

日志文件名字格式

log_file_mode = 0600                          

# creation mode for log files   日志文件权限

log_truncate_on_rotation = on

设置重用日志

log_rotation_age = 1d

多长时间重用日志

log_rotation_size = 10MB

日志达到多大重用

log_min_messages = warning                    

# debug5,debug4,debug3,debug2,debug1,info,notice,warning,error,log,fatal,panic

log_min_duration_statement = 60

慢sql记录（超过多长时间的sql）

log_checkpoints = on

记录checkpoint操作

log_connections = on

记录会话连接操作

log_disconnections = on

记录会话断开操作

log_duration = on

记录sql执行时间

log_lock_waits = on                           

# log lock waits >= deadlock_timeout   记录时间长的阻塞

log_statement = 'ddl'                        

# none, ddl, mod, all    记录ddl

 

1.2  注意事项


  • 设置csv格式日志的话一定要设置logging_collector 为 on

  • pg10版本的运行日志一般在$PGDATA/log目录下

  • log目录是开启运行日志后自动生成的

  • 可以通过log_rotation_age来设置多久重新生成一个日志文件

  • 可以通过log_rotation_size来设置多大的日志来重新生成日志文件

  • 上面两个都需要配合log_truncate_on_rotation 为 on来使用

  • 可以开启log_duration来记录sql执行时间

  • 可以开启log_statement来记录数据库ddl


1.3  csv日志载入数据库


Oracle有外部表，pg也有fdw。oracle可以用外部表的方式将alert日志载入到数据库中用SQL来查看。PG可以用copy命令将csv日志载入到数据库中用SQL来查看。这种方式都可以很方便得用sql来查询想要的日志内容。这种方式的有点是显而易见的，就是可以很容易得用SQL来查询和过滤日志，pg的日志文件可以截断分割成若干小文件，可以载入自己需要的日志。而Oracle的alert通常会很大。

缺点也是显而易见的，如果数据库挂了就不能用这种方式来查看日志。而且pg的csv日志不容易直接阅读。


1.3.1 创建日志表


创建了一个数据库和新的表来载入日志

postgres=# create database test;	
CREATE DATABASE	
postgres=# \c test	
You are now connected to database "test" as user "pg12".	
test=# CREATE TABLE pg_log	
test-# (	
test(#   log_time timestamp(3) with time zone,	
test(#   user_name text,	
test(#   database_name text,	
test(#   process_id integer,	
test(#   connection_from text,	
test(#   session_id text,	
test(#   session_line_num bigint,	
test(#   command_tag text,	
test(#   session_start_time timestamp with time zone,	
test(#   virtual_transaction_id text,	
test(#   transaction_id bigint,	
test(#   error_severity text,	
test(#   sql_state_code text,	
test(#   message text,	
test(#   detail text,	
test(#   hint text,	
test(#   internal_query text,	
test(#   internal_query_pos integer,	
test(#   context text,	
test(#   query text,	
test(#   query_pos integer,	
test(#   location text,	
test(#   application_name text,	
test(#   PRIMARY KEY (session_id, session_line_num)	
test(# );	

	
CREATE TABLE	
test=#


1.3.2 查看日志文件名字


[pg12@whf307 ~]$ cd $PGDATA/log	
[pg12@whf307 log]$ ls -rtl	
total 24	
-rw------- 1 pg12 pg12  166 May 30 13:32 postgresql-2019-05-30_133202.log	
-rw------- 1 pg12 pg12  496 May 30 13:32 postgresql-2019-05-30_133202.csv	
-rw------- 1 pg12 pg12    0 May 30 13:32 postgresql-2019-05-30_133254.log	
-rw------- 1 pg12 pg12  170 May 30 13:32 postgresql-2019-05-30_133254.csv	
-rw------- 1 pg12 pg12  166 May 30 13:33 postgresql-2019-05-30_133324.log	
-rw------- 1 pg12 pg12 6566 May 30 16:16 postgresql-2019-05-30_133324.csv	
-rw------- 1 pg12 pg12    0 May 31 00:00 postgresql-2019-05-31_000000.log	
-rw------- 1 pg12 pg12    0 May 31 00:00 postgresql-2019-05-31_000000.csv	
[pg12@whf307 log]$	
[pg12@whf307 log]$ pwd	
/soft/pg_data/log	
[pg12@whf307 log]$


1.3.3 载入到数据库


[pg12@whf307 log]$ psql  test	
psql (12beta1)	
Type "help" for help.	
test=# \d	
        List of relations	
 Schema |  Name  | Type  | Owner 	
--------+--------+-------+-------	
 public | pg_log | table | pg12	
(1 row)	

	
test=# copy pg_log from '/soft/pg_data/log/postgresql-2019-05-30_133324.csv' with csv; 	
COPY 32


1.3.4 查看日志


这样就可以用sql来查看了。执行一个普通查询

test=# select relfilenode from pg_class where relname='pg_log';	
 relfilenode 	
-------------	
       16385	
(1 row)


载入最新的日志。这里可以重复载入，不会覆盖之前的数据。

[pg12@whf307 log]$ ls -rtl	
total 32	
-rw------- 1 pg12 pg12  166 May 30 13:32 postgresql-2019-05-30_133202.log	
-rw------- 1 pg12 pg12  496 May 30 13:32 postgresql-2019-05-30_133202.csv	
-rw------- 1 pg12 pg12    0 May 30 13:32 postgresql-2019-05-30_133254.log	
-rw------- 1 pg12 pg12  170 May 30 13:32 postgresql-2019-05-30_133254.csv	
-rw------- 1 pg12 pg12  166 May 30 13:33 postgresql-2019-05-30_133324.log	
-rw------- 1 pg12 pg12 6566 May 30 16:16 postgresql-2019-05-30_133324.csv	
-rw------- 1 pg12 pg12    0 May 31 00:00 postgresql-2019-05-31_000000.log	
-rw------- 1 pg12 pg12 4545 May 31 00:37 postgresql-2019-05-31_000000.csv	
[pg12@whf307 log]$ psql test	
psql (12beta1)	
Type "help" for help.	
test=# copy pg_log from '/soft/pg_data/log/postgresql-2019-05-31_000000.csv' with csv; 	
COPY 28


再次查看日志

test=# SELECT COUNT(*) FROM PG_LOG;	
 count 	
-------	
    60	
(1 row)	

	
test=# select log_time at time zone 'UTC' ,database_name,connection_from,query from pg_log where log_time>to_timestamp('2019-05-31 14:35:00','yyyy-mm-dd hh24:mi:ss');	
        timezone         | database_name | connection_from |                           query                           	
-------------------------+---------------+-----------------+-----------------------------------------------------------	
 2019-05-31 06:35:42.843 | test          | [local]         | 	
 2019-05-31 06:35:57.582 | test          | [local]         | 	
 2019-05-31 06:36:54.369 | test          | [local]         | selectt relfilenode from pg_class where relname='pg_log';	
 2019-05-31 06:36:58.002 | test          | [local]         | 	
 2019-05-31 06:37:00.192 | test          | [local]         | 	
 2019-05-31 06:37:11.651 |               | [local]         | 	
 2019-05-31 06:37:11.651 | test          | [local]         | 	
(7 rows)


可以看到记录数变成了60，之前的记录没有被覆盖，我们可以一直使用该表，可以用sql来查看sql，数据库，登录时间等等的所有日志。


查看日志起始结束时间

test=# select min(log_time)  at time zone 'UTC',max(log_time)  at time zone 'UTC' from pg_log;	
        timezone         |        timezone         	
-------------------------+-------------------------	
 2019-05-30 19:33:24.892 | 2019-05-31 06:37:11.651	
(1 row)


有了灵活的数据加载方式，让SQL处理很多问题更加简捷便利。


640?wx_fmt=jpeg


扩展阅读


  1. 《PostgreSQL 指南：内幕探索》之基础备份与时间点恢复（上）

  2. 《PostgreSQL 指南：内幕探索》之基础备份与时间点恢复（下）

  3. 史上最全PostgreSQL体系结构

  4. 2019全球PostgreSQL生态报告出炉，PG为何从RDBMS中脱颖而出？

  5. “王者对战”之 MySQL 8 vs PostgreSQL 10

  6. 大象起舞：用PostgreSQL解海盗分金问题

  7. 解读年度数据库PostgreSQL：如何巧妙地实现缓冲区管理器

  8. 美女DBA带你了解PostgreSQL用户及角色

  9. 从Oracle到PostgreSQL：最全控制文件

  10. PostgreSQL的常见参数和技巧

  11. PostgreSQL的日志文件和数据加载

  12. 从Oracle到PostgreSQL：一文掌握Checkpoint重要概念


640?wx_fmt=png

640?wx_fmt=png

640?wx_fmt=png

数据和云

ID：OraNews

如有收获，请划至底部，点击“在看”，谢谢！

资源下载

关注公众号：数据和云（OraNews）回复关键字获取

help，30万+下载的完整菜单栏

2019DTCC，数据库大会PPT

2018DTCC , 数据库大会PPT

2018DTC，2018 DTC 大会 PPT

ENMOBK，《Oracle性能优化与诊断案例》

DBALIFE，“DBA 的一天”海报

DBA04，DBA 手记4 电子书

122ARCH，Oracle 12.2体系结构图

2018OOW，Oracle OpenWorld 资料

产品推荐

云和恩墨Bethune Pro2 企业版，集监控、巡检、安全于一身，你的专属数据库实时监控和智能巡检平台，漂亮的不像实力派，你值得拥有！


640?wx_fmt=jpeg


云和恩墨zData一体机现已发布超融合版本和精简版，支持各种简化场景部署，零数据丢失备份一体机ZDBM也已发布，欢迎关注。


640?wx_fmt=jpeg

云和恩墨大讲堂 | 一个分享交流的地方

长按，识别二维码，加入万人交流社群


640?wx_fmt=jpeg

请备注：云和恩墨大讲堂

数据和云
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值