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

Basis

When all other attempts failed, the pgreset gem somehow fixed rails/pg thinking a connection existed, which did not. – Coca

Answer 1

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

Prosthodontics

Note that in Postgres 9.2, procpid is renamed to pid. – Ceratoid

If he was a superuser, couldn't he have sudo-ed the kill anyway? – Unstressed

@ndn A database superuser is not the same thing as an OS level superuser. There is no sudo in PG. – Inmate

This is the only working answer for many SO questions because it has the REVOKE step. You saved someone, one more time I guess ! – Gramercy

I think you can use SELECT pg_terminate_backend(pid) to kill your own connections even if you're not a superuser, however, doing REVOKE CONNECT ON DATABASE is probably not the best idea for your own user :) – Revue

This solution does NOT work if you have pgAdmin4 opened and connected to the server. See the answer here for this problem: dba.stackexchange.com/questions/11893/… – Doloroso

SELECT pg_cancel_backend(pid); is a much safer alternative – Sphagnum

@cluis92: pg_cancel_backend() is a different function that does a different thing: It just cancels a current query, but it doesn't kill the database connection. It's not safer, it's just different. – Prosthodontics

hi, why is that we are not able to login after executing the above query to terminate all the current users? Now none of my users are able login to the db. The details can be found here #68411806 – Stria

This deserve a coffee – Averir

Answer 2

Maybe just restart postgres => sudo service postgresql restart

Bonina

@Starkers I went through most of answers above, until it dawned on me :) – Bonina

@Starkers Yes, especially safe in production under high load ;) – Husking

brew services restart postgresql if u have brew – Guillermoguilloche

ultimate solution, thanks. On Windows: type services on search bar -> find your postgresql service -> click postgresql service -> and then click restart the service on the left – Cubbyhole

Didn't solve my issue, but the idea was sound. – Luckett

Answer 3

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

Ochlophobia

Answer 4

MacOS, if postgresql was installed with brew:

brew services restart postgresql

Source: Kill a postgresql session/connection

Bassarisk

Answer 5

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.

Rosemarierosemary

Answer 6

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

Edna

Or brew services restart postgresql – Philoctetes

@Philoctetes Thanks for the utmost simple solution! I think your comment deserves to be a real answer, thus: https://mcmap.net/q/73367/-kill-a-postgresql-session-connection – Bassarisk

Thanks for that, @JuusoOhtonen. Tell you what though, if you want the reputation from it, you could at least link back to my comment? – Philoctetes

@Philoctetes Done. – Bassarisk

Had issues with other answers and other similar SO post solutions. Running your pg_ctl restart -D /usr/local/var/postgres did the trick! (I didn't even run first or third command). – Seif

Answer 7

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

Hotchpotch

Answer 8

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

Karp

This is great, just what I needed. The site was down, but my local GUI DB client was still connected! Disconnected that, and I was able to make the change. – Aleris

Answer 9

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.

Untitled

Answer 10

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

Agna

Answer 11

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+

Mcandrew

You need to use pg_stat_activity.procpid instead of pg_stat_activity.pid for Postgres 9.1 and below. See https://mcmap.net/q/53648/-how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it – Cymry

This is a great answer! It's better and safer than the Rails default. Thanks! – Dance

Answer 12

I'VE SOLVED THIS WAY:

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

Gratt

That is just doing a restart which is generally not desirable for production environments, killing the hug process is a much more desirable option. – Rhinehart

Answer 13

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.

Knightly

In my limited experience with Navicat Lite, simply closing the database or server connection is not always enough. Navicat Lite seems to keep the occasional connection open until the application is terminated. – Aft

Answer 14

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

Sheley

Answer 15

First, locate the PID of the query/connection you want to close (see How to list active connections on PostgreSQL?)

mydb=> select pid, application_name, state, query from pg_stat_activity where application_name = 'myapp';
 pid  | application_name | state | query 
------+------------------+-------+-------
 1234 | myapp            | idle  | 
 5678 | myapp            | idle  | 
(2 rows)

Then, closing the connection with the PID you picked like this

mydb=> SELECT pg_terminate_backend(1234);

Mccreery

Answer 16

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

Barbecue

Simple and works! To clarify further quite pgAdmin 4 and restart – Haversack

Answer 17

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

Landlocked

Answer 18

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.

Pouncey

Answer 19

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.

Sputnik

Answer 20

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

Grajeda

Answer 21

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

Aurore

Answer 22

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.

Mitigate

Answer 23

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.

Spectroradiometer

Answer 24

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'

Weymouth

downvoted, this question is not releated to gitlab – Mirna

Answer 25

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

Washstand