How to log PostgreSQL queries?

postgresql logging

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?

This is important: logging_collector = on

Also, beware that on some GNU/Linux distributions (e.g. Debian Jessie) systemctl restart postgresql may not actually restart PostgreSQL service you have configured (I don't understand why yet), so changes in the configuration file won't be applied. It is safer to use pg_ctl (or pg_ctlcluster on Debian).

I just tested this in Ubuntu 16.04 LTS, with PostgreSQL 9.5, and systemctl reload postgresql, systemctl restart postgresql, service postgresql reload and service postgresql restart all render configuration changes effective.

In my case (Win 10 desktop, pg12) I had to explicitly enable logging for the actual database using ALTER DATABASE (as in this answer)

automatix

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.

So just curious, does that mean PostgreSQL can't enable logging unless I restart the server? In MySQL, it is as simple as "SET GLOBAL general_log = 'ON';"

I myself don't know if there's a way to do it using a SQL statement like MySQL, but you can send a running server a command to reload the config with pg_ctl reload

PostgreSQL doesn't have a way to change its parameters via SQL statements yet (as of 9.2). Most logging parameters can be changed without a full server restart, by just doing pg_ctl reload instead. However, it takes a restart to change logging_collector.

With Postgres 9.4 and the new ALTER SYSTEM command a superuser can set GUC params from SQL.

The data directory cited in the answer is not its literal name; it refers to the path assigned to the data_directory variable in the PostgreSQL configuration file. On Debian and Ubuntu GNU/Linux, this file usually resides at /etc/postgresql/$v/main/postgresql.conf, where $v is the server version. Also, on the aforementioned systems, when log_destination = 'stderr', the output is written to /var/log/postgresql/postgresql-$v-main.log, where $v is the server version (not to some location inside data_directory).

itachi

Edit your /etc/postgresql/9.3/main/postgresql.conf, and change the lines as follows.

Note: If you didn't find the postgresql.conf file, then just type $locate postgresql.conf in a terminal

#log_directory = 'pg_log' to log_directory = 'pg_log' #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' to log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' #log_statement = 'none' to log_statement = 'all' #logging_collector = off to logging_collector = on Optional: SELECT set_config('log_statement', 'all', true); sudo /etc/init.d/postgresql restart or sudo service postgresql restart Fire query in postgresql select 2+2 Find current log in /var/lib/pgsql/9.2/data/pg_log/

The log files tend to grow a lot over a time, and might kill your machine. For your safety, write a bash script that'll delete logs and restart postgresql server.

Thanks @paul , @Jarret Hardie , @Zoltán , @Rix Beck , @Latif Premani

On debian stretch, I also had to uncomment # log_destination = 'stderr' in the configuration file before this worked.

I've following your step carefully and it doesn't works. Does it need restart?

if you don't want to write a bash script but just want the logs to overwrite monthly do this: log_filename = 'postgresql-%d.log' and no it won't overwrite after each restart, it will append for each day and overwrite each month. Of course there are different days depending on month 28,29,30,31 -- but you get the idea.

I discovered you can run select pg_reload_config(); since Postgres 9.0 rather than restarting the service in step 6.

A T

FYI: The other solutions will only log statements from the default database—usually postgres—to log others; start with their solution; then:

ALTER DATABASE your_database_name
SET log_statement = 'all';

Ref: https://serverfault.com/a/376888 / log_statement

This should be the accepted answer - the only one that has practical value!

The quoted statements work in Postgres 9.4 or later, but the leading paragraph is false. Setting log_statement = all in postgresql.conf for the respective database cluster - like the accepted answer suggests - affects all databases of that cluster - which can be overruled by per-database settings.

reconnect psql to get effect.

Rix Beck

SELECT set_config('log_statement', 'all', true);

With a corresponding user right may use the query above after connect. This will affect logging until session ends.

It's generally cleaner to use SET log_statement = 'all' or (for transaction level) SET LOCAL log_statement = 'all'. You might also be interested in the client_min_messages and log_min_messages settings.

This is great, since I want to log just the messages of my connection. Unfortunately I get: permission denied to set parameter "log_statement" since my user is not superuser.

You may ask DB administrator for grants executing the function.

GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...] | ALL FUNCTIONS IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

Jason Higgins

You also need add these lines in PostgreSQL and restart the server:

log_directory = 'pg_log'                    
log_filename = 'postgresql-dateformat.log'
log_statement = 'all'
logging_collector = on

logging_collector=on is necessary

Works like a charm. I was confused by the comment on the config file saying Required to be on for csvlogs, thinking this option was to log query output and not just statements, but it's not the case.

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

Chad Birch

Set log_statement to all:

Error Reporting and Logging - log_statement

Shekhar

+1 to above answers. I use following config

log_line_prefix = '%t %c %u ' # time sessionid user
log_statement = 'all'

Zoltán

Just to have more details for CentOS 6.4 (Red Hat 4.4.7-3) running PostgreSQL 9.2, based on the instructions found on this web page:

Set (uncomment) log_statement = 'all' and log_min_error_statement = error in /var/lib/pgsql/9.2/data/postgresql.conf. Reload the PostgreSQL configuration. For me, this was done by running /usr/pgsql-9.2/bin/pg_ctl reload -D /var/lib/pgsql/9.2/data/. Find today's log in /var/lib/pgsql/9.2/data/pg_log/

You don't need to restart - a pg_ctl reload is sufficient, and does not interrupt connections. Not convinced this answer adds anything to those already here.

@CraigRinger Thanks for the comment, that's quite an important fact. I will update the answer once I've tried your suggestion. I wrote this answer primarily for reference for myself, because at the time I had very little experience with UNIX, and I wanted to have all the necessary information in one place (e.g. the locations of postgresql.conf and the log files).

Delirante

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.

H.Ç.T

You should also set this parameter to log every statement:

log_min_duration_statement = 0

Ser

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

Suresh

Dynamically we can enable/disable the logging in 2 ways

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(); 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.

How to log PostgreSQL queries?

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US