How Do I Monitor Open Database Sessions Associated With PostgreSQL ODBC Connections

Users can run ps auxww | grep ^postgres to display current connections. Connection information will appear after entries for the postmaster and statistics collector. For example:

$ ps auxww | grep ^postgres
postgres   850  0.0  1.1  4204 980   pts/1  SN   20:05   postmaster -i
postgres   853  0.0  1.1  5076 2327 pts/1  SN   20:05   postgres: stats buffer process   
postgres   855  0.0  1.1  4125 1618 pts/1  SN   20:05   postgres: stats collector process   
postgres   888  0.0  2.3  4523 4008 pts/1  SN   20:10   postgres: openlink test 192.168.13.146 idle
postgres   943  0.0  2.4  4523 5132 pts/1  SN   20:11   postgres: openlink regression 192.168.13.146 SELECT waiting

Note the following entries:

postgres   888  0.0  2.3  4523 4008 pts/1  SN   20:10   postgres: openlink test 192.168.13.146 idle
postgres   943  0.0  2.4  4523 5132 pts/1  SN   20:11   postgres: openlink regression 192.168.13.146 SELECT waiting

Each entry represents database activity. Here is the syntax the output employs:

postgres: user database host activity

For example:

postgres: openlink test 192.168.13.146 idle
postgres: openlink regression 192.168.13.146 SELECT waiting

Return values for the activity column may include a specific SQL command such as SELECT, idle, idle in transaction, or waiting. Connections go into idel status when the database is awaiting the issuance of a SQL command. The idle in transaction notation occurs when the database is waiting for a client in a BEGIN block. Waiting entails that the server is waiting for a lock to be released.

Note: Solaris users must run the following command to insure proper output: /usr/ucb/ps auxww | grep ^postgres