Shulou(Shulou.com)06/01 Report--

This section describes the log-related configuration parameter log_XXX.

Overview

Query pg_settings on the database of the new initdb, and the parameters associated with log in PostgreSQL include:

[test@localhost] $psqlExpanded display is used automatically.psql (12.1) Type "help" for help. [local:/var/run/test]: 5000 test@testdb=# select category,name,setting from pg_settings where name like 'log%' order by category After canonicalize_qual () {OPEXPR: opno 1209: opfuncid 1209: opresulttype 16: opretset false: opcollid 0: inputcollid 100: args ({VAR: varno 4: varattno 1: vartype 25: vartypmod-1: varcollid 100: varlevelsup 0: varnoold 4: varoattno 1: location-1} {CONST: consttype 25: consttypmod-1: constcollid 100: constlen-1: constbyval false: constisnull false: location 62: constvalue 8 [3200 0108 111 103 37]}): location 57} RELOPTINFO (a): rows=5 width=96 baserestrictinfo: a.name ~ log% path list: FunctionScan (a) rows=5 cost=0.00..12.50 cheapest parameterized paths: FunctionScan (a) rows=5 cost=0.00..12.50 Cheapest startup path: FunctionScan (a) rows=5 cost=0.00..12.50 cheapest total path: FunctionScan (a) rows=5 cost=0.00..12.50 category | name | setting-+-- -+-- Reporting and Logging / What to Log | log_lock_waits | off Reporting and Logging / What to Log | log_checkpoints | off Reporting and Logging / What to Log | log_connections | off Reporting and Logging / What to Log | log_timezone | PRC Reporting and Logging / What to Log | log_temp_files |-1 Reporting and Logging / What to Log | log_disconnections | off Reporting and Logging / What to Log | log_duration | off Reporting and Logging / What to Log | log_error_verbosity | default Reporting and Logging / What to Log | log_statement | none Reporting and Logging / What To Log | log_replication_commands | off Reporting and Logging / What to Log | log_autovacuum_min_duration |-1 Reporting and Logging / What to Log | log_hostname | off Reporting and Logging / What to Log | log_line_prefix |% m [% p] Reporting and Logging / When to Log | log_min_duration_statement |-1 Reporting and Logging / When to Log | log_min_error_statement | error Reporting and Logging / When to Log | | log_min_messages | warning Reporting and Logging / When to Log | log_transaction_sample_rate | 0 Reporting and Logging / Where to Log | log_destination | stderr Reporting and Logging / Where to Log | log_filename | postgresql-%Y-%m-%d_%H%M%S.log Reporting and Logging / Where to Log | logging_collector | off Reporting and Logging / Where to Log | log_truncate_on_rotation | | | off Reporting and Logging / Where to Log | log_rotation_size | 10240 Reporting and Logging / Where to Log | log_file_mode | 0600 Reporting and Logging / Where to Log | log_rotation_age | 1440 Reporting and Logging / Where to Log | log_directory | log Statistics / Monitoring | log_statement_stats | off Statistics / Monitoring | log_planner_ | Stats | off Statistics / Monitoring | log_executor_stats | off Statistics / Monitoring | log_parser_stats | off (29 rows) [local:/var/run/test]: 5000 test@testdb=#

As you can see, the log is output directly on the console, and there are 29 parameters that start with log_. These parameters are parsed from the dimensions of where, when and what. This section is the first part, which introduces where.

Log:where

Log parameters related to the dimension "where" include log_directory, log_destination, log_filename, and log_file_mode.

Log_directory

The default value is log, which is a relative path, and the actual absolute path is $PGDATA/log. Of course, you can also use / to specify the absolute path for log storage.

Log_destination

Options include stderr, csvlog, syslog, eventlog

Stderr

The default value STDERR, standard output (standard error), the string before the actual output of the statement we execute the query configuration parameters is actually the output to STDERR. In the case of logging_collector=off, using this option outputs the log information to terminal.

Syslog

Operating system log daemon. There are several other parameters related to syslog:

Syslog_facility = 'LOCAL0', means "category of source"

