List sessions / active connections in Redshift

Redshift tables contains a lot of useful information about database sessions.

Query

select s.process as process_id,
       c.remotehost || ':' || c.remoteport as remote_address,
       s.user_name as username,
       s.starttime as session_start_time,
       s.db_name,
       i.starttime as current_query_time,
       i.text as query 
from stv_sessions s
left join pg_user u on u.usename = s.user_name
left join stl_connection_log c
          on c.pid = s.process
          and c.event = 'authenticated'
left join stv_inflight i 
          on u.usesysid = i.userid
          and s.process = i.pid
where username <> 'rdsdb'
order by session_start_time desc;

Columns

  • process_id - Process ID of the session
  • remote_address - remote host with remote port
  • username - user name
  • session_start_time - date and time of session start
  • db_name - database name
  • current_query_time - time of current query executing was started
  • query - current query executing in session

Rows

  • One row: represents one active connection
  • Scope of rows: all active connections
  • Ordered by start time of the session

Sample results

You can see session list on our test server.

sample results

0
There are no comments. Click here to write the first comment.