SHOW FULL PROCESSLIST in PostgreSQL

MySQL has show full processlist; to see a list of currently running queries.

PostgreSQL has a similar shell like select * from pg_stat_activity;

SHOW FULL PROCESSLIST in PostgreSQL
SHOW FULL PROCESSLIST in PostgreSQL

1. Simple Command

select * from pg_stat_activity;

2. Second Option

SELECT user, pid, client_addr, query, query_start, NOW() - query_start AS elapsed
FROM pg_stat_activity
WHERE query != '<IDLE>'
-- AND EXTRACT(EPOCH FROM (NOW() - query_start)) > 1
ORDER BY elapsed DESC;

3. Third Option

SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

4. Finding Long Processing Time / Stuck Queries

SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

5. Cancelling Long Running Queries

SELECT pg_cancel_backend(pid);

6. Kill Process ID / Terminate Stuck Queries

SELECT pg_terminate_backend(pid);

SHOW FULL PROCESSLIST in PostgreSQL

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Peter’s Adventures in Ruby: Garbage Collection in Ruby

🔮 5 Personal Development Tips For Budding Developers

Kubernetes 1.22 Release

The Kubernetes logo

360° IT Check #34 — Happy Birthday .NET!

Getting started with DevOps

SWITCH! Code Demo Day

How to setup your Snowflake environment when moving on-premise databases to the cloud

Why do we calculate STRESS, when we have FORCES?

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Josua Marcel C.

Josua Marcel C.

ask Google

More from Medium

An Introduction to Github and GitHub Desktop

What to do when PostgreSQL does not use your index

One Piece Searcher — How proof-of-concepts can quickly go south

Introduce to Async/Await by making breakfast