Syslog_ident = 'identifier of the postgres', log

Syslog_sequence_numbers = on, whether to enable sequence numbering

Syslog_split_messages = on, whether to split the message

The following is the log output after select 1 / 0 is executed after being modified to syslog.

[root@localhost log] # uname-aLinux localhost.localdomain 3.10.0-693.el7.x86_64 # 1 SMP Tue Aug 22 21:09:27 UTC 2017 x86 "64 GNU/Linux [root@localhost log] # pwd/var/log [root@localhost log] # tail-n 20 messages...Dec 26 11:28:03 localhost postgres [5035]: [7-1] 2019-12-26 1111 28 messages...Dec 03.737 CST [5035] ERROR: division by zeroDec 26 11:28:03 localhost postgres [5035]: [7-2] 2019-12-26 11 Freight 28 purl 03.737 CST [5035] STATEMENT: select 1gamet0 ... [root@localhost log] #

Postgres is a string defined by syslog_ident and can be defined by the user. 5035 in [5035] is pid.

For example, change the parameter to 12.1:

# modify the parameter [test@localhost tmpdb] $pg_ctl reloadserver signaled [test@localhost tmpdb] $grep 'syslog_ident' postgresql.conf syslog_ident =' pg12.1' [test@localhost tmpdb] $# Log output Dec 26 11:34:32 localhost postgres [4800]: [7-1] 2019-12-26 1134 LOG 32.906 CST [4800] received SIGHUP Reloading configuration filesDec 26 11:34:32 localhost pg12.1 [4800]: [8-1] 2019-12-26 11 LOG: parameter "syslog_ident" changed to "pg12.1" Dec 26 11:34:37 localhost pg12.1 [5035]: [8-1] 2019-12-26 11 ERROR: division by zeroDec 26 11:34:37 localhost pg12.1 [5035]: [8-2] 20119-2019- 12-26 11-34 CST 37.163 [5035] STATEMENT: select 1-0 [root@localhost log] #

Of these, 4800 are postmaster processes and 5035 are backend processes

View process information through ps-ef

[test@localhost tmpdb] $ps-ef | grep postgres | grep 4800test 4800 1 0 11:24? 00:00:00 / appdb/pg12/pg12.1/bin/postgrestest 4802 4800 0 11:24? 00:00:00 postgres: checkpointer test 4803 4800 0 11:24? 00:00:00 postgres: background writer test 4804 4800 0 11:24? 00:00:00 postgres: walwriter test 4805 4800 0 11 24? 00:00:00 postgres: autovacuum launcher test 4806 4800 11:24? 00:00:00 postgres: stats collector test 4807 4800 0 11:24? 00:00:00 postgres: logical replication launcher test 5035 4800 0 11:27? 00:00:00 postgres: test testdb [local] idle [test@localhost tmpdb] $

Modify the configuration of rsyslog, set the output of the local0 log and restart the rsyslog service

[root@localhost ~] # grep 'local0' / etc/rsyslog.conf local0.* / var/log/postgres.log [root@localhost ~] #

Modify the PG log output to syslog to view the log output

