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 ----whether to redirect the log to a file, which is off by default ( the DB service needs to be restarted after this configuration modification )

The DB installation is complete and the service start process is as follows

[Email protected] ~]#PS-elf |grepPostgres0S Postgres2385     1  0   the   0-66829poll_s A: A?xx:xx:xx/opt/pg9.6/bin/postgres-d/mnt/Pgdata1S Postgres2387  2385  0   the   0-66829Ep_pol A: A?xx:xx:xxPostgres:checkpointer Process1S Postgres2388  2385  0   the   0-66829Ep_pol A: A?xx:xx:xxPostgres:writer Process1S Postgres2389  2385  0   the   0-66870Ep_pol A: A?xx:xx:xxPostgres:wal Writer Process1S Postgres2390  2385  0   the   0-66952Ep_pol A: A?xx:xx:xxpostgres:autovacuum Launcher Process1S Postgres2391  2385  0   the   0-29643Ep_pol A: A?xx:xx:xxPostgres:stats Collector Process

Modify this configuration to on, and restart the DB service, which prompts for the log redirection in ${pgdata}/pg_log during DB startup.

su ' /opt/pg9.6/bin/pg_ctl-d/mnt/pgdata Start ' Server Startinglog:  redirecting log output to logging collector Processhint:  in" Pg_log ".

At this point in the running process, as compared to the previous one, more than a logger process.

[Email protected] ~]#PS-elf |grepPostgres0S Postgres2448     1  0   the   0-66830poll_s A: -?xx:xx:xx/opt/pg9.6/bin/postgres-d/mnt/Pgdata1S Postgres2449  2448  0   the   0-29645Ep_pol A: -?xx:xx:xxPostgres: logger process 1S Postgres2451  2448  0   the   0-66830Ep_pol A: -?xx:xx:xxPostgres:checkpointer Process1S Postgres2452  2448  0   the   0-66830Ep_pol A: -?xx:xx:xxPostgres:writer Process1S Postgres2453  2448  0   the   0-66871Ep_pol A: -?xx:xx:xxPostgres:wal Writer Process1S Postgres2454  2448  0   the   0-66953Ep_pol A: -?xx:xx:xxpostgres:autovacuum Launcher Process1S Postgres2455  2448  0   the   0-29644Ep_pol A: -?xx:xx:xxPostgres:stats Collector Process

The following configuration modifications do not need to restart the service, just overload the configuration

su ' /opt/pg9.6/bin/pg_ctl-d/mnt/pgdata Reload '

2.log_directory = ' pg_log ' ----log file directory, default is ${pgdata} relative path, 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

mkdir -p/var/log/chown postgres:postgres/var/log/pg_log/chmod  /var/log/pg_log/

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

su ' /opt/pg9.6/bin/pg_ctl-d/mnt/pgdata Start ' Server Startinglog:  redirecting log output to logging collector Processhint:  in" /var/log/pg_log "  ls /var/log/pg_log/postgresql--18_130611.log

3.log_filename = ' postgresql-%y-%m-%d_%h%m%s.log ' ----log file naming form, using the 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.

log_min_duration_statement =-1 #-1 is disabled, 0 logs all statements and their durations, > 0 logs only statements running @ 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.

log_connections = off ----whether to log connection logs

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

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

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

Server Time zone settings

Cp

PostgreSQL Log Collection