1

I need to delete a database. But the database cannot be deleted due to some active sessions using the database. I then run the command

psql -c "select pg_terminate_backend(procpid) from pg_stat_activity where procpid<> pg_backend_pid() and datname='mydb"
 pg_terminate_backend 
----------------------
 t
 t
 t
 t
 (4 rows)

to delete the sessions. However, the sessions come back with different process ids.

psql -c "select * from pg_stat_activity where datname='mydb';"
 datid  |   datname   | procpid | usesysid |   usename   | current_query |    waiting | xact_start |          query_start          |         backend_start         | client_addr | client_port 
--------+-------------+---------+----------+-------------+---------------+---------+------------+-------------------------------+-------------------------------+-------------+-------------
 220030 | mydb |   25454 |   189669 | myname | <IDLE>        | f       |            | 2018-05-17 15:13:04.741252-05 | 2018-05-17 15:12:12.480389-05 | ::1         |       59540
 220030 | mydb |   25475 |   189669 | myname | <IDLE>        | f       |            | 2018-05-17 15:13:04.903452-05 | 2018-05-17 15:12:12.559564-05 | ::1         |       59542
 220030 | mydb |   25639 |   189669 | myname | <IDLE>        | f       |            | 2018-05-17 15:13:04.731736-05 | 2018-05-17 15:12:12.969821-05 | ::1         |       59546
 220030 | mydb |   25640 |   189669 | myname | <IDLE>        | f       |            | 2018-05-17 15:13:04.735792-05 | 2018-05-17 15:12:12.984415-05 | ::1         |       59548

(4 rows)

My PostgreSQL is 8.4.20. Why do the connections keep on comming back? How can I completely remove them?

  • The corresponding client is probably detecting the loss of connection and is just reconnecting. You should first make sure the given username can not login anymore and/or the specific schema is not open anymore to connection, and then close their connection, so that they are not able to reconnect. Or track which client this is and just stop the client. BTW you should upgrade, end of life of 8.4 was 4 years ago. – Patrick Mevzek May 17 '18 at 20:40
  • 1
    You should check this post if you haven't. – Sahap Asci May 18 '18 at 10:35
1

It seems there is an always-running program that reconnects to this database as soon as it's disconnected.

Before terminating the connections, you might prevent this from happening again with:

REVOKE CONNECT ON DATABASE mydb FROM public, myname;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.