This is an internal documentation. There is a good chance you’re looking for something else. See Disclaimer.

SQL Snippets (Postgres)

Show Queries Running in Postgres

Show Queries Running on DB

Show open transactions and queries running on current database:

SELECT
  pid,
  now() - xact_start as "tx time",
  now() - query_start as "query time",
  state,
  query
FROM
  pg_stat_activity
WHERE
  state <> 'idle'
  AND datname = current_database()
  AND pid <> pg_backend_pid()
ORDER BY
  xact_start;

Show Long Running Queries

Show queries associated with transactions that have been open for more than 5 minutes:

 SELECT
   pid,
   now() - xact_start as "tx time",
   now() - query_start as "query time",
   datname,
   state,
   query
 FROM
   pg_stat_activity
 WHERE
   state <> 'idle'
   AND now() - xact_start > interval '5m'
ORDER BY
   xact_start;

Hint

You can terminate any running query using the number shown in the pid column:

SELECT pg_terminate_backend(pid);

Forcibly Close Connections to DB

Close all connections to DB DB_NAME.

Caution

This kills all connections to the database, including connections from Nice and pg_dump!

SELECT
  pg_terminate_backend(pg_stat_activity.pid)
FROM
  pg_stat_activity
WHERE
  pg_stat_activity.datname = 'DB_NAME'
  AND pid <> pg_backend_pid();