POSTGRESQL DATABASE MONITORING

Engin Yilmaz

6 years ago

In this article I will share some monitoring scripts about postgresql.

To Check if the Postgresql service is running via OS(linux):

ps -ef | grep postgres

1	ps -ef \| grep postgres

To Check the RAM / CPU status via OS(linux):

top

top

To Check the Disk Status via OS(linux9:

df -h

df -h

To Check the Number of the Connection Count:

You can find the following information with the following script.

How many connections in the database?
What is Transaction Count?
Write to disk,
Read from cache,
What is the number of rows inserted, deleted etc?

SELECT * FROM pg_stat_database;

1	SELECT * FROM pg_stat_database;

However, to have this table, after installing the Postgresql9.6-contrib package, it is necessary to create the following extension by connecting to the database with psql.

create extension pg_stat_statements;

1	create extension pg_stat_statements;

Long Running Queries:

SELECT total_time,query FROM pg_stat_database;

1	SELECT total_time,query FROM pg_stat_database;

Table and index access statistics:

select * from pg_stat_user_indexes;

select * from pg_stat_activity;

select * from pg_stat_user_indexes;

select * from pg_stat_activity;

How long have the existing queries been working:

SELECT pid,datname,usename, now() – query_start AS runtime, state,query FROM pg_stat_activity;

1	SELECT pid,datname,usename, now() – query_start AS runtime, state,query FROM pg_stat_activity;

To stop a query:

select pg_cancel_backend(<pid of the process>)

1	select pg_cancel_backend(<pid of the process>)

To kill if the query does not stop using the above method:

select pg_terminate_backend(<pid of the process>)

1	select pg_terminate_backend(<pid of the process>)

To see locked objects:

SELECT * FROM pg_locks;

1	SELECT * FROM pg_locks;

To see the size of a database:

SELECT pg_size_pretty(pg_database_size(‘database_name’));

1	SELECT pg_size_pretty(pg_database_size(‘database_name’));

After connecting with psql, you can see the sizes of all databases with l+ command.

To see the size of a table:

After connecting to database with psql;

SELECT pg_size_pretty( pg_total_relation_size(‘table_name’));

1	SELECT pg_size_pretty( pg_total_relation_size(‘table_name’));

You can read the following articles:

“How To Get Size Of All Tables And Database On PostgreSQL“,

“How to List PostgreSQL Users and Permission“,

“How To List All PostgreSQL Databases“,

“How to Get The Size of all Postgres Databases”