Small knowledge, big challenge! This article is participating in the “Essential Tips for Programmers” creation campaign. This article also participated in the “Digitalstar Project” to win the creation package and challenge the creation incentive money. We studied and learned how to set the postgresql.conf parameter with reference to ali Cloud best practices and the official PostgresQL manual. (Only the parameters that need to be modified are listed below. Those whose default values are appropriate are not listed.)

Log Settings

Log_destination = 'csvlog' logging_collector = on log_directory = '/app/pg12.4/logs' log_filename = 'postgresQL -%a.log' log_file_mode = 0600 log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 1GB log_min_duration_statement = 5s log_checkpoints = on log_connections = on log_disconnections = on log_error_verbosity = verbose log_line_prefix = '%m [%p] %q %u %d %a %r %e ' log_statement = 'ddl' log_timezone = 'PRC'Copy the code

Log_destination: there are three output methods, stderr,csvlog,syslog; Eventlog is also supported on Windows. The default is stderr, and logging_collector must be enabled if csvlog is used. You can also use csvlog and stderr together, and log in both formats. Logging_collector: Log collector, which is a background process that captures log messages sent to stderr and redirects them to log files. The default value is OFF. Log_directory: Log save path. When logging_collector is enabled, this parameter determines the directory in which log files will be created. Log_filename: indicates the log name format. The default value is PostgresQL -%Y-%m-%d_%H% m %S.log. Log_file_mode: The default permission is 0600, which means that only the server owner can read or write log files. The other common setting is 0640, which allows the owner’s group members to read the file. Note, however, that you need to modify log_directory to store files somewhere outside the clustered data directory to take advantage of this setting. In any case, it is unwise to make log files readable by anyone, as they may contain sensitive data. Log_truncate_on_rotation: When logging_collector is enabled, this parameter causes PostgreSQL to truncate (overwrite rather than append) any existing log files with the same name. However, truncation only occurs when a new file is opened due to a time-based rotation, not for a dimension-based rotation. If closed, previously existing files are appended in all cases. Log_rotation_age: When logging_collector is enabled, this parameter determines the maximum amount of time a single log file can be used, after which a new log file will be created. The default is 24 hours. Setting this parameter to zero disables the creation of new log files based on time. Log_rotation_size: This parameter determines the maximum size of an individual log file when logging_collector is enabled. When this amount of data is sent to a log file, a new log file is created, with a default value of 10M. Setting it to zero disables the creation of new log files based on size. Log_min_duration_statement: Indicates the time threshold for recording slow SQL statements. SQL statements that exceed the time threshold are recorded in logs. The default value is -1. Log_checkpoints: Controls whether checkpoints and restarts are recorded in server logs. Parameter Description Changing the parameter Parameter Log_checkpoints: Controls whether checkpoints and restarts are recorded in server logs, including data written to the buffer and the time it takes to write them. Log_connections: Controls whether connection information is logged, attempts to connect to the server are logged, successful completion of client authentication is logged, it cannot be changed during the session, default is off. Log_disconnections: Controls whether the termination of a session is recorded. The log output provides information similar to log_connections, but with the addition of the duration of the session, which cannot be changed in the session and defaults to off. Log_error_verbosity: Valid values are TERSE, DEFAULT, and VERBOSE. TERSE excludes logged DETAIL, HINT, QUERY, and CONTEXT error information. VERBOSE output includes the SQLSTATE error code and the source file name, function name, and line number that generated the error. The DEFAULT value is DEFAULT. Log_line_prefix: sets the probability of what is in the log. The default value is ‘%m [%p] ‘and records the timestamp and process ID. Log_statement: Controls which SQL statements are recorded. Valid values are None (off), DDL, mod, and all (all statements). Mod records all DDL statements, plus data modification statements such as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE, EXECUTE, and EXPLAIN ANALYZE are also logged if they contain the appropriate type of command. The default is None. Log_timezone: Sets the timezone for writing time stamps to server logs. The default value is GMT.

Log_filename Format description

%A: Abbreviation of the word of the week: If Monday, return Mon %A: full abbreviation of the word of the week: if Monday, return Monday %B: Abbreviation of the word of the month: if January, return Jan %b: citation word abbreviation of the month: if January, return Mon % C: Returns a string representation of datetime, such as 03/08/15 23:01:26 %d: returns the day of the month in which the current time is. %f: microsecond representation: range: [0,999999] %H: indicates the current hour in 24-hour system %I: indicates the current hour in 12-hour system %j: indicates the range of the current day in the current year [001,366] %m: indicates the range of the month [0,12] %m: indicates the range of the returned minutes [0,59] %P: indicates the range of the returned minutes. AM or PM %S: returns the range of seconds [0,61]… %U: indicates the week in which the current week is returned. Sunday is the first day. %W: indicates the number of days in the current week. 23:22:08 %Y: two-digit year 15% Y: four-digit year 2015 %Z: interval from UTC time (empty string if local time) %z: Time zone name (empty string if local time)

Log_line_prefix format description

%a: application name %u: user name % D: database name % R: remote host name or IP address, and remote port % H: remote host name or IP address % B: back-end type %p: process ID % T: no millisecond timestamp %m: with millisecond timestamp %n: % I: command label: type of the current command %e: SQLSTATE Error code % C: session ID: see below % L: log line number for each session or process, starting from 1 %s: timestamp at which the process started %v: Virtual transaction ID (backendID/localXID) %x: transaction ID (0 if not assigned) %q: does not produce output, but tells non-session processes to stop at this point in the string; Session process ignores %% : plain text %