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;
You can terminate any running query using the number shown in the
Forcibly Close Connections to DB¶
Close all connections to DB
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();