PostgreSQLの設定ファイルで、実行されたSQLをログに出力するよう設定

実行されたSQLのログへの出力は、デフォルトでは無効になっています。PostgreSQLのサービスを起動時にSQLのログ出力を有効にするのは、PostgreSQLの設定ファイル（ここでの説明に使用しているCentOSでは「/var/lib/pgsql/9.4/data/postgresql.conf」）で指定可能になっています。

実行されたSQLのログ出力を有効にするには、以下のような設定を行います。

#------------------------------------------------------------------------------ # ERROR REPORTING AND LOGGING #------------------------------------------------------------------------------ ：（略） # - What to Log - ：（略） #log_statement = 'none' # none, ddl, mod, all ↓変更 log_statement = 'all' # none, ddl, mod, all

• 「log_statement」に「all」と指定すると、実行された全てのSQLがログに出力されるようになります。

ログファイルの保存先は指定可能になっており、デフォルトではデータディレクトリ（ここでは「/var/lib/pgsql/9.4/data/」）配下のディレクトリ「pg_log」に、ファイル名が「postgresql-曜日を表すアルファベット3文字.log」で保存されるようになっています。

ログファイルの保存先に関するデフォルトの設定ファイルの内容は、以下のようになっています。

#------------------------------------------------------------------------------ # ERROR REPORTING AND LOGGING #------------------------------------------------------------------------------ # - Where to Log - log_destination = 'stderr' # Valid values are combinations of # stderr, csvlog, syslog, and eventlog, # depending on platform. csvlog # requires logging_collector to be on. # This is used when logging to stderr: logging_collector = on # Enable capturing of stderr and csvlog # into log files. Required to be on for # csvlogs. # (change requires restart) # These are only used if logging_collector is on: log_directory = 'pg_log' # directory where log files are written, # can be absolute or relative to PGDATA log_filename = 'postgresql-%a.log' # log file name pattern, # can include strftime() escapes 

• 「log_destination」に「stderr」と指定し、「logging_collector」に「on」と指定すると、実行されたSQLのログが指定されたディレクトリのファイルに出力されるようになります。

• 「log_directory」に、ログファイルを保存するディレクトリを指定します。

  絶対パス、もしくは相対パスで指定することができ、相対パスで指定した場合は、データディレクトリの配下のディレクトリとなります。

• 「log_filename」に、ログファイルの名前を指定します。

  ファイル名にはstrftimeのパターンを使用することができます。

  デフォルトで指定されている「%a」は曜日を表すアルファベット3文字を示し、「postgresql-Mon.log」、「postgresql-Tue.log」のようなファイル名になります。

また、ログファイルのローテーションに関する設定もあり、デフォルトの設定ファイルの内容は、以下のようになっています。

#------------------------------------------------------------------------------ # ERROR REPORTING AND LOGGING #------------------------------------------------------------------------------ # - Where to Log - ：（略） log_truncate_on_rotation = on # If on, an existing log file with the # same name as the new log file will be # truncated rather than appended to. # But such truncation only occurs on # time-driven rotation, not on restarts # or size-driven rotation. Default is # off, meaning append to existing files # in all cases. log_rotation_age = 1d # Automatic rotation of logfiles will # happen after that time. 0 disables. log_rotation_size = 0 # Automatic rotation of logfiles will # happen after that much log output. # 0 disables.

• 「log_rotation_age」に指定している時間（分単位）を経過すると、「log_filename」に指定しているファイル名にログファイルが切り替わります。

  デフォルトで指定されている「1d」のように、1日を指定することもできます。

  「0」を指定すると、時間の経過によるログファイルのローテーションは行われなくなります。

• 「log_truncate_on_rotation」に「on」と指定すると、ログファイルのローテーション時に同じファイル名のものが既に存在していると上書きされるようになります。

  ただし、この上書きはPostgreSQLのサービスが起動中にローテーションが発生した場合に行われ、サービスの起動時に同じファイル名のものが存在した場合は、追記されるようになります。

  「log_truncate_on_rotation」に「off」と指定すると、この上書きは行われなくなります。

