How to Kill a Session in PostgreSQL

You can terminate a problematic session using the pg_terminate_backend function.

SELECT pg_terminate_backend(PID);

Identifying Long-Running Queries

Before terminating a session in PostgreSQL, you need to know which session to target. To find long-running queries, you can use the pg_stat_activity view and filter by the state and query_start columns.

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

Expected Output:

Terminating a Session

Once you have identified the problematic session, you can terminate it using the pg_terminate_backend function.

SELECT pg_terminate_backend(1236);

This command forcibly terminaties the connection to the backend. It sends a SIGTERM signal to the process and allows it to terminate safely, offering a final chance to clean up before it terminates. Before executing this command, make sure to check the nature of the running queries and consider the output.

Automating Session Termination

For environments where session issues are frequent, you can automate session termination using a script. Here is an example of a basic script to terminate long-running queries:

DO $$ 
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT pid 
        FROM pg_stat_activity 
        WHERE state = 'active' 
        AND now() - query_start > interval '10 minutes'
    LOOP
        PERFORM pg_terminate_backend(r.pid);
    END LOOP;
END $$;

This script will terminate any active session that has been running for more than 10 minutes.

Using the pg_cancel_backend function

Sometimes, it’s not desirable to kill a session but to simply cancel the query that the session is executing. In such cases, you can use the pg_cancel_backend function.

SELECT pg_cancel_backend(PID);

Replace PID with the process ID of the session with the query you want to cancel.

This command sends a SIGINT to the process to cancel the query. It’s less brute force compared to pg_terminate_backend, and allows the session itself to remain alive, even though the running query gets cancelled.

Remember, that both pg_cancel_backend and pg_terminate_backend have the superuser access requirement. If the session you are trying to terminate or cancel belongs to a superuser, you will need superuser privileges to run these commands successfully.

Conclusion

You now know how to kill sessions in PostgreSQL. Managing sessions in PostgreSQL is an important skill for database administrators. By effectively identifying and terminating problematic sessions, you can better maintain the performance and reliability of your PostgreSQL databases. Remember to use these commands judiciously, as their misuse can interrupt important data processing steps and compromise the integrity of your PostgreSQL databases.

Other posts you might enjoy:

• How to Drop Constraints in PostgreSQL
• Understanding PostgreSQL Information_Schema Views