PostgreSQL how to see which queries have run

Question

I have a PostgreSQL DB at my computer and I have an application that runs queries on it.

How can I see which queries has run on my DB?

I use a Linux computer and pgadmin.

any details about what does your application look like and how it accesses database? — pavel_kazlou, Nov 21 '11 at 07:06

Erwin Brandstetter · Accepted Answer · 2020-08-28T23:47:13.963

Turn on the server log:

log_statement = all

This will log every call to the database server.

I would not use log_statement = all on a production server. Produces huge log files.
The manual about logging-parameters:

log_statement (enum)

Controls which SQL statements are logged. Valid values are none (off), ddl, mod, and all (all statements). [...]

Resetting the log_statement parameter requires a server reload (SIGHUP). A restart is not necessary. Read the manual on how to set parameters.

Don't confuse the server log with pgAdmin's log. Two different things!

You can also look at the server log files in pgAdmin, if you have access to the files (may not be the case with a remote server) and set it up correctly. In pgadmin III, have a look at: Tools -> Server status. That option was removed in pgadmin4.

I prefer to read the server log files with vim (or any editor / reader of your choice).

Brandstetter I will just see logs and turn it off. Is it enough just to make log_statement = all? I have opened Server status it asked me something about installing a package but it opened a window and writes: Logs are not available for this server. Should I restart my postgresql? — kamaci, Nov 21 '11 at 07:28
@kamaci: I amended my answer with additional information. Follow the links I provided for more. — Erwin Brandstetter, Nov 21 '11 at 07:45

score 86 · Answer 2 · edited Aug 10 '18 at 17:19

PostgreSql is very advanced when related to logging techniques

Logs are stored in Installationfolder/data/pg_log folder. While log settings are placed in postgresql.conf file.

Log format is usually set as stderr. But CSV log format is recommended. In order to enable CSV format change in

log_destination = 'stderr,csvlog'   
logging_collector = on

In order to log all queries, very usefull for new installations, set min. execution time for a query

log_min_duration_statement = 0

In order to view active Queries on your database, use

SELECT * FROM pg_stat_activity

To log specific queries set query type

log_statement = 'all'           # none, ddl, mod, all

For more information on Logging queries see PostgreSql Log.

score 9 · Answer 3 · answered Apr 30 '17 at 22:38

9

I found the log file at /usr/local/var/log/postgres.log on a mac installation from brew.

answered Apr 30 '17 at 22:38

Michael

1,177
15
15

commamd+shift+. to show hidden files in users folder. .pgadmin ->pgadim4.log – AJ AJ Sep 11 '22 at 11:24

score 3 · Answer 4 · answered Dec 12 '18 at 05:19

3

While using Django with postgres 10.6, logging was enabled by default, and I was able to simply do:

tail -f /var/log/postgresql/*

Ubuntu 18.04, django 2+, python3+

answered Dec 12 '18 at 05:19

jmunsch

22,771
11
93
114

This seems to only log inserts and queries that failed with an error, Helpful answer nontheless. – Peter Chaula Dec 15 '20 at 17:24

score 2 · Answer 5 · answered Dec 12 '18 at 14:47

2

You can see in pg_log folder if the log configuration is enabled in postgresql.conf with this log directory name.

answered Dec 12 '18 at 14:47

s21s

111
1
9

PostgreSQL how to see which queries have run

5 Answers5

Linked