Kill a postgresql session/connection

by

You can use pg_terminate_backend() to kill a connection. You have to be superuser to use this function. This works on all operating systems the same. 

SELECT 
    pg_terminate_backend(pid) 
FROM 
    pg_stat_activity 
WHERE 
    -- don't kill my own connection!
    pid <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname="database_name"
    ;

Before executing this query, you have to REVOKE the CONNECT privileges to avoid new connections:

REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username;

If you’re using Postgres 8.4-9.1 use procpid instead of pid

SELECT 
    pg_terminate_backend(procpid) 
FROM 
    pg_stat_activity 
WHERE 
    -- don't kill my own connection!
    procpid <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname="database_name"
    ;

More Related Contents:

  1. Keep PostgreSQL from sometimes choosing a bad query plan
  2. Copying PostgreSQL database to another server
  3. Primary keys with Apache Spark
  4. How can I put a database under git (version control)?
  5. How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?
  6. Store query result in a variable using in PL/pgSQL
  7. Can I rollback a transaction I’ve already committed? (data loss)
  8. PostgreSQL: Drop PostgreSQL database through command line [closed]
  9. Do stored procedures run in database transaction in Postgres?
  10. Import SQL dump into PostgreSQL database
  11. how to use Blob datatype in Postgres
  12. How can I solve Postgresql SCRAM authentication problem?
  13. Convert SQLITE SQL dump file to POSTGRESQL
  14. Is it better to use multiple databases with one schema each, or one database with multiple schemas? [closed]
  15. How to login and authenticate to Postgresql after a fresh install?
  16. PostgreSQL Database Log Transaction
  17. Connect to a heroku database with pgadmin
  18. How do I automatically update a timestamp in PostgreSQL
  19. Starting and populating a Postgres container in Docker
  20. Adding a new value to an existing ENUM Type
  21. How can I import a .sql file into my Heroku postgres database?
  22. Backup/Restore a dockerized PostgreSQL database
  23. Display dynamic image from database or remote source with p:graphicImage and StreamedContent
  24. Change from SQLite to PostgreSQL in a fresh Rails project
  25. How to disable Django query cache?
  26. Deciding between an artificial primary key and a natural key for a Products table
  27. Django’s ManyToMany Relationship with Additional Fields
  28. How should international geographical addresses be stored in a relational database?
  29. INSERT COMMAND :: ERROR: column “value” does not exist
  30. I need a client side browser database. What are my options [closed]

Leave a Comment