Kill a postgresql session/connection

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'
    ;

---

Maybe just restart postgres => sudo service postgresql restart

---

With all infos about the running process:

SELECT *, pg_terminate_backend(pid)
FROM pg_stat_activity 
WHERE pid <> pg_backend_pid()
AND datname = 'my_database_name';

---

MacOS, if postgresql was installed with brew:

brew services restart postgresql

Source: Kill a postgresql session/connection

---

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

---

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.

---

SELECT 
pg_terminate_backend(pid) 
FROM 
pg_stat_activity 
WHERE
pid <> pg_backend_pid()
-- no need to kill connections to other databases
AND datname = current_database();
-- use current_database by opening right query tool

---

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' => 'postgres', 'schema_search_path' => 'public'))
    ActiveRecord::Base.connection.select_all("select * from pg_stat_activity order by procpid;").each do |x|
      if config['database'] == x['datname'] && x['current_query'] =~ /<IDLE>/
        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.

---

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+

---

I'VE SOLVED THIS WAY:

In my Windows8 64 bit, just restarting the service: postgresql-x64-9.5

---

MacOS, if postgresql was installed with brew:

brew services restart postgresql

UBUNTU,

firstly check with this (kill server which is running in background)

sudo kill -9 $(lsof -i :3000 -t)

if you didn't find pid Then you just need to restart Postgresql service by command which is mention are as under:

sudo service postgresql restart

---

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.

---

If you need to disconnect sessions of a particular user, this helped me:

Check all current connections:

select * from pg_stat_activity; 

Grant a role to your user (not important):

set role "db_admin";

Kill sessions:

select pg_terminate_backend(pid)
from pg_stat_activity
where usename = '*** USER NAME TO DISCONNECT ***';

---

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

---

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

---

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.

---

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.

---

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

---

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.

---

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 => :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 => :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 => :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

---

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 <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname = 'database_name'
    ;

---

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'

---

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.

---

the answer is hidden in one of the comments above: brew services restart postgresql

---