How to Kill a Postgres Session: A Complete Guide for Database Administrators

We’ve all been there. You’re monitoring your PostgreSQL database, and suddenly you notice a query that’s been running for hours, hogging resources and slowing everything down. Or worse, a session is blocking critical operations, and your entire application is grinding to a halt.

When I first encountered a runaway Postgres session during a production deployment, I panicked. The application was timing out, users were complaining, and I needed to act fast. That’s when I learned the importance of knowing how to properly kill a Postgres session.

In this comprehensive guide, I’ll walk you through everything you need to know about identifying, managing, and terminating problematic PostgreSQL sessions safely and effectively.

Understanding PostgreSQL Sessions

Before we dive into killing sessions, let’s understand what we’re dealing with. A PostgreSQL session is created whenever a client connects to your database. Each session has a unique process ID (PID) and can run multiple queries throughout its lifetime.

Sometimes these sessions can become problematic:

• Long-running queries that consume excessive CPU or memory
• Idle transactions that hold locks on tables
• Blocking sessions that prevent other queries from executing
• Zombie connections from crashed applications

Why You Might Need to Kill a Session

There are several legitimate scenarios where terminating a session becomes necessary:

Performance Issues: A poorly optimized query running for hours can degrade your entire database performance. I once had a developer accidentally run an unindexed query on a table with 50 million rows during peak hours – not fun.

Deadlock Situations: When two sessions are waiting for each other to release locks, manual intervention is often the only solution.

Application Bugs: Sometimes applications don’t close connections properly, leaving zombie sessions that accumulate over time.

Emergency Maintenance: During critical updates or maintenance windows, you may need to clear all active sessions quickly.

Step 1: Identifying Active Sessions

Before you can kill a session, you need to find it. PostgreSQL provides several views and functions to monitor active connections.

View All Active Sessions

Connect to your PostgreSQL database and run:

sql

SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query,
    query_start
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

This query shows you all currently active sessions with important details like:

• pid: The process ID you’ll use to kill the session
• usename: Which database user is running the query
• state: Whether it’s active, idle, or idle in transaction
• query: The actual SQL being executed
• query_start: When the query began

Finding Long-Running Queries

To specifically identify sessions that have been running too long:

sql

SELECT 
    pid,
    now() - query_start AS duration,
    usename,
    query
FROM pg_stat_activity
WHERE state = 'active'
    AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;

This helped me countless times when troubleshooting performance issues on production databases at VMHoster’s infrastructure.

Step 2: Understanding the Difference Between Cancel and Terminate

PostgreSQL offers two functions for stopping sessions, and knowing when to use each is crucial:

pg_cancel_backend()

This function cancels the currently running query but keeps the session alive. Think of it as a gentle tap on the shoulder.

sql

SELECT pg_cancel_backend(pid);

Use this when:

• The query might be important, but you want it to stop temporarily
• You want to give the application a chance to handle the cancellation gracefully
• You’re dealing with a potentially expensive query that you want to interrupt

pg_terminate_backend()

This function forcefully terminates the entire session, disconnecting the client immediately. It’s the nuclear option.

sql

SELECT pg_terminate_backend(pid);

Use this when:

• pg_cancel_backend() didn’t work
• The session is truly stuck or unresponsive
• You need to immediately free up resources
• You’re dealing with zombie connections

Step 3: Killing a Single Session

Let’s say you’ve identified a problematic session with PID 12345. Here’s how to handle it:

Try Canceling First

Always start with the gentler approach:

sql

SELECT pg_cancel_backend(12345);

Wait 10-30 seconds and check if the query stopped:

sql

SELECT * FROM pg_stat_activity WHERE pid = 12345;

Terminate if Necessary

If the session is still running after cancellation, use terminate:

sql

SELECT pg_terminate_backend(12345);

The function returns true if successful, false if the PID doesn’t exist or you lack permissions.

Step 4: Killing Multiple Sessions at Once

Sometimes you need to clear out multiple problematic sessions. Here’s how to do it safely.

Terminate All Idle Connections

sql

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
    AND pid != pg_backend_pid();

The pid != pg_backend_pid() part ensures you don’t accidentally kill your own session!

Kill All Sessions from a Specific User

sql

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'problematic_user'
    AND pid != pg_backend_pid();

Terminate Sessions Blocking a Specific Query

When you need to clear blocking sessions:

sql

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid IN (
    SELECT blocking_pid
    FROM pg_blocking_pids(your_session_pid)
);

Common Issues and Troubleshooting

“Must be a superuser or have the pg_signal_backend role”

If you get this error, you lack the necessary permissions. You’ll need:

• Superuser privileges, or
• Membership in the pg_signal_backend role (PostgreSQL 10+)

Your DBA can grant this with:

sql

GRANT pg_signal_backend TO your_username;

Session Won’t Terminate

Sometimes even pg_terminate_backend() fails. This usually happens with:

• Sessions in certain wait states
• Background worker processes
• Replication connections

In extreme cases, you might need to restart the PostgreSQL service, though this should be a last resort.

Accidentally Killing Critical Sessions

Always double-check before running terminate commands. I recommend using transactions when testing:

sql

BEGIN;
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE ...;
-- Check the results first
ROLLBACK; -- or COMMIT if everything looks good

Best Practices for Session Management

From my experience managing databases, here are some practices that have saved me countless headaches:

Set Statement Timeouts: Configure statement_timeout in your postgresql.conf to automatically kill queries running too long:

sql

ALTER DATABASE your_database SET statement_timeout = '5min';

Monitor Regularly: Use monitoring tools like pgAdmin, pg_hero, or custom scripts to track session activity. At VMHoster, we have automated alerts for sessions running longer than defined thresholds.

Use Connection Pooling: Tools like PgBouncer help manage connections efficiently and prevent session accumulation.

Log Long Queries: Enable slow query logging in PostgreSQL to identify problematic queries before they become critical:

sql

ALTER SYSTEM SET log_min_duration_statement = '1000'; -- logs queries over 1 second

Document Your Process: Create a runbook for your team with clear guidelines on when and how to kill sessions.

Automating Session Management

For production environments, consider creating a monitoring function that automatically handles runaway queries:

sql

CREATE OR REPLACE FUNCTION kill_long_running_queries()
RETURNS void AS $$
BEGIN
    PERFORM pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE state = 'active'
        AND now() - query_start > interval '1 hour'
        AND pid != pg_backend_pid()
        AND usename != 'postgres'; -- protect superuser sessions
END;
$$ LANGUAGE plpgsql;

You can schedule this with pg_cron or an external cron job for regular cleanup.

Conclusion

Knowing how to kill a Postgres session is an essential skill for any database administrator or developer working with PostgreSQL. While it might seem intimidating at first, following the systematic approach outlined in this guide will help you handle problematic sessions confidently and safely.

Remember: always try pg_cancel_backend() before pg_terminate_backend(), verify the PID before terminating, and never kill your own session or critical system processes. With proper monitoring and proactive session management, you can maintain a healthy, performant PostgreSQL database.

Have you encountered challenging session management scenarios? What strategies have worked best for your PostgreSQL environment? Understanding these techniques will help you respond quickly when issues arise, minimizing downtime and keeping your applications running smoothly.