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

Vao Tsun · Accepted Answer · 2020-04-27 09:34:15Z

5

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

edited Apr 27, 2020 at 9:34

answered Jan 27, 2018 at 16:42

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rjp Over a year ago

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

Kevin G Over a year ago

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

Angel Angeles · Accepted Answer · 2022-05-09 19:53:47Z

0

In my case I followed this documentation and it didn't show me the query logs (which was my intention) but it didn't work. Then I added these parameters to the postgres.conf file and the logs were shown:

event_source = 'PostgreSQL'

log_statement = 'all'

Both are commented out by default.

answered May 9, 2022 at 19:53

Angel Angeles

1112 silver badges3 bronze badges

1 Comment

mrbarletta Over a year ago

This is relevant only for Windows.

Collectives™ on Stack Overflow

postgres: how to turn on logging?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related