How to terminate PostgreSQL sessions

https://blog.sleeplessbeastie.eu/2014/07/23/how-to-terminate-postgresql-sessions/

 

Recently, I have encountered an interesting issue, as I could not perform specific database operations due to unwanted and active sessions using the database. Thus, I will briefly note the solution for further reference.

https://blog.sleeplessbeastie.eu/2014/07/23/how-to-terminate-postgresql-sessions/

 

Recently, I have encountered an interesting issue, as I could not perform specific database operations due to unwanted and active sessions using the database. Thus, I will briefly note the solution for further reference.

Prerequisites

This blog post is based on a Debian Wheezy and PostgreSQL 9.1 version.

I have deliberately written down this information here, as there are some minor differences between PostgreSQL versions, so please be aware of potential differences.

The problem and the solution

Sometimes you need to terminate connections initiated by badly behaving client application, or just make sure nobody is querying database during a major update.

The solution is to use pg_stat_activity view to identify and filter active database sessions and then use pg_terminate_backend function to terminate them.

To prevent access during an update process or any other important activity you can simplyrevoke connect permission for selected database users or alter pg_database system table.

Who is permitted terminate connections

Every database role with superuser rights is permitted to terminate database connections.

How to display database sessions

pg_stat_activity system view provides detailed information about server processes.

Sample output that will be used in the following examples.

How to terminate all connections to the specified database

Use the following query to terminate all connections to the specified database.

How to terminate all connections tied to specific user

Use the following query to terminate connections initiated by a specific user.

How to terminate all connections but not my own

To terminate every other database connection you can use process ID attached to the current session.

Alternatively, you can simply use username to filter out permitted connections.

Every example mentioned above can be extended to include more conditions like database name, client name, query, or even client address.

How to cancel running query

It is not always desired to abruptly terminate existing database connection, as you can just cancel running query using function shown in the following query.

How to prevent users from connecting to the database

Database connect privilege

To prevent connections from specific database user revoke the connect privilege for selected database.

To reverse this process use the GRANT statement.

Use the public keyword to specify every database user.

Database user login privilege

I did not mentioned it earlier but you can also use database user login privilege to disallow new connections.

To reverse this modification use the following query.

pg_database system table

Alternatively, you can alter pg_database system table to disallow new connections to specific database.

To reverse this process use the following query.

How to use the above-mentioned queries inside shell script

Use the postgres user to terminate connections..

Use role with superuser rights to terminate connections.

You can read more about providing password using environment variables in my previous article: How to non interactively provide password for the PostgreSQL interactive terminal.

pg_cancel_backend相关说明

https://www.postgresql.org/docs/9.2/static/functions-admin.html

点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注