ablog

不器用で落着きのない技術者のメモ

PostgreSQL の 接続・クエリ・切断のログは Process ID で紐付けできる

PostgreSQL で接続・クエリ・切断のログは Process ID で紐付けることができる。今回は Aurora PostgreSQL 互換 11.6 で確認した。
f:id:yohei-a:20200510182650p:plain

設定

  • パラメータグループで以下を設定する(今回は接続・接続のログのみ確認した)。
    • log_connections = 1
    • log_disconnections = 1
  • ログのエクスポートを有効化しておくと、CloudWatch Logs で確認できる。有効化していない場合は、マネジメントコンソールかもしくは AWS CLI でログをダウンロードして確認できる。

ログサンプル

  • CloudWatch > ロググループ > /aws/rds/cluster/aurora-postgres-116/postgresql > aurora-postgres-116-instance-1.0
2020-05-10 07:13:39 UTC:3.***.**.130(43334):[unknown]@[unknown]:[27199★]:LOG:  connection received: host=3.***.**.130 port=43334
2020-05-10 07:13:39 UTC:3.***.**.130(43334):awsuser@mydb:[27199★]:LOG:  connection authorized: user=awsuser database=mydb SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256, compression=off)
2020-05-10 07:13:39 UTC:3.***.**.130(43334):awsuser@mydb:[27199★]:LOG:  disconnection: session time: 0:00:00.029 user=awsuser database=mydb host=3.***.**.130 port=43334

参考

log_connections (boolean)
Causes each attempted connection to the server to be logged, as well as successful completion of client authentication. Only superusers can change this parameter at session start, and it cannot be changed at all within a session. The default is off.

Note
Some client programs, like psql, attempt to connect twice while determining if a password is required, so duplicate “connection received” messages do not necessarily indicate a problem.

log_disconnections (boolean)
Causes session terminations to be logged. The log output provides information similar to log_connections, plus the duration of the session. Only superusers can change this parameter at session start, and it cannot be changed at all within a session. The default is off.

PostgreSQL: Documentation: 11: 19.8. Error Reporting and Logging

log_line_prefix (string)
This is a printf-style string that is output at the beginning of each log line. % characters begin “escape sequences” that are replaced with status information as outlined below. Unrecognized escapes are ignored. Other characters are copied straight to the log line. Some escapes are only recognized by session processes, and will be treated as empty by background processes such as the main server process. Status information may be aligned either left or right by specifying a numeric literal after the % and before the option. A negative value will cause the status information to be padded on the right with spaces to give it a minimum width, whereas a positive value will pad on the left. Padding can be useful to aid human readability in log files. This parameter can only be set in the postgresql.conf file or on the server command line. The default is '%m [%p] ' which logs a time stamp and the process ID.

Escape Effect Session only
%a Application name yes
%u User name yes
%d Database name yes
%r Remote host name or IP address, and remote port yes
%h Remote host name or IP address yes
%p Process ID no
%t Time stamp without milliseconds no
%m Time stamp with milliseconds no
%n Time stamp with milliseconds (as a Unix epoch) no
%i Command tag: type of session's current command yes
%e SQLSTATE error code no
%c Session ID: see below no
%l Number of the log line for each session or process, starting at 1 no
%s Process start time stamp no
%v Virtual transaction ID (backendID/localXID) no
%x Transaction ID (0 if none is assigned) no
%q Produces no output, but tells non-session processes to stop at this point in the string; ignored by session processes no
%% Literal % no
PostgreSQL: Documentation: 11: 19.8. Error Reporting and Logging