How to log PostgreSQL queries?
PostgresqlLoggingPostgresql Problem Overview
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?
Postgresql Solutions
Solution 1 - Postgresql
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 thedata
directory, and that the postgres user can write to it.
Solution 2 - Postgresql
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'
tolog_directory = 'pg_log'
-
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
tolog_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
-
#log_statement = 'none'
tolog_statement = 'all'
-
#logging_collector = off
tologging_collector = on
-
Optional:
SELECT set_config('log_statement', 'all', true);
-
sudo /etc/init.d/postgresql restart
orsudo 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
Solution 3 - Postgresql
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';
Solution 4 - Postgresql
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.
Solution 5 - Postgresql
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
Solution 6 - Postgresql
Set log_statement
to all
:
http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html#GUC-LOG-STATEMENT">Error Reporting and Logging - log_statement
Solution 7 - Postgresql
+1 to above answers. I use following config
log_line_prefix = '%t %c %u ' # time sessionid user
log_statement = 'all'
Solution 8 - Postgresql
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'
andlog_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/
Solution 9 - Postgresql
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.
Solution 10 - Postgresql
You should also set this parameter to log every statement:
log_min_duration_statement = 0
Solution 11 - Postgresql
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
Solution 12 - Postgresql
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.