[root@localhost ~] # ls-l / var/log/postg*-rw- 1 root root 1656 Dec 26 16:29 / var/log/postgres.log [root@localhost ~] # cat / var/log/postgres.logDec 26 16:29:04 localhost pg12.1 [23642]: [1-1] 2019-12-26 16 root root 294.668 CST [23642] LOG: starting PostgreSQL 12.1 on x86_64-pc-linux-gnu Compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bitDec 26 16:29:04 localhost pg12.1 [23642]: [2-1] 2019-12-26 16 64-bitDec 2904.668 CST [23642] LOG: listening on IPv6 address ":: 1", port 5000Dec 26 16:29:04 localhost pg12.1 [23642]: [3-1] 2019-12-26 16 Swiss 29race 04.668 CST [23642] LOG: listening on IPv4 address "127.0.0.1" Port 5000Dec 26 16:29:04 localhost pg12.1 [23642]: [4-1] 2019-12-26 16 LOG 04.669 CST [23642] LOG: listening on Unix socket "/ var/run/test/.s.PGSQL.5000" Dec 26 16:29:04 localhost pg12.1 [23642]: [5-1] 2019-12-26 16 Swiss 2904.683 CST [23642] LOG: redirecting log output to logging collector processDec 26 16:29:04 localhost pg12.1 [23642] : [5-2] 2019-12-26 16 Future log output will appear in directory 29 CST 04.683 CST [23642] HINT: Future log output will appear in directory "log" .Dec 26 16:29:04 localhost pg12.1 [23642]: [6-1] 2019-12-26 16 V 29 log 04.684 CST [23642] LOG: ending log output to stderrDec 26 16:29:04 localhost pg12.1 [23642]: [6-2] 2019-12-26 16 Switzerland 2940 CST [23642] HINT: Future log output will go to log destination "syslog" .Dec 26 16:29:04 localhost pg12.1 [23644]: [7-1] 2019-12-26 16 LOG 2904.688 CST [23644] LOG: database system was shut down at 2019-12-26 16:29:04 CSTDec 26 16:29:04 localhost pg12.1 [23642]: [7-1] 2019-12-26 16290lane 4.693 CST [23642] LOG: database system is ready to accept connectionsDec 26 16:29 38 localhost pg12.1 [23683]: [8-1] 2019-12-26 1615 division by zeroDec 2938.824 CST [23683] ERROR: division by zeroDec 26 16:29:38 localhost pg12.1 [23683]: [8-2] 2019-12-26 16Vera 2938.824 CST [23683] STATEMENT: select 1Comp0 [root@localhost ~] #

Syslog needs to rely on the implementation of the platform, more in-depth content will be introduced later.

Csvlog

Files in csv format. If you use this option, you need to set the logging_collector parameter to on.

Set this parameter to on,log_destination and stderr.

# logging_collector= off[ root @ localhost 5035] # ps-ef | grep 4800test 4800 10 11:24? 00:00:00 / appdb/pg12/pg12.1/bin/postgrestest 4802 4800 0 11:24? 00:00:00 postgres: checkpointer test 4803 4800 0 11:24? 00:00:00 postgres: background writer test 4804 4800 0 11:24? 00:00:00 postgres: walwriter test 4805 4800 0 11:24? 00:00:00 postgres: autovacuum launcher test 4806 4800 0 11:24? 00:00:00 postgres: stats collector test 4807 4800 0 11:24? 00:00:00 postgres: logical replication launcher test 5035 4800 0 11:27? 00:00:00 postgres: test testdb [local] idleroot 15986 5275 0 14:36 pts/5 00:00:00 grep-color=auto 4800 # logging_collector= on [root @ localhost 5035] # ps-ef | grep 16024test 16024 1 0 14:37? 00:00:00 / appdb/pg12/pg12.1/bin/postgrestest 16025 16024 0 14:37? 00:00:00 postgres: logger test 16027 16024 0 14:37? 00:00:00 postgres: checkpointer test 16028 16024 0 14:37? 00:00:00 postgres: background writer test 16029 16024 0 14:37-00:00:00 postgres: walwriter test 16030 16024 0 14:37-00:00:00 postgres: autovacuum launcher test 16031 16024 0 14:37-00:00:00 postgres: stats collector test 16032 16024 0 14:37-00:00:00 postgres: logical replication launcher root 16090 5275 0 14:38 pts/5 00:00:00 grep-- color=auto 16024

A new process logger appears (the source code of this part will be interpreted step by step later), and the log is output to the $PGDATA/log directory.

[test@localhost tmpdb] $cd log [test@localhost log] $lspostgresql-2019-12-26143730.log [test @ localhost log] $tail-n 20 postgresql-2019-12-26_143730.log 2019-12-26 1414 test@localhost log 37test@localhost log 30.317 CST [16026] LOG: database system was shut down at 2019-12-26 14:37:30 CST2019-12-26 1437VM 30.354 CST [16024] LOG: database system is ready to accept connections2019-12-26 14:41:27. 684 CST [16293] ERROR: division by zero2019-12-26 14 division by zero2019 41 division by zero2019 27.684 CST [16293] STATEMENT: select 1 Comp0 [test@localhost log] $

