How to log PostgreSQL queries

Question

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

User · Answer

1 to above answers  I use following config  log line prefix     t  c  u     time sessionid user log statement    all

User · Answer

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

User · Answer

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

User · Answer

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 · Answer

There is an extension in postgresql for this  It s name is  quot pg stat statements quot   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  quot pg stat statements quot   In this view you can see all the executed queries

User · Answer

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

User · Answer

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

User · Answer

You should also set this parameter to log every statement   log min duration statement   0

User · Answer

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

User · Answer

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 · Answer

Set log statement to all   Error Reporting and Logging - log statement

[postgresql] How to log PostgreSQL queries?

Examples related to postgresql

Examples related to logging