Last active time for a database

On Tue, Sep 9, 2014 at 4:27 AM, Jeff Janes <jeff.janes@gmail.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general