Updated on 2024-05-07 GMT+08:00

Debugging

To control the output of log files and better understand the operating status of the database, modify specific configuration parameters in the postgresql.conf file in the instance data directory.

Table 1 describes the adjustable configuration parameters.

Table 1 Configuration parameters

Parameter

Description

Value Range

Remarks

client_min_messages

Level of messages to be sent to clients.

  • DEBUG5
  • DEBUG4
  • DEBUG3
  • DEBUG2
  • DEBUG1
  • LOG
  • NOTICE
  • WARNING
  • ERROR
  • FATAL
  • PANIC

Default value: NOTICE

Messages of the set level or lower will be sent to clients. The lower the level is, the fewer the messages will be sent.

log_min_messages

Level of messages to be recorded in server logs.

  • DEBUG5
  • DEBUG4
  • DEBUG3
  • DEBUG2
  • DEBUG1
  • INFO
  • NOTICE
  • WARNING
  • ERROR
  • LOG
  • FATAL
  • PANIC

Default value: WARNING

Messages higher than the set level will be recorded in logs. The higher the level is, the fewer the server logs will be recorded.

log_min_error_statement

Level of SQL error statements to be recorded in server logs.

  • DEBUG5
  • DEBUG4
  • DEBUG3
  • DEBUG2
  • DEBUG1
  • INFO
  • NOTICE
  • WARNING
  • ERROR
  • FATAL
  • PANIC

Default value: ERROR

SQL error statements of the set level or higher will be recorded in server logs.

Only a system administrator is allowed to modify this parameter.

log_min_duration_statement

Minimum execution duration of a statement. If the execution duration of a statement is equal to or longer than the set milliseconds, the statement and its duration will be recorded in logs. Enabling this function can help you track the query attempts to be optimized.

INT type

Default value: -1

Unit: millisecond

The value –1 indicates that the function is disabled.

Only a system administrator is allowed to modify this parameter.

log_connections/log_disconnections

Specifies whether to record a server log message when each session is connected or disconnected.

  • on: The system records a log server when each session is connected or disconnected.
  • off: The system does not record a log server when each session is connected or disconnected.

Default value: off

-

log_duration

Specifies whether to record the duration of each executed statement.

  • on: The system records the duration of each executed statement.
  • off: The system does not record the duration of each executed statement.

Default value: off

Only a system administrator is allowed to modify this parameter.

log_statement

SQL statements to be recorded in logs.

  • none: The system does not record any SQL statements.
  • ddl: The system records data definition statements.
  • mod: The system records data definition statements and data operation statements.
  • all: The system records all statements.

Default value: none

Only a system administrator is allowed to modify this parameter.

log_hostname

Specifies whether to record host names.

  • on: The system records host names.
  • off: The system does not record host names.

Default value: off

By default, connection logs only record the IP addresses of connected hosts. With this function, the host names will also be recorded.

This parameter has an impact on viewing audit results, PG_STAT_ACTIVITY, and the GUC parameter log_line_prefix.

Table 2 describes the preceding parameter levels.

Table 2 Description of log level parameters

Level

Description

DEBUG[1-5]

Provides information that can be used by developers. Level 1 is the lowest level whereas level 5 is the highest level.

INFO

Provides information about users' hidden requests, for example, information about the VACUUM VERBOSE process.

NOTICE

Provides information that may be important to users, for example, truncations of long identifiers or indexes created as a part of a primary key.

WARNING

Provides warning information for users, for example, COMMIT out of transaction blocks.

ERROR

Reports an error that causes a command to terminate.

LOG

Reports information that administrators may be interested in, for example, the activity levels of check points.

FATAL

Reports the reason that causes a session to terminate.

PANIC

Reports the reason that causes all sessions to terminate.