[database] Kill a postgresql session/connection

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

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.

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

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.

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

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.

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'

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.

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.

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

Maybe just restart postgres => sudo service postgresql restart

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

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

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+

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

Easier and more updated way is:

1. Use ps -ef | grep postgres to find the connection #
2. sudo kill -9 "#" of the connection

Note: There may be identical PID. Killing one kills all.

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

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

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.

MacOS, if postgresql was installed with brew:

brew services restart postgresql

Source: Kill a postgresql session/connection

I'VE SOLVED THIS WAY:

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