Monitoring Postgres from the Command Line

August 06, 2012

You might already be aware that Postgres updates the process title of all its running processes. For example, this is a Debian Linux ps display for an idle Postgres server:



postgres  2544  2543  0 10:47 ?        00:00:00 /u/pgsql/bin/postmaster -i
postgres  2546  2544  0 10:47 ?        00:00:00 postgres: checkpointer process
postgres  2547  2544  0 10:47 ?        00:00:00 postgres: writer process
postgres  2548  2544  0 10:47 ?        00:00:00 postgres: wal writer process
postgres  2558  2544  0 10:47 ?        00:00:01 postgres: autovacuum launcher process
postgres  2575  2544  0 10:47 ?        00:00:02 postgres: stats collector process



You can see the postmaster, and its helper processes. "postgres:" indicates the name of the executable. You can also see session information:



postgres  6564  2544  0 18:27 ?        00:00:00 postgres: postgres test [local] idle
postgres  6860  2544  9 18:28 ?        00:00:00 postgres: demo template1 [local] SELECT



The first session shows an idle user postgres connected to database test; the second shows user demo doing a select in the template1 database. These updates are controlled by update_process_title, which is enabled by default.

Another features is that tools like top can also display this status information, though such display is often disabled by default. For top, using the -c flag or typing 'c' will display the process status — this is often helpful in obtaining information about a running session, perhaps one that is consuming much cpu or memory.

While the statistics views give in-depth reporting of database activity at the sql level (particularly pg_stat_activity), monitoring Postgres at the command line is also useful. It allows database information to be integrated with operating system monitoring tools to offer superior analysis about how the database is interacting with system resources.

Share this

Relevant Blogs

Move Your Workloads to the Public Cloud—Not Oracle’s

In recent years, more and more businesses—both industry leaders and up-and-comers—have decided to ditch their legacy commercial database in favor of open source solutions like Community PostgreSQL. With constantly rising...
February 24, 2023

More Blogs