Pivotal Knowledge Base

Follow

How to Log the time of user connection/disconnection in the DB

Goal

How to capture the time of user connecting and disconnecting from the database ?

Solution

This relevant information is controlled by two parameter "log_connections" & "log_disconnections", which is turned off by default in the database.

Turning it on using

[gpadmin@mdw faisal]$ gpconfig -c log_disconnections -v on;
20141224:01:40:38:027572 gpconfig:mdw:gpadmin-[INFO]:-completed successfully
[gpadmin@mdw faisal]$ gpconfig -c log_connections -v on;
20141224:01:43:08:028070 gpconfig:mdw:gpadmin-[INFO]:-completed successfully

and restarting the database (as this cannot be set dynamically), will log the below information in the master log, where disconnection also print the amount of time in the database.

2014-12-24 01:44:16.758338 PST,,,p28330,th1874561184,,,2014-12-24 01:44:16 PST,0,,,seg-1,,,,,"LOG","00000","connection received: host=[local]",,,,,,,0,,"postmaster.c",6781,
2014-12-24 01:44:16.758584 PST,"gpadmin","flightdata",p28330,th1874561184,"[local]",,2014-12-24 01:44:16 PST,0,,,seg-1,,,,,"LOG","00000","connection authorized: user=gpadmin database=flightdata",,,,,,,0,,"postmaster.c",6867,
2014-12-24 01:44:21.107764 PST,"gpadmin","flightdata",p28330,th1874561184,"[local]",,2014-12-24 01:44:16 PST,0,con8,,seg-1,,,,,"LOG","00000","disconnection: session time: 0:00:04.349 user=gpadmin database=flightdata host=[local]",,,,,,,0,,"postgres.c",5422,

Comments

Powered by Zendesk