Postgresql Log Collection

When the PG installation is complete, the log is not logged by default and the corresponding (${pgdata}/postgresql.conf) configuration must be modified to only describe the common log configuration.

1.logging_collector = ON/off----If the log is redirected to a file, the default is off (after this configuration modification, the DB service needs to be restarted)

Ps-ef |grep postgrespostgres26167 1 1  the: -?xx:xx:xx/usr/lib/postgresql/9.5/bin/postgres-d/var/lib/postgresql/9.5/main-c config_file=/etc/postgresql/9.5/main/Postgresql.confpostgres26169 26167 0  the: -?xx:xx:xxPostgres:checkpointer Process Postgres26170 26167 0  the: -?xx:xx:xxPostgres:writer Process Postgres26171 26167 0  the: -?xx:xx:xxpostgres:wal writer Process Postgres26172 26167 0  the: -?xx:xx:xxpostgres:autovacuum launcher Process Postgres26173 26167 0  the: -?xx:xx:xxpostgres:stats Collector Proces

After opening

Ps-ef |grep postgrespostgres25943 1 0  the: -?xx:xx:xx/usr/lib/postgresql/9.5/bin/postgres-d/var/lib/postgresql/9.5/main-c config_file=/etc/postgresql/9.5/main/Postgresql.confpostgres25944 25943 0  the: -?xx:xx:xxPostgres:logger Process Postgres25946 25943 0  the: -?xx:xx:xxPostgres:checkpointer Process Postgres25947 25943 0  the: -?xx:xx:xxPostgres:writer Process Postgres25948 25943 0  the: -?xx:xx:xxpostgres:wal writer Process Postgres25949 25943 0  the: -?xx:xx:xxpostgres:autovacuum launcher Process Postgres25950 25943 0  the: -?xx:xx:xxPostgres:stats Collector Process

2.log_directory = ' pg_log '----log file directory, the default is the relative path of PGDATA, that is, the relative path of PGDATA, that is, {pgdata}/pg_log, can also be changed to absolute path

The default is ${pgdata}/pg_log, which is the cluster directory, but the log files can be very numerous and it is recommended that you redirect the logs to a different directory or partition.

To modify this configuration to/var/log/pg_log, you must first create this directory and modify the permissions, such as

[Email protected] ~]# mkdir-p/var/log/~]# chown postgres:postgres/var/log/pg_log/    /log/pg_log//var

After restarting the DB service, the log will be redirected to/var/log/pg_log

3.log_filename = ' postgresql-%y-%m-%d_%h%m%s.log '----log file naming form, using default

4. Log_rotation_age = 1d----The lifetime of a single log file, by default 1 days, when the log file size does not reach log_rotation_size, only one log file is generated per day

5. log_rotation_size = 10MB----The size of a single log file, if the time does not exceed log_rotation_age, a log file can be up to 10M, otherwise it will be reborn as a log file.

6.log_truncate_on_rotation = off----When the log file already exists, if the configuration is off, the newly generated logs will be appended to the end of the file, and if on, the original log will be overwritten.

7.log_lock_waits = off----Controls whether a log message is generated when a session waits longer than deadlock_timeout to be locked. It is useful to determine whether a lock wait will affect performance, which is off by default.

8.log_statement = ' None ' # None, DDL, MOD, all----control which SQL statements are logged. None is logged, and the DDL records all data definition commands, such as create,alter, and drop statements. MoD records all DDL statements, plus data modification statements insert,update, and so on, all records all executed statements, and this configuration is set to all to track the SQL statements executed by the entire database.

9.log_duration = off----records how long each SQL statement takes to complete and sets this configuration to on to count which SQL statements take a long time.

10.log_min_duration_statement =-1 #-1 is disabled, 0 logs all statements and their durations, > 0 logs only statement s running at least this number of milliseconds

1 means not available, 0 will log all SQL statements and their time-consuming, >0 only those SQL statements that take longer than (or equal to) this value (MS). Individuals prefer to use this configuration to track SQL statements that are lengthy and may have performance issues. Although using Log_statement and log_duration can also be used to count SQL statements and time-consuming, SQL statements and time-consuming statistics may differ by many rows, or in different files, but Log_min_duration_ Statement will record SQL statements and time-consuming records on the same line for easier reading.

11.log_connections = off----Whether the connection log is logged

12.log_disconnections = off----Whether to log connection disconnect logs

13.log_line_prefix = '%m%p%u%d%r '----log output format (explained in%m,%p actual configuration file), can be set according to your own needs (can record time, user name, database name, client IP and port, easy to locate the problem)

14.log_timezone = ' Asia/shanghai '----log time zone, preferably with server settings in the same time zone, convenient for problem locating

Server Time zone settings

[Email protected] ~]# Cp-rf/usr/share/zoneinfo/asia/shanghai/etc/localtime

Postgresql Log Collection