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 :)

3 comments:

Jon Erdman said...

This looks pretty kick ass, thanks for FOSSing it. We'll be trying it out at our company.

-Jon Erdman (aka StuckMojo)

OBTW, I don't suppose you'd be interested in giving a lightning talk on it at the Postgres Open conference in Chicago Sept 16-18 (I'm on the planning committee)?

Details here: http://www.postgresopen.org/2013/home/

Jeff Lambert said...

Very impressive indeed. I could see this becoming a core tool.

Valentine Gogichashvili said...

Hello Jon,

I will be giving a talk at PGConf.EU in Dublin next month. And I suppose Alexey will give a lightening talk there about the tool as well.

Unfortunately I am not planning to coming to Chicago conference yet.

We will push some minor improvements to the tool next week :) So it will become even more usable, I hope :)