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

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)

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay