Monitoring database activity

Sometimes, for diagnostic purposes, you may wish to see what the PostgreSQL database server is doing at any time. This can be useful if VPOP3 is taking a long time to start (eg after an upgrade) or if the server is struggling because it's so busy.

All you need to do is open a command prompt and navigate to the VPOP3 installation directory then type 'psql' (the default password here is 'vpop3pass'). Then, at the prompt, type

select * from pg_stat_activity;

This will give lots of data depending on which version of PostgreSQL you are using. In the Windows version of PostgreSQL 9.1 which VPOP3 5 and later use, then the useful columns are:

  • xact_start (this is the time when the current database transaction started)
  • query_start (this is the time when the current query started)
  • waiting (this is 'f' if the query is progressing, or 't' if the query is waiting for a lock on some part of the database to be released before it can continue)
  • state (this is 'idle' if the connection is idle, or 'active' if it is doing something - note that if it is 'idle', then the rest of the fields are the PREVIOUS data, not current data - eg the 'query' will be the last query which was run, it is not being run now)
  • query (this is the current (or previous, if state is 'idle') SQL query being processed

If you wish, you could just receive this data by typing:

select state, waiting, xact_start, query_start, query from pg_stat_activity;

but it may be easier just to retrieve all the data and filter it visually.