How to Log the Time of User Connection and Disconnection in the Database

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article describes how to capture the time of user connecting and disconnecting from the database.

 

Environment

Resolution

This relevant information is controlled by two parameters "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 prints 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,

Additional Information

+ Environment:

Pivotal Greenplum Database (GPDB) all versions