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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.