Monitoring Queries on PostgreSQL

I'm used to Redis, and there is a wonderful command called MONITOR that shows the commands being executed by the server in real-time. Here's how I'm achieving the same with Postgres.

Enabling logging_collector

First, check if logging_collector is on:

show logging_collector;

If it's showing off, you have to enable it by changing Postgres configuration file.

To check the config filepath:

show config_file;

Here's mine:

/etc/postgresql/17/main/postgresql.conf

There, locate this line:

#logging_collector = off

And change to:

logging_collector = on

Restart the server:

sudo systemctl restart [e-mail]

Enabling log_statement

Now, on the desired database, check if log_statement is enabled:

show log_statement;

If it says none, you can change to all with the following:

alter database <your-database-name> set log_statement = 'all';

This enables logging only for the desired database. This setting persists even in a service restart.

Checking the logs

You can run those to get the log filepath:

show data_directory;
show log_directory;
show log_filename;

Running them together in a single command:

select pg_catalog.current_setting('data_directory') || '/' || pg_catalog.current_setting('log_directory') || '/' || pg_catalog.current_setting('log_filename');

Which gave me:

/var/lib/postgresql/17/main/log/postgresql-%Y-%m-%d_%H%M%S.log

Now just tail -f this file :)