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

More Blogs

Postgres 16 contribution analysis 2023

Robert Haas recently published his 2023 analysis Who Contributed to PostgreSQL Development in 2023? - a follow up to prior analyses done in 2022 and 2021. Robert focuses...
February 08, 2024

Powering the Future of Finance with Postgres

Today’s financial service providers are under pressure from multiple angles. Expanding data, stringent regulations, security threats, rising costs, increased customer expectations and more are requiring banking, financial services, and insurance...
January 24, 2024