• 「log_rotation_size」に指定しているファイルサイズ（キロバイト単位）を超過すると、「log_filename」に指定しているファイル名にログファイルが切り替わります。

  「0」を指定すると、ファイルサイズの超過によるログファイルのローテーションは行われなくなります。

  ファイルサイズの超過によるログファイルのローテーションは、「log_truncate_on_rotation」の指定による上書きの対象にはなりません。

PostgreSQLのサービスを起動後に、「SELECT * FROM tbl01,」を実行した場合のログファイルの内容は、以下のようになります。

<, 2016-01-27 23:20:53.955 JST >,LOG: データベースシステムは 2016-01-27 23:20:34 JST にシャットダウンしました <, 2016-01-27 23:20:54.024 JST >,LOG: MultiXact member wraparound protections are now enabled <, 2016-01-27 23:20:54.026 JST >,LOG: データベースシステムの接続受付準備が整いました。 <, 2016-01-27 23:20:54.027 JST >,LOG: 自動バキュームランチャプロセス <, 2016-01-27 23:20:59.982 JST >,LOG: 文: SELECT * FROM tbl01,

SQLが実行されたデータベースの名前をログに出力させる

ログに出力されるSQLの内容は、上記のようにデフォルトの設定では、どのデータベースで実行されたSQLなのか判別することができません。

SQLが実行されたデータベースの名前をログに出力させるには、以下のような設定を行います。

#------------------------------------------------------------------------------ # ERROR REPORTING AND LOGGING #------------------------------------------------------------------------------ ：（略） # - What to Log - ：（略） log_line_prefix = '<, %m >,' # special values: # %a = application name # %u = user name # %d = database name # %r = remote host and port # %h = remote host # %p = process ID # %t = timestamp without milliseconds # %m = timestamp with milliseconds # %i = command tag # %e = SQL state # %c = session ID # %l = session line number # %s = session start timestamp # %v = virtual transaction ID # %x = transaction ID (0 if none) # %q = stop here in non-session # processes # %% = '%' # e.g. '<,%u%%%d>, ' ↓変更 log_line_prefix = '<, %m >,[%d]'

• 「log_line_prefix」に、ログの各行の先頭に出力する文字列を指定します。

  「%m」と指定した箇所には、ログを出力した時刻がミリ秒単位のタイムスタンプ付きで出力されます。

  「%d」と指定した箇所には、接続しているデータベースの名前が出力されます。

PostgreSQLのサービスを起動後に、データベース「db01」で「SELECT * FROM tbl01,」を実行し、データベース「db02」で「SELECT * FROM tbl11,」を実行した場合のログファイルの内容は、以下のようになります。

<, 2016-02-08 23:42:23.134 JST >,[]LOG: データベースシステムは 2016-02-08 23:42:06 JST にシャットダウンしました <, 2016-02-08 23:42:23.214 JST >,[]LOG: MultiXact member wraparound protections are now enabled <, 2016-02-08 23:42:23.217 JST >,[]LOG: データベースシステムの接続受付準備が整いました。 <, 2016-02-08 23:42:23.217 JST >,[]LOG: 自動バキュームランチャプロセス <, 2016-02-08 23:42:41.772 JST >,[db01]LOG: 文: SELECT * FROM tbl01, <, 2016-02-08 23:42:54.332 JST >,[db02]LOG: 文: SELECT * FROM tbl11,

設定ファイルの値をSQLで変更する

設定ファイルの値は、以下のようなSQLで変更することもできます。

postgres=# ALTER SYSTEM SET log_statement = 'all',

• 「ALTER SYSTEM」文を使用して、「SET」に続けて変更したい値を「パラメーター名 = 値」または「パラメーター名 TO 値」の形で指定します。

  ここでは「log_statement」に「all」と指定するようにしています。

  「ALTER SYSTEM」文を使用できるのは、スーパーユーザーのみになります。

ALTER SYSTEM文で指定した内容は、「postgresql.conf」ではなく、「postgresql.auto.conf」（ここでの説明に使用しているCentOSでは「/var/lib/pgsql/9.4/data/postgresql.auto.conf」）に保存されるようになっています。

「postgresql.auto.conf」に設定されている内容は、PostgreSQLのサービスの起動時に「postgresql.conf」より優先して使用されるようになります。

上記のALTER SYSTEM文を実行した際の「postgresql.auto.conf」の内容は、以下のようになります。

# Do not edit this file manually! # It will be overwritten by ALTER SYSTEM command. log_statement = 'all'

