2013-08-24

Real-time console based monitoring of PostgreSQL databases (pg_view)

In many cases, it is important to be able to keep your hand on the pulse of your database in real-time. For example when you are running a big migration task that can introduce some unexpected locks, or when you are trying to understand how the current long running query is influencing your IO subsystem.

For a long time I was using a very simple bash alias that was injected from the .bashrc script and that included the calls to system utilities like watch, iostat, uptime, df, some additional statistics from the /proc/meminfo and psql that was extracting information about currently running queries and if that queries are waiting for a lock. But this approach had several disadvantages. In many cases I was interested in the disk read/write information for query processes or PostgreSQL system processes, like WAL and archive writers. Also I wanted to have a really easy way to notice the queries that are waiting for locks and probably highlight them by color.
Several weeks ago we finally open-sourced our new tool, that makes our lives much easier. That tool combines all the feature requests that I was dreaming of for a long time. Here it is: pg_view.

I already have some more feature requests actually and hope that Alexey will find some time to add them to the tool in nearest future. So if somebody wants to contribute or give some more ideas, please comment and open feature requests on the github page :)