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.