How to profile PostgreSQL Database?

Question

I want to profile (keep an Eye on) all the activities that goes on in a Database which is in PostgreSQL.

Is there any such utility which will help me do this?

Answer 1

"Keep an eye on" and "profile" are two quite different tasks in my view.

For profiling (not a live view on what's going on right now, but to see which queries take most time etc), check out pgFouine:

http://pgfouine.projects.postgresql.org/

This will let you see which queries are resource intensive, and take appropriate action: Add missing indexes, rewrite queries using other techiques etc.

Answer 2

For "keeping an eye", I use pgtop, a program which deliberately mimics Unix 'top' command.