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

saint_vandora profile image

How to Use JSONB in PostgreSQL with Step-by-Step Instructions

FOLASAYO SAMUEL OLAYEMI -

edvichuki profile image

Comprehensive Weather Data Analysis Using Python: Temperature, Rainfall Trends, and Visualizations

Gichuki Edwin -

jacktt profile image

[Postgres] Isolation levels

JackTT -

franckpachot profile image

A brief example of an SQL serializable transaction

Franck Pachot -