Next, we change the parameter to csvlog, and execute select 1 Universe 0

[test@localhost tmpdb] $ls-l. / log/total 12When RW-1 test test 807 Dec 26 14:45 postgresql-2019-12-26 colors 143730.logw-1 test test 676 Dec 26 14:45 postgresql-2019-12-26 years 144540.csvLash168 Dec 26 14:45 postgresql-2019-12-26 _ 144540.logtest @ localhost tmpdb] $tail-n 20. / log/postgresql- 2019-12-26_144540.csv2019-12-26 1414 40.781 CST , 16534 ending log output to stderr CST,0,LOG,00000 5e045714.4096 CST,0,LOG,00000, "ending log output to stderr", "Future log output will go to log destination" and "csvlog". "," 2019-12-26 14 Future log output will go to log destination CST,16536,5e045714.4098,1,2019-12-26 14:45:40 CST,0,LOG,00000, "database system was shut down at 2019-12-26 14:45:40 CST" , "2019-12-26 14 database system is ready to accept connections 45 CST,16534,5e045714.4096,2,2019 40.808 CST,16534,5e045714.4096,2,2019-12-26 14:45:40 CST,0,LOG,00000," database system is ready to accept connections "," 2019-12-26 14 14 14 45 45 CST 44.258 CST, "test", "testdb", 16546, "[local]", 5e045717.40a2, "SELECT", 2019-12-26 14:45:43 CST,3/2,0,ERROR,22012, "division by zero" , "select 1 Compact 0 " "psql" [test@localhost tmpdb] $tail-n 20. / log/postgresql-2019-12-26_144540.log2019-12-26 1414 26_144540.log2019 45 psql 40.781 CST [16534] LOG: ending log output to stderr2019-12-26 1415 45 csvlog 40.781 CST [16534] HINT: Future log output will go to log destination "csvlog". [test @ localhost tmpdb] $tail-n 100. / log/postgresql-2019-12-26_144540.log2019-12-26 1445 csvlog 40.781 CST [16534] LOG: ending log output to stderr2019-12-26 14 CST 45 CST [16534] HINT: Future log output will go to log destination "csvlog". [test @ localhost tmpdb] $

As you can see, after being modified to csvlog, the output log format is a comma-separated csv file format, which can be easily imported into the database (Oracle, PG, MySQL, etc.).

Eventlog

Event log for the Windows platform.

Logging_collector

See the above explanation, to put it simply, if set to on, PG will start the logger process, and the log will be sent to the syslogger process of pg, otherwise it will not.

Log_filename

The default value is' postgresql-%Y-%m-%d_%H%M%S.log', where% Y represents year,% m represents month,% d represents day,% H represents hours,% M represents minutes, and% S represents seconds.

The available matches can be found in strftime, and if the default value is used, a log file, if any, is generated every other second.

% a

The abbreviated name of the day of the week according to the current locale. (Calculated from tm_wday.)

% A

The full name of the day of the week according to the current locale. (Calculated from tm_wday.)

% b

The abbreviated month name according to the current locale. (Calculated from tm_mon.)

% B

The full month name according to the current locale. (Calculated from tm_mon.)

% c

The preferred date and time representation for the current locale.

% C

The century number (year/100) as a 2-digit integer. (SU) (Calculated from tm_year.)

% d

The day of the month as a decimal number (range 01 to 31). (Calculated from tm_mday.)

% D

Equivalent to% m/%d/%y. (Yecch-for Americans only. Americans should note that in other countries% d/%m/%y is rather common. This means that in international context this format is ambiguous and should not be used.) (SU)

% e

Like% d, the day of the month as a decimal number, but a leading zero is replaced by a space. (SU) (Calculated from tm_mday.)

% E

Modifier: use alternative format, see below. (SU)

% F

Equivalent to Y-%m-%d (the ISO 8601 date format). (C99)

% G

