Kill a postgresql session/connection

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

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

I'VE SOLVED THIS WAY:

In my **Windows8 64** bit, just `restart`ing the service: **postgresql-x64-9.5**

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:

[To see links please register here]

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

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.

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.

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