DEV Community

Cover image for How to use pg_stat_monitor on PostgreSQL
Sihar Simbolon
Sihar Simbolon

Posted on

How to use pg_stat_monitor on PostgreSQL

#postgres #monitoring

This setup using PostgreSQL 12 on CentOS 7

After this setup, you can check the query plan for query that already done on PostgreSQL.

Install PostgreSQL repository package

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Enter fullscreen mode Exit fullscreen mode

Install extension pg_stat_monitor

yum install -y pg_stat_monitor_12
Enter fullscreen mode Exit fullscreen mode

Add extension in Postgresql configuration

...
shared_preload_libraries = 'pg_stat_monitor'    # (change requires restart)
...

pg_stat_monitor.pgsm_query_max_len = 4096
pg_stat_monitor.pgsm_normalized_query = 1
pg_stat_monitor.pgsm_enable_query_plan = 1
Enter fullscreen mode Exit fullscreen mode

Restart PostgreSQL service

systemctl restart postgresql-12
Enter fullscreen mode Exit fullscreen mode

Enter to psql and create the extension

psql -U postgres  
postgres=# create extension pg_stat_monitor;
postgres=# select query, query_plan from pg_stat_monitor;
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

Read next

olibhiaghosh profile image

How To Make A Background Color Switcher Project Using JavaScript?

Olibhia Ghosh -

meenachan101 profile image

Git Worktree: Your Ticket to Parallel Development Paradise

meenachan -

thepracticaldev profile image

What Are Your C# Strategies?

dev.to staff -

ccoveille profile image

📸🔒GitHub: protect your email from spammers with this GitHub privacy setting

Christophe Colombier -