PostgreSQL: Script to Kill Sessions

This is often used script to kill PostgreSQL session(s).

DBA Life Easy

pg_terminate_backend() AND pg_cancel_backend()

1) kill all connections  of  a specified database name.

-- List all available databases

postgres=>\l

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

2) kill all connections  of a specified user name.

postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)
             FROM pg_stat_activity
            WHERE usename = 'testuser'
              AND pid <> pg_backend_pid();

3) kill all sessions of current database.

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

4) kill a connection  of a specified pid.

postgres=> SELECT pg_terminate_backend(pid);

5) kill a running SQL of a session, but does not kill the session.

postgres=> SELECT pg_cancel_backend( pid );

It is useful when a running SQL is taking long time than expected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.