Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Terminate Postgresql Sessions

This blog post is based on a PostgreSQL 9.1 version.
problems:-
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 simply revoke 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.
SELECT datname as database,
      procpid as pid,
      usename as username,
      application_name as application,
      client_addr as client_address,
      current_query
 FROM pg_stat_activity

Sample output that will be used in the following examples.
database | pid  | username | application | client_address |                                                                           current_query
----------+------+----------+-------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
blog     | 8603 | blog     | blog_app    | 192.168.3.11   | select * from posts order by pub_date
postgres | 8979 | postgres | psql        |                | select datname as database,procpid as pid,usename as username,application_name as application, client_addr as client_address, current_query from pg_stat_activity
wiki     | 8706 | wiki     | wiki_app    | 192.168.3.8    |
(3 rows)


How to terminate all connections to the specified database
Use the following query to terminate all connections to the specified database.
SELECT pg_terminate_backend(procpid)
 FROM pg_stat_activity
WHERE datname = 'wiki'

Comments

Popular posts from this blog

PostgreSQL pgBadger

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Database startup / shutdown /restart