How to log PostgreSQL queries?

user_83313 0
Viewed 526118

How to enable logging of all SQL executed by PostgreSQL 8.3?

Edited (more info) I changed these lines :

log_directory = 'pg_log'                    
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'all'

And restart PostgreSQL service... but no log was created... I'm using Windows Server 2003.

Any ideas?

logging postgresql
12 Answers

In your data/postgresql.conf file, change the log_statement setting to 'all'.

Edit

Looking at your new information, I'd say there may be a few other settings to verify:

  • make sure you have turned on the log_destination variable
  • make sure you turn on the logging_collector
  • also make sure that the log_directory directory already exists inside of the data directory, and that the postgres user can write to it.
user_722470
user_416650

There is an extension in postgresql for this. It's name is "pg_stat_statements". https://www.postgresql.org/docs/9.4/pgstatstatements.html

Basically you have to change postgresql.conf file a little bit:

shared_preload_libraries= 'pg_stat_statements'
pg_stat_statements.track = 'all'

Then you have to log in DB and run this command:

create extension pg_stat_statements;

It will create new view with name "pg_stat_statements". In this view you can see all the executed queries.

user_35461070

You should also set this parameter to log every statement:

log_min_duration_statement = 0
user_105507380

Dynamically we can enable/disable the logging in 2 ways

  1. Change the global variables in DB and reload the configuration a) Set log_statement = 'all'; or set log_min_duration_statement = 0; b) select pg_reload_conf();
  2. From the Linux command line, edit the postgres configuration file, change the log related parameters log_min_duration_statement = 0 log_statement = 'all' Reload the configuration file su - postgres /usr/bin/pg_ctl reload

In both these cases, we should not be doing a Postgres restart. We can dynamically enable/disable logging with configuration reload.

I hope this should be helpful.

user_134725450

I was trying to set the log_statement in some postgres config file but in fact the file was not read by our postgres.

I confirmed that using the request : 

select *
from pg_settings

[...]
log_statement   none # That was not the value i was expected for !!!

I use this way https://stackoverflow.com/a/41912295/2294168

command: postgres -c config_file=/etc/postgresql.conf
user_22941680
Related