設定ファイルの変更内容をPostgreSQLのサービスの起動中に反映させる

設定ファイルの変更内容をPostgreSQLのサービスの起動中に反映させるには、以下のSQLを実行します。

postgres=# SELECT pg_reload_conf(), pg_reload_conf ---------------- t

• 「pg_reload_conf()」関数を実行すると、設定ファイルの再読み込みが行われます。

  関数の実行が成功するとtrue、失敗するとfalseが返ります。

設定ファイルの再読み込みは、コマンドラインで以下のコマンドを実行することで行うこともできます。

# su - postgres

$ /usr/pgsql-9.4/bin/pg_ctl reload

• 「pg_ctl reload」コマンドで設定ファイルの再読み込みを行うには、「su」コマンドでPostgreSQLのサービスの実行ユーザーである「postgres」に切り替える必要があります。

SQLで現在のログ出力の設定を確認

現在の接続で使用されているパラメーターの値を確認するには、以下のようなSQLを実行します。

postgres=# SHOW log_statement, log_statement --------------- all

• 「SHOW」に続けて、値を確認するパラメーターの名前を指定します。

  ここではパラメーター「log_statement」の値を確認するようにしています。

以下のようなSQLで、より詳細な設定の情報を確認することもできます。

postgres=# SELECT * FROM pg_settings WHERE name = 'log_statement', name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline ---------------+---------+------+-------------------------------------+-------------------------------------+------------+-----------+---------+--------------------+---------+---------+--------------------+----------+-----------+-----------------------------------------+------------ log_statement | all | | Reporting and Logging / What to Log | Sets the type of statements logged. | | superuser | enum | configuration file | | | {none,ddl,mod,all} | none | all | /var/lib/pgsql/9.4/data/postgresql.conf | 440

• 「pg_settings」は、パラメーターの情報を取得するビューになります。

• WHERE句の「name」で、設定の情報を確認するパラメーターの名前を指定します。

  ここではパラメーター「log_statement」の設定の情報を確認するようにしています。

• 「sourcefile」で設定値を参照している設定ファイルを確認することができます。

  ALTER SYSTEM文で設定を行っていない場合は、元の設定ファイル「/var/lib/pgsql/9.4/data/postgresql.conf」が参照されるようになっています。

ALTER SYSTEM文で設定を行っている場合は、以下のようになります。

postgres=# SELECT * FROM pg_settings WHERE name = 'log_statement', name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline ---------------+---------+------+-------------------------------------+-------------------------------------+------------+-----------+---------+--------------------+---------+---------+--------------------+----------+-----------+----------------------------------------------+------------ log_statement | all | | Reporting and Logging / What to Log | Sets the type of statements logged. | | superuser | enum | configuration file | | | {none,ddl,mod,all} | none | all | /var/lib/pgsql/9.4/data/postgresql.auto.conf | 3

• ALTER SYSTEM文で設定を行っている場合は、変更用の設定ファイル「/var/lib/pgsql/9.4/data/postgresql.auto.conf」が参照されるようになっています。

ALTER SYSTEM文で設定した内容の削除

ALTER SYSTEM文で「postgresql.auto.conf」に設定した内容を削除するには、以下のようなSQLを実行します。

postgres=# ALTER SYSTEM RESET log_statement,

または

postgres=# ALTER SYSTEM SET log_statement = DEFAULT,

• 「ALTER SYSTEM」文を使用して、「RESET」に続けて削除したいパラメーター名を指定、または「SET」に続けて削除したいパラメーターを「パラメーター名 = DEFAULT」または「パラメーター名 TO DEFAULT」の形で指定します。

  ここでは「log_statement」の設定を削除するようにしています。

ALTER SYSTEM文で複数のパラメーターを「postgresql.auto.conf」に設定している場合に、全ての設定を削除するには、以下のようなSQLを実行します。

postgres=# ALTER SYSTEM RESET ALL,

• 「ALTER SYSTEM」文を使用して、「RESET」に続けて「ALL」を指定します。

SQLのテクニックに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのテクニックに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

SQLのパフォーマンスに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのパフォーマンスに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

DB設計、SQLに関するおすすめの書籍

当サイトの書籍紹介ページで、DB設計、SQLに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。