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?

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

score 544 · Accepted Answer · edited Nov 23 '18 at 13:23

544

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.

edited Nov 23 '18 at 13:23

automatix

14,018
26
105
230

answered Apr 06 '09 at 16:37

Jarret Hardie

95,172
10
132
126

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
10

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
11

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

score 147 · Answer 2 · edited Jan 27 '16 at 13:02

147

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

edited Jan 27 '16 at 13:02

itachi

3,389
2
24
29

answered Jan 07 '16 at 20:15

vijay

10,276
11
64
79

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

score 90 · Answer 3 · answered Aug 08 '17 at 08:45

90

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

answered Aug 08 '17 at 08:45

A T

13,008
21
97
158

2

This should be the accepted answer - the only one that has practical value! – Janaka Bandara Jul 23 '20 at 13:19
2

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
2

reconnect psql to get effect. – Daniil Iaitskov Nov 12 '21 at 19:58

score 52 · Answer 4 · answered May 16 '13 at 14:18

52

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.

answered May 16 '13 at 14:18

Rix Beck

981
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

score 41 · Answer 5 · edited Jul 11 '14 at 20:00

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

edited Jul 11 '14 at 20:00

Jason Higgins

1,516
1
17
37

answered Jul 11 '14 at 19: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

score 28 · Answer 6 · answered Apr 06 '09 at 16:37

28

Set log_statement to all:

Error Reporting and Logging - log_statement

answered Apr 06 '09 at 16:37

Chad Birch

73,098
23
151
149

score 21 · Answer 7 · answered Apr 08 '10 at 09:53

21

+1 to above answers. I use following config

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

answered Apr 08 '10 at 09:53

Shekhar

7,095
4
40
45

Zoltán · Answer 8 · 2013-12-09T09:33:30.987

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:

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/

edited Dec 09 '13 at 09:33

answered Sep 17 '13 at 08:40

Zoltán

21,321
14
93
134

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

score 5 · Answer 9 · answered Oct 08 '20 at 11:05

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.

score 3 · Answer 10 · answered Sep 29 '19 at 18:36

3

You should also set this parameter to log every statement:

log_min_duration_statement = 0

answered Sep 29 '19 at 18:36

H.Ç.T

3,335
1
18
37

score 3 · Answer 11 · answered Mar 07 '22 at 10:03

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.

score 1 · Answer 12 · edited Feb 09 '23 at 05:22

You need to run the query below then restart PostgreSQL to enable logging persistently. *The parameter with ALTER SYSTEM SET is set to postgresql.auto.conf rather than postgresql.conf:

ALTER SYSTEM SET log_statement = 'all';

And, you need to run either of the queries below then restart PostgreSQL to disable logging persistently:

ALTER SYSTEM RESET log_statement;

Or:

ALTER SYSTEM SET log_statement = 'none';

You can also run the query below then need to restart PostgreSQL to enable logging persistently:

ALTER SYSTEM SET log_min_duration_statement = 0;

And, you can also run either of the queries below then need to restart PostgreSQL to disable logging persistently:

ALTER SYSTEM RESET log_min_duration_statement;

Or:

ALTER SYSTEM SET log_min_duration_statement = -1;

Be careful, these queries below cannot enable or disable logging persistently:

SET SESSION log_statement = 'all'
SET log_statement = 'all'
SET LOCAL log_statement = 'all'

SET SESSION log_min_duration_statement = 0;
SET log_min_duration_statement = 0;
SET LOCAL log_min_duration_statement = 0;

Or:

RESET log_statement;
SET SESSION log_statement = 'none'
SET log_statement = 'none'
SET LOCAL log_statement = 'none'

RESET log_min_duration_statement;
SET SESSION log_min_duration_statement = -1;
SET log_min_duration_statement = -1;
SET LOCAL log_min_duration_statement = -1;

score 0 · Answer 13 · answered Feb 27 '20 at 10:45

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

How to log PostgreSQL queries?

13 Answers13

Linked