The ISO 8601 week-based year (see NOTES) with century as a decimal number. The 4-digit year corresponding to the ISO week number (see% V). This has the same format and value as% Y, except that if the ISO week number belongs to the previous or next year, that year is used instead. TZ) (Calculated from tm_year, tm_yday, and tm_wday.)

% g

Like% G, but without century, that is, with a 2-digit year (00-99). TZ) (Calculated from tm_year, tm_yday, and tm_wday.)

% h

Equivalent to% b. (SU)

% H

The hour as a decimal number using a 24-hour clock (range 00 to 23). (Calculated from tm_hour.)

% I

The hour as a decimal number using a 12-hour clock (range 01 to 12). (Calculated from tm_hour.)

% j

The day of the year as a decimal number (range 001 to 366). (Calculated from tm_yday.)

% k

The hour (24-hour clock) as a decimal number (range 0 to 23); single digits are preceded by a blank. (See also% H.) (Calculated from tm_hour.) (TZ)

% l

The hour (12-hour clock) as a decimal number (range 1 to 12); single digits are preceded by a blank. (See also% I.) (Calculated from tm_hour.) (TZ)

% m

The month as a decimal number (range 01 to 12). (Calculated from tm_mon.)

% M

The minute as a decimal number (range 00 to 59). (Calculated from tm_min.)

% n

A newline character. (SU)

% O

Modifier: use alternative format, see below. (SU)

% p

Either "AM" or "PM" according to the given time value, or the corresponding strings for the current locale. Noon is treated as "PM" and midnight as "AM". (Calculated from tm_hour.)

% P

Like% p but in lowercase: "am" or "pm" or a corresponding string for the current locale. (Calculated from tm_hour.) (GNU)

% r

The time in a.m. Or p.m. Notation. In the POSIX locale this is equivalent to% I:%M:%S% p. (SU)

% R

The time in 24-hour notation (% HRV% M). (SU) For a version including the seconds, see% T below.

% s

The number of seconds since the Epoch, 1970-01-01 00:00:00 + 0000 (UTC). (TZ) (Calculated from mktime (tm).)

% S

The second as a decimal number (range 00 to 60). (The range is up to 60 to allow for occasional leap seconds.) (Calculated from tm_sec.)

% t

A tab character. (SU)

% T

The time in 24-hour notation (% H:%M:%S). (SU)

% u

The day of the week as a decimal, range 1 to 7, Monday being 1. See also% w. (Calculated from tm_wday.) (SU)

% U

The week number of the current year as a decimal number, range 00 to 53, starting with the first Sunday as the first day of week 01. See also% V and% W. (Calculated from tm_yday and tm_wday.)

% V

The ISO 8601 week number (see NOTES) of the current year as a decimal number, range 01 to 53, where week 1 is the first week that has at least 4 days in the new year. See also% U and% W. (Calculated from tm_year, tm_yday, and tm_wday.) (SU)

% w

The day of the week as a decimal, range 0 to 6, Sunday being 0. See also% u. (Calculated from tm_wday.)

% W

The week number of the current year as a decimal number, range 00 to 53, starting with the first Monday as the first day of week 01. (Calculated from tm_yday and tm_wday.)

% x

The preferred date representation for the current locale without the time.

% X

The preferred time representation for the current locale without the date.

% y

The year as a decimal number without a century (range 00 to 99). (Calculated from tm_year)

% Y

The year as a decimal number including the century. (Calculated from tm_year)

% z

The + hhmm or-hhmm numeric timezone (that is, the hour and minute offset from UTC). (SU)

% Z

The timezone name or abbreviation.

% +

The date and time in date (1) format. (TZ) (Not supported in glibc2.)

%%

A literal'% 'character.

Log_file_mode

The mode of the log file, which is used to control the access to the log file. Default is 0600.

[test@localhost tmpdb] $cd log [test@localhost log] $lltotal 16murr RW-1 test test 807 Dec 26 14:45 postgresql-2019-12-26mm 143730.log RW-1 test test 1584 Dec 26 14:52 postgresql-2019-12-26" 144540.csv Dec RW-1 test test RW-0.cs168 Dec 26 14:45 postgresql-2019-12-26mm 144540.log RW-1 test test 309 Dec 26 14:52 postgresql-2019-12-26_145238.log

