Kill a postgresql session connection

Question

How can I kill all my postgresql connections   I m trying a rake db drop but I get   ERROR   database  database name  is being accessed by other users DETAIL   There are 1 other session s  using the database    I ve tried shutting down the processes I see from a ps -ef   grep postgres but this doesn t work either   kill  kill 2358 failed  operation not permitted

User · Answer

I use the following rake task to override the Rails drop_database method.

lib/database.rake

require 'active_record/connection_adapters/postgresql_adapter'
module ActiveRecord
  module ConnectionAdapters
    class PostgreSQLAdapter < AbstractAdapter
      def drop_database(name)
        raise "Nah, I won't drop the production database" if Rails.env.production?
        execute <<-SQL
          UPDATE pg_catalog.pg_database
          SET datallowconn=false WHERE datname='#{name}'
        SQL

        execute <<-SQL
          SELECT pg_terminate_backend(pg_stat_activity.pid)
          FROM pg_stat_activity
          WHERE pg_stat_activity.datname = '#{name}';
        SQL
        execute "DROP DATABASE IF EXISTS #{quote_table_name(name)}"
      end
    end
  end
end

Edit: This is for Postgresql 9.2+

User · Answer

I m on a mac and I use postgres via Postgres app  I solved this problem just quitting and starting again the app

User · Answer

Quit postgres and restart it  Simple  but works every time for me  where other cli commands sometimes don t

User · Answer

Maybe just restart postgres    sudo service postgresql restart

User · Answer

Easier and more updated way is     Use ps -ef   grep postgres to find the connection    sudo kill -9     of the connection   Note  There may be identical PID  Killing one kills all

User · Answer

Case   Fail to execute the query    DROP TABLE dbo t tabelname   Solution   a  Display query Status Activity as follow    SELECT   FROM pg stat activity      b  Find row where  Query   column has contains     DROP TABLE dbo t tabelname    c  In the same row  get value of  PID  Column     example   16409   d  Execute these scripts    SELECT      pg terminate backend 25263   FROM      pg stat activity  WHERE      -- don t kill my own connection      25263  lt  gt  pg backend pid       -- don t kill the connections to other databases     AND datname    database name

User · Answer

Definitely one of the answers above gave me the idea for solving it in Windows  Open the Services from Windows  locate the Postgres service and restart it

User · Answer

This seems to be working for PostgreSQL 9 1     Rails root  lib tasks databases rake   monkey patch ActiveRecord to avoid There are n other session s  using the database  def drop database config    case config  adapter     when  mysql      ActiveRecord  Base establish connection config      ActiveRecord  Base connection drop database config  database     when  sqlite      require  pathname      path   Pathname new config  database        file   path absolute    path to s   File join Rails root  path       FileUtils rm file    when  postgresql      ActiveRecord  Base establish connection config merge  database    gt   postgres    schema search path    gt   public        ActiveRecord  Base connection select all  select   from pg stat activity order by procpid    each do  x        if config  database      x  datname    amp  amp  x  current query        lt IDLE gt           ActiveRecord  Base connection execute  select pg terminate backend   x  procpid             end     end     ActiveRecord  Base connection drop database config  database     end end   Lifted from gists found here and here   Here s a modified version that works for both PostgreSQL 9 1 and 9 2

User · Answer

For me worked the following   sudo gitlab-ctl stop sudo gitlab-ctl start gitaly sudo gitlab-rake gitlab setup  type yes and let it finish  sudo gitlab-ctl start   I am using  gitlab edition   gitlab-ce  gitlab version   12 4 0-ce 0 el7

User · Answer

With all infos about the running process   SELECT    pg terminate backend pid  FROM pg stat activity  WHERE pid  lt  gt  pg backend pid   AND datname    my database name

User · Answer

In PG admin you can disconnect your server  right click on the server   amp  all sessions will be disconnected at restart

User · Answer

MacOS  if postgresql was installed with brew   brew services restart postgresql   Source  Kill a postgresql session connection

User · Answer

Open PGadmin see if there is any query page open  close all query page and disconnect the PostgresSQL server and Connect it again and try delete drop option This helped me

User · Answer

SELECT  pg terminate backend pid   FROM  pg stat activity  WHERE pid  lt  gt  pg backend pid   -- no need to kill connections to other databases AND datname   current database    -- use current database by opening right query tool

User · Answer

Just wanted to point out that Haris s Answer might not work if some other background process is using the database  in my case it was delayed jobs  I did   script delayed job stop   And only then I was able to drop reset the database

User · Answer

I VE SOLVED THIS WAY   In my Windows8 64 bit  just restarting the service  postgresql-x64-9 5

User · Answer

OSX  Postgres 9 2  installed with homebrew     launchctl unload -w   Library LaunchAgents homebrew mxcl postgresql plist   pg ctl restart -D  usr local var postgres   launchctl load -w   Library LaunchAgents homebrew mxcl postgresql plist    If your datadir is elsewhere you can find out where it is by examining the output of ps aux   grep postgres

User · Answer

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  lt  gt  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  lt  gt  pg backend pid       -- don t kill the connections to other databases     AND datname    database name

User · Answer

There is no need to drop it  Just delete and recreate the public schema  In most cases this have exactly the same effect   namespace  db do  desc  Clear the database  task  clear db   gt   environment do  t args    ActiveRecord  Base establish connection   ActiveRecord  Base connection tables each do  table      next if table     schema migrations      ActiveRecord  Base connection execute  TRUNCATE   table      end end  desc  Delete all tables  but not the database   task  drop schema   gt   environment do  t args    ActiveRecord  Base establish connection   ActiveRecord  Base connection execute  DROP SCHEMA public CASCADE     ActiveRecord  Base connection execute  CREATE SCHEMA public     ActiveRecord  Base connection execute  GRANT ALL ON SCHEMA public TO postgres     ActiveRecord  Base connection execute  GRANT ALL ON SCHEMA public TO public     ActiveRecord  Base connection execute  COMMENT ON SCHEMA public IS  standard public schema    end  desc  Recreate the database and seed  task  redo db   gt   environment do  t args      Executes the dependencies  but only once   Rake  Task  db drop schema   invoke   Rake  Task  db migrate   invoke   Rake  Task  db migrate status   invoke    Rake  Task  db structure dump   invoke   Rake  Task  db seed   invoke end  end

User · Answer

I had this issue and the problem was that Navicat was connected to my local Postgres db  Once I disconnected Navicat the problem disappeared   EDIT   Also  as an absolute last resort you can back up your data then run this command   sudo kill -15  ps -u postgres -o pid        which will kill everything that the postgres user is accessing  Avoid doing this on a production machine but you shouldn t have a problem with a development environment  It is vital that you ensure every postgres process has really terminated before attempting to restart PostgreSQL after this   EDIT 2   Due to this unix SE post I ve changed from kill -9 to kill -15

User · Answer

Remote scenario  But if you re trying to run tests in a rails app  and you get something like   ActiveRecord  StatementInvalid  PG  ObjectInUse  ERROR   database  myapp test  is being accessed by other users DETAIL   There is 1 other session using the database    Make sure you close pgAdmin or any other postgres GUI tools before running tests

[database] Kill a postgresql session/connection

Examples related to database

Examples related to postgresql