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

女爷i 2021-10-31 15:52 104阅读 0赞

原文：[https://www.enmotech.com/web/detail/1/848/1.html ][https_www.enmotech.com_web_detail_1_848_1.html]

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

**运行日志参数**

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

**1.1 运行日志主要参数**

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

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> 参数 </td> 
 <td style="vertical-align:top;"> 可选值/说明 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> og_destination = 'csvlog'&nbsp; </td> 
 <td style="vertical-align:top;"> # stderr, csvlog, syslog, and eventlog ,csvlog requires logging_collector to be on 一般选择这个，可以将csv日志导入数据库中查看 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> logging_collector = on </td> 
 <td style="vertical-align:top;"> # Enable capturing of stderr and csvlog into log files 选scv格式的日志必须设置这个参数on，修改需要重启 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> log_directory = 'log' </td> 
 <td style="vertical-align:top;"> 日志输出目录 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log </td> 
 <td style="vertical-align:top;"> 日志输出目录 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> log_file_mode = 0600&nbsp; </td> 
 <td style="vertical-align:top;"> 日志文件名字格式 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> log_truncate_on_rotation = on </td> 
 <td style="vertical-align:top;"> # creation mode for log files&nbsp;&nbsp; 日志文件权限 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> log_rotation_age = 1d </td> 
 <td style="vertical-align:top;"> 设置重用日志 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> log_rotation_size = 10MB </td> 
 <td style="vertical-align:top;"> 多长时间重用日志 </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="vertical-align:top;"> log_min_messages = warning&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </td> 
 <td colspan="1" style="vertical-align:top;"> 日志达到多大重用 </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="vertical-align:top;"> log_min_duration_statement = 60 </td> 
 <td colspan="1" style="vertical-align:top;"> # debug5,debug4,debug3,debug2,debug1,info,notice,warning,error,log,fatal,panic </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="vertical-align:top;"> log_checkpoints = on </td> 
 <td colspan="1" style="vertical-align:top;"> 慢sql记录（超过多长时间的sql） </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="vertical-align:top;"> log_connections = on </td> 
 <td colspan="1" style="vertical-align:top;"> 记录checkpoint操作 </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="vertical-align:top;"> log_disconnections = on </td> 
 <td colspan="1" style="vertical-align:top;"> 记录会话断开操作 </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="vertical-align:top;"> log_duration = on </td> 
 <td colspan="1" style="vertical-align:top;"> 记录sql执行时间 </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="vertical-align:top;"> log_lock_waits = on&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </td> 
 <td colspan="1" style="vertical-align:top;"> # log lock waits &gt;= deadlock_timeout&nbsp;&nbsp; 记录时间长的阻塞 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> log_statement = 'ddl'&nbsp; </td> 
 <td style="vertical-align:top;"> # none, ddl, mod, all&nbsp;&nbsp;&nbsp; 记录ddl </td> 
 </tr> 
 </tbody> 
</table>

**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

再次查看日志

可以看到记录数变成了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处理很多问题更加简捷便利。

**想了解更多关于数据库、云技术的内容吗？**

**快来关注“数据和云"、"云和恩墨，"公众号及"云和恩墨"官方网站，我们期待大家一同学习与进步！**

![警告：ORA-00600 2252 错误正在SCN问题下不断爆发][ORA-00600 2252 _SCN]

**数据和云小程序”DBASK“在线问答，随时解惑，欢迎了解和关注！**

![20190815114222753.jpg][]

[https_www.enmotech.com_web_detail_1_848_1.html]: https://www.enmotech.com/web/detail/1/848/1.html
[ORA-00600 2252 _SCN]: /images/20211031/9bc0314ad5e64bcd96db8a8e99d5c5b1.png
[20190815114222753.jpg]: https://img-blog.csdnimg.cn/20190815114222753.jpg