DEV Community

Cover image for Monitoring Queries on PostgreSQL
Talles L
Talles L

Posted on

1

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;
Enter fullscreen mode Exit fullscreen mode

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

To check the config filepath:

show config_file;
Enter fullscreen mode Exit fullscreen mode

Here's mine:

/etc/postgresql/17/main/postgresql.conf
Enter fullscreen mode Exit fullscreen mode

There, locate this line:

#logging_collector = off
Enter fullscreen mode Exit fullscreen mode

And change to:

logging_collector = on
Enter fullscreen mode Exit fullscreen mode

Restart the server:

sudo systemctl restart postgresql@17-main.service
Enter fullscreen mode Exit fullscreen mode

Enabling log_statement

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

show log_statement;
Enter fullscreen mode Exit fullscreen mode

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

alter database <your-database-name> set log_statement = 'all';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

Which gave me:

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

Now just tail -f this file :)

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (0)

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay