postgres: how to turn on logging?

Question

I followed what is explained in How to log PostgreSQL queries? but the logging collector stays off.

    postgres=# show logging_collector;
    logging_collector
    -------------------
    off
    (1 row)

My postgresql.conf file Looks like this:

    listen_addresses = '*'
    tcpip_socket = true
    log_statement = 'all'
    log_connections = yes
    log_destination = 'syslog'
    logging_collector = on
    log_directory = '/var/log/pg_log'
    log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
    log_truncate_on_rotation = true
    log_rotation_age = 1440
    client_min_messages = LOG
    log_min_messages = INFO
    log_min_error_statement = DEBUG5
    log_min_duration_statement = 0

Solution

often it is enough to

pg_reload_conf()

to re-read postgres.conf

https://www.postgresql.org/docs/current/static/functions-admin.html

pg_reload_conf() Cause server processes to reload their configuration files

but:

t=# select name,setting,unit,source,context from pg_settings where name = 'logging_collector';
       name        | setting | unit |       source       |  context
-------------------+---------+------+--------------------+------------
 logging_collector | on      |      | configuration file | postmaster
(1 row)

https://www.postgresql.org/docs/current/static/view-pg-settings.html

postmaster These settings can only be applied when the server starts, so any change requires restarting the server. Values for these settings are typically stored in the postgresql.conf file, or passed on the command line when starting the server. Of course, settings with any of the lower context types can also be set at server start time.

so it means you have to restart your cluster

Comments

[rjp]

Small typo - pg_reload_config() in the first code block should be pg_reload_conf() per the second highlight.

[Kevin G]

command to run to reload server with new configs: SELECT pg_reload_conf();