Postgresql – Monitoring Amazon RDS with pgtop


I recently migrated a Postgres database to Amazon RDS and I am lacking a good live monitoring tool. I tried pgtop, but I can't get it to work on Amazon. I can see the header with the DB statistics (CPU, I/O, memory, etc.) but the query list is completely empty.

So, does anyone have any experience with pgtop specifically on RDS for PostgreSQL? I would like to know what queries are running, cpu usage, queries state, etc.

Best Answer

I don't know if that applies to Amazon RDS, but a possible reason for getting an empty list of queries with pgtop is when using a version older than 3.7.0 (released in August 2013) against a 9.2 or newer PostgreSQL instance.

I just noticed this with the ptop 3.6.2 package that currently ships with Ubuntu 12.04 LTS. I guess this may be also not fixed in other distributions.

The empty list problem occurs with older versions of pgtop because they expect the postgres process ID in pg_stat_statements.procpid, and this column was renamed to in PG 9.2.

This is combined with pgtop being unhelpful by not reporting any error when its SQL command fails. Instead it just displays a blank list of processes. The problematic query can be spotted in the server's log.