Re: Last active time for a database

On Tue, Sep 9, 2014 at 4:27 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> Is there a way for a superuser to find the last time a database had an
> active user connection? (While being logged into a different database in the
> same instance, of course).
> The context here is looking for looking for automated integration testing
> databases that have been leaked due to bugs/crashes in the testing
> framework.
From the client-side, not that I am aware of. pg_stat_activity reports
only the active connections not an history of it.

> Yes, I can mine the server log files, but that is really scraping the bottom
> of the barrel.
It depends on the log format, you could always couple a CSV log file
using log_connection = on with file_fdw and query the logs directly,
filtering connection messages. Another idea could be to use the hook
in elog.c to track messages beginning by "connection authorized: " and
perform some pre-processing on the error data context that could
facilitate of tracking. That's not much different from the file_fdw
solution with for example CSV format, still it may help with systems
having lots of logs to avoid a lot of processing that file_fdw may do
uselessly.
My 2c.
--
Michael