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

cre8stevedev profile image

Project Starter Kit: Neon Postgres Database + ExpressJs + TypeScript and TypeORM + CLI Tool

Stephen Omoregie -

kurtchan profile image

Writing a Telegram and Email Notifications Script for Zabbix

Kurt De Austria -

shravan20 profile image

Neonode: Power Up Your Development with TypeScript & Neon.tech

Shravan Kumar B -

franckpachot profile image

Fault Tolerance with Raft and no Single Point of Failure

Franck Pachot -