Set to 0777, restart, file is 666,

[test@localhost tmpdb] $ll. / logtotal 20Raq RW-1 test test 807 Dec 26 14:45 postgresql-2019-12-26 test test 143730.log RW-1 test test 1584 Dec 26 14:52 postgresql-2019-12-26 "144540.csvRose RW-1 test test 168csv Dec 26 14:45 postgresql-2019-12-26mm 144540.logRW-1 test test 807 Dec 26 14:57 postgresql-2019 -12-26 test test 145238.logMurray RWMurRW-1 test test 309 Dec 26 14:58 postgresql-2019-12-26145757.log [test @ localhost tmpdb] $

Log_truncate_on_rotation

The options are on and off, and the default is off.

If set to on, if the file exists in rotate, the contents of the file will be truncated, otherwise information will be appended to the existing file.

There are two modes for rotation,PG: one is size-based, the other is time-based, and the corresponding parameters are log_rotation_size and log_rotation_age.

Log_rotation_size

The unit is KB and the default value is 10MB. This parameter means that when the size exceeds this setting, a new log file is generated.

Log_rotation_age

Unit is minute, default is 1D, that is, 1440 minutes. This parameter means that after this period of time, a new log file is generated. If configured for 5 minutes, a new log file is generated after 5 minutes, and the file name is controlled by log_filename.

Because the file name is controlled by log_filename, what happens when the size or time exceeds the setting, but the file name is set to the same (such as set to postgresql-%Y-%m-%d.log)?

[test@localhost tmpdb] $grep 'log_filename' postgresql.conf log_filename =' postgresql-%Y-%m-%d.log' # log filename pattern, [test@localhost tmpdb] $grep 'rotation' postgresql.conf log_truncate_on_rotation = on # If on, an existing log file with the # time-driven rotation, not on restarts # or size-driven rotation. Default islog_rotation_age = 5 # Automatic rotation of logfiles willlog_rotation_size = 10MB # Automatic rotation of logfiles will [test@localhost tmpdb] $[test@localhost tmpdb] $rm-rf. / log [test@localhost tmpdb] $pg_ctl restartwaiting for server to shut down.... Doneserver stoppedwaiting for server to start....2019-12-26 15 LOG 10 starting PostgreSQL 26.812 CST [18042] LOG: starting PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit2019-12-26 15 LOG 10 starting PostgreSQL 26.812 CST [18042] LOG: listening on IPv6 address ":: 1" Port 50002019-12-26 15 listening on IPv4 address 10 listening on IPv4 address 26.812 CST [18042] LOG: listening on IPv4 address "127.0.0.1", port 50002019-12-26 15 listening on IPv4 address 10 listening on IPv4 address 26.814 CST [18042] LOG: listening on Unix socket "/ var/run/test/.s.PGSQL.5000" 2019-12-26 15 redirecting log output to logging collector process2019 10 CST [18042] LOG: redirecting log output to logging collector process2019-12-26 15 redirecting log output to logging collector process2019 1026.823 CST: Future log output will appear in directory "log". Doneserver started [test@localhost tmpdb] $ll. / log/total 4murRW-1 test test 188 Dec 26 15:11 postgresql-2019-12-26.log [test@localhost tmpdb] $[test@localhost tmpdb] $ll. / log/total 4murrw1 test test 188 Dec 26 15:11 postgresql-2019-12-26.log [test@localhost tmpdb] $watch-N1 ls-l. / log [test@localhost tmpdb] $ll. / log/total 4murrw1 test test 551 Dec 26 15:12 postgresql-2019-12-26.log [test@localhost tmpdb] $

Because the file already exists, it will not be truncated. By the same token, if the file already exists, it will not be truncated even if the time is up.

In engineering practice, by setting log_filename to Sunday or monthly, you can ensure that there is only one file a day from Monday to Sunday or from the 1st to the 31, so as to avoid too many files.

references

Understanding postgresql.conf: log

Error Reporting and Logging

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.