434

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?

ROMANIA_engineer
  • 51,252
  • 26
  • 196
  • 186
Paul
  • 11,941
  • 20
  • 63
  • 99
  • 24
    This is important: ```logging_collector = on``` – bonyiii Jan 08 '15 at 13:15
  • 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). – Skippy le Grand Gourou Apr 07 '16 at 19:24
  • 4
    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. – Ben Johnson Aug 08 '16 at 18:48
  • In my case (Win 10 desktop, pg12) I had to explicitly enable logging for the actual database using `ALTER DATABASE` (as in [this answer](https://stackoverflow.com/questions/722221/how-to-log-postgresql-queries#45563564)) – Janaka Bandara Jul 23 '20 at 13:26

12 Answers12

520

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.
automatix
  • 12,706
  • 23
  • 96
  • 213
Jarret Hardie
  • 90,470
  • 10
  • 128
  • 124
  • 4
    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';" – Antony Dec 07 '11 at 22:41
  • 9
    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` – Jarret Hardie Dec 08 '11 at 08:14
  • 9
    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. – Greg Smith Jul 23 '12 at 19:11
  • 8
    With Postgres 9.4 and the new [`ALTER SYSTEM`](http://www.postgresql.org/docs/current/interactive/sql-altersystem.html) command a superuser can set GUC params from SQL. – Erwin Brandstetter May 25 '15 at 23:55
  • FWIW - If you do the above 3 things, it works. That is: `logging_collector = on`, `log_destination = 'stderr'`, `log_statement = 'all'` I was missing `log_destination`, and it wasn't working. – MrProper Feb 03 '16 at 13:59
  • What about trigger functions? Can they be logged? – Bertus Kruger Mar 16 '16 at 02:57
  • 8
    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`). – Ben Johnson Aug 08 '16 at 18:40
  • In postgres 10, the folder is now called just "log" and postgres made it automatically for me (at least on macos) – chrismarx Jun 18 '18 at 20:27
  • 2
    For those using Docker: if you don't want to create a configuration file just to enable logging then add this to your docker-compose.yml: `command: postgres -c 'log_statement=all'` – mennanov May 02 '19 at 18:24
  • For the reload, `SELECT pg_reload_conf()` might work; the bundled `postgresql.conf.sample` says: *If you edit the file on a running system, you have to SIGHUP the server for the changes to take effect, run "pg_ctl reload", or execute "SELECT pg_reload_conf()". Some parameters, which are marked below, require a server shutdown and restart to take effect.* – Janaka Bandara Jul 22 '20 at 05:44
  • Scratch `SELECT pg_reload_conf()` and `pg_ctl reload`; in my case only the service reload did the trick. It appears that the logging setting only applies to *new* connections (i.e. those that are already alive - such as from a connection pool - remain in non-logging state) so until you terminate and recreate them you are probably not going to see the logs that you want to see; which is kind of the whole point in enabling logging! – Janaka Bandara Jul 23 '20 at 13:22
134

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

  1. #log_directory = 'pg_log' to log_directory = 'pg_log'

  2. #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' to log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

  3. #log_statement = 'none' to log_statement = 'all'

  4. #logging_collector = off to logging_collector = on

  5. Optional: SELECT set_config('log_statement', 'all', true);

  6. sudo /etc/init.d/postgresql restart or sudo service postgresql restart

  7. Fire query in postgresql select 2+2

  8. 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

itachi
  • 3,129
  • 2
  • 21
  • 28
vijay
  • 9,053
  • 8
  • 59
  • 72
  • 3
    On debian stretch, I also had to uncomment `# log_destination = 'stderr'` in the configuration file before this worked. – phihag Jul 04 '18 at 19:09
  • I've following your step carefully and it doesn't works. Does it need restart? – Yohanim Jul 19 '18 at 08:53
  • 3
    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. – sojim2 Mar 06 '19 at 00:38
  • I discovered you can run `select pg_reload_config();` since Postgres 9.0 rather than restarting the service in step 6. – jamesthollowell Oct 08 '21 at 13:47
65

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

A T
  • 11,788
  • 17
  • 89
  • 150
  • 1
    This should be the accepted answer - the only one that has practical value! – Janaka Bandara Jul 23 '20 at 13:19
  • 1
    The quoted statements work in Postgres 9.4 or later, but the leading paragraph is false. Setting [`log_statement = all`](https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-STATEMENT) 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. – Erwin Brandstetter Mar 20 '21 at 16:55
  • 1
    reconnect psql to get effect. – Daniil Iaitskov Nov 12 '21 at 19:58
49
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.

Rix Beck
  • 941
  • 8
  • 5
  • 9
    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. – Craig Ringer Nov 11 '13 at 02:31
  • 1
    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. – guettli Jun 10 '14 at 12:20
  • 3
    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 ]` – Rix Beck Aug 31 '14 at 20:03
41

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
Jason Higgins
  • 1,476
  • 1
  • 17
  • 37
Latif Premani
  • 411
  • 4
  • 2
  • 2
    logging_collector=on is necessary – wjin Dec 06 '14 at 00:21
  • 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. – Jacopofar Jan 17 '18 at 10:23
  • In your data/postgresql.conf file, change the log_statement setting to 'all'. – FlyingV Apr 10 '19 at 20:54
27

Set log_statement to all:

Error Reporting and Logging - log_statement

Chad Birch
  • 71,186
  • 23
  • 148
  • 148
21

+1 to above answers. I use following config

log_line_prefix = '%t %c %u ' # time sessionid user
log_statement = 'all'
Shekhar
  • 6,925
  • 4
  • 39
  • 45
13

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:

  1. Set (uncomment) log_statement = 'all' and log_min_error_statement = error in /var/lib/pgsql/9.2/data/postgresql.conf.
  2. 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/.
  3. Find today's log in /var/lib/pgsql/9.2/data/pg_log/
Zoltán
  • 20,250
  • 12
  • 87
  • 128
  • 4
    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. – Craig Ringer Nov 11 '13 at 02:29
  • 1
    @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). – Zoltán Nov 11 '13 at 08:59
3

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.

Delirante
  • 468
  • 5
  • 10
1

You should also set this parameter to log every statement:

log_min_duration_statement = 0
H.Ç.T
  • 2,862
  • 1
  • 13
  • 33
0

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
Ser
  • 2,494
  • 20
  • 27
0

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.

Suresh
  • 133
  • 1
  • 17