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

When all other attempts failed, the pgreset gem somehow fixed rails/pg thinking a connection existed, which did not. — JosephK, Jun 10 '20 at 14:48

score 993 · Accepted Answer · edited May 20 '16 at 22:54

993

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

edited May 20 '16 at 22:54

notpeter

1,046
11
16

answered Feb 24 '11 at 19:00

Frank Heikens

117,544
24
142
135

85

Note that in Postgres 9.2, procpid is renamed to pid. – Devin Dec 04 '12 at 22:39
1

If he was a superuser, couldn't he have `sudo`-ed the kill anyway? – ndnenkov Mar 11 '16 at 14:45
3

@ndn A database superuser is not the same thing as an OS level superuser. There is no `sudo` in PG. – jpmc26 Jul 29 '16 at 15:36
This is the only working answer for many SO questions because it has the `REVOKE` step. You saved someone, one more time I guess ! – AymDev Aug 15 '19 at 22:07
1

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 :) – Peter Kilczuk Nov 25 '20 at 10:32
This solution does NOT work if you have pgAdmin4 opened and connected to the server. See the answer here for this problem: https://dba.stackexchange.com/questions/11893/force-drop-db-while-others-may-be-connected – M46 Feb 24 '21 at 15:37
SELECT pg_cancel_backend(__pid__); is a much safer alternative – cluis92 Mar 15 '21 at 22:43
@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. – Frank Heikens Mar 16 '21 at 08:58
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 https://stackoverflow.com/questions/68411306/how-to-login-to-postgresql-db-after-session-kill-for-copy-database – The Great Jul 16 '21 at 15:20
*This* deserve a coffee – SuperPoney Oct 29 '21 at 09:51

score 271 · Answer 2 · answered May 13 '14 at 21:22

271

Maybe just restart postgres => sudo service postgresql restart

answered May 13 '14 at 21:22

Haris Krajina

14,824
12
64
81

@Starkers I went through most of answers above, until it dawned on me :) – Haris Krajina Aug 18 '14 at 13:35
62

@Starkers Yes, especially safe in production under high load ;) – Erathiel Apr 05 '16 at 06:22
19

`brew services restart postgresql` if u have brew – Sam Kah Chiin Jun 20 '18 at 07:34
4

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 – alramdein Dec 17 '20 at 06:58
1

Didn't solve my issue, but the idea was sound. – Loïc N. Aug 10 '21 at 14:03

Dorian · Answer 3 · 2017-03-01T08:23:13.583

65

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

edited Mar 01 '17 at 08:23

answered Jan 11 '17 at 17:54

Dorian

22,759
8
120
116

Juuso Ohtonen · Answer 4 · 2018-01-15T05:38:00.137

28

MacOS, if postgresql was installed with brew:

brew services restart postgresql

Source: Kill a postgresql session/connection

edited Jan 15 '18 at 05:38

answered Jan 12 '18 at 12:52

Juuso Ohtonen

8,826
9
65
98

score 19 · Answer 5 · edited Jul 15 '19 at 20:39

19

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.

edited Jul 15 '19 at 20:39

Jake Worth

5,490
1
25
35

answered Jun 18 '19 at 18:18

Mr. Rene

1,167
10
17

score 14 · Answer 6 · answered Feb 26 '13 at 16:17

14

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

answered Feb 26 '13 at 16:17

artemave

6,786
7
47
71

7

Or `brew services restart postgresql` – PJSCopeland Oct 25 '17 at 01:03
@PJSCopeland Thanks for the utmost simple solution! I think your comment deserves to be a real answer, thus: https://stackoverflow.com/a/48226667/1097104 – Juuso Ohtonen Jan 12 '18 at 12:52
Thanks for that, @JuusoOhtonen. Tell you what though, if you want the reputation from it, you could at least link back to my comment? – PJSCopeland Jan 14 '18 at 20:54
@PJSCopeland Done. – Juuso Ohtonen Jan 15 '18 at 05:40
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). – Iggy Feb 23 '19 at 17:13

score 13 · Answer 7 · answered Jun 19 '18 at 21:23

13

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

answered Jun 19 '18 at 21:23

Janki

191
1
8

score 9 · Answer 8 · answered Oct 19 '21 at 16:00

9

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

answered Oct 19 '21 at 16:00

eku-code

111
1
4

1

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. – Nick K9 Oct 29 '22 at 14:49

score 8 · Answer 9 · answered Oct 29 '21 at 12:18

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

score 8 · Answer 10 · edited May 23 '17 at 11:47

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.

Chris Aitchison · Answer 11 · 2013-05-10T00:18:28.953

6

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+

edited May 10 '13 at 00:18

answered Mar 31 '13 at 12:39

Chris Aitchison

4,656
1
27
43

You need to use `pg_stat_activity.procpid` instead of `pg_stat_activity.pid` for Postgres 9.1 and below. See http://stackoverflow.com/a/5408501/444774 – talyric May 09 '13 at 23:44
1

This is a great answer! It's better and safer than the Rails default. Thanks! – piersadrian Jul 12 '13 at 23:12

score 5 · Answer 12 · answered Nov 13 '16 at 05:47

5

I'VE SOLVED THIS WAY:

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

answered Nov 13 '16 at 05:47

X-Coder

2,632
2
19
17

6

That is just doing a restart which is generally not desirable for production environments, killing the hug process is a much more desirable option. – BrianC Apr 01 '17 at 15:54

score 4 · Answer 13 · edited May 23 '17 at 12:18

4

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.

edited May 23 '17 at 12:18

Community

1
1

answered Jun 12 '12 at 21:50

Jamon Holmgren

23,738
6
59
75

1

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. – ken Jun 05 '13 at 13:32

score 3 · Answer 14 · answered Oct 29 '19 at 14:53

3

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

answered Oct 29 '19 at 14:53

Alex Binzar

31
4

score 2 · Answer 15 · answered Sep 22 '16 at 13:02

2

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

answered Sep 22 '16 at 13:02

Stan Amsellem

503
1
5
16

Simple and works! To clarify further quite pgAdmin 4 and restart – Ka Tech Apr 08 '17 at 02:12

score 1 · Answer 16 · answered Jan 22 '15 at 09:04

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

score 1 · Answer 17 · answered May 28 '15 at 20:54

1

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.

answered May 28 '15 at 20:54

mlabarca

796
7
16

score 1 · Answer 18 · answered Mar 08 '16 at 18:01

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.

score 1 · Answer 19 · edited Jul 04 '16 at 10:47

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

score 1 · Answer 20 · answered Jan 28 '19 at 20:59

1

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

answered Jan 28 '19 at 20:59

Juan José Ramírez

639
6
10

score 1 · Answer 21 · answered Apr 02 '19 at 18:39

1

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.

answered Apr 02 '19 at 18:39

score 1 · Answer 22 · answered Jan 05 '21 at 12:11

1

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.

answered Jan 05 '21 at 12:11

Jesús Vera

321
2
5

score 1 · Answer 23 · answered Mar 21 '23 at 21:38

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

score -1 · Answer 24 · answered Nov 18 '19 at 15:32

-1

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'

answered Nov 18 '19 at 15:32

Kostas Demiris

3,415
8
47
85

downvoted, this question is not releated to gitlab – Vokail Nov 18 '22 at 14:01

score -5 · Answer 25 · answered May 21 '21 at 08:32

-5

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

answered May 21 '21 at 08:32

Thomas Van Holder

1,137
9
12

Kill a postgresql session/connection

25 Answers25

Linked