Phppgadmin: How does it push users out of postgres, so can it db_drop?

Question

I have one Posgresql database (I own it), and I would like to reset it and recreate it.

The problem is that there are several applications (two websites, rails and perl) that regularly access db. Therefore, I get the error "Access to the database to other users".

I read that one of the possibilities is to get parts of processes and kill them separately. I would like to do something cleaner if possible.

Phppgadmin seems to do what I want: I can opt out of schemes using my web interface, even when websites are turned on, without errors. So I study how its code works. However, I am not a PHP expert.

I am trying to understand phppgadmin code to see how it does it. I recognized the line (257 in Schemas.php) that says:

$data->dropSchema(...) 

$data is a global variable, and I could not find where it is defined.

Any pointers would be greatly appreciated.

Answer 1

First find all current procesid using your database:

 SELECT usename, procpid FROM pg_stat_activity WHERE datname = current_database(); 

Secondly, kill processes that you don't need:

 SELECT pg_terminate_backend(your_procpid); 

This works with version 8.4, otherwise pg_terminate_backend () is unknown and you need to kill the process at OS level.

---

To quickly remove all connections connected to this database, this shortcut works beautifully. Should work as superuser:

 SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname='YourDB'; 

---

In later versions of Postgres (at least 9.2+, most likely earlier), the column names changed and the query:

 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='YourDB'; 

Answer 2

I'm not sure about PostgreSQL, but I think a possible solution would be to lock the table so that other processes do not work when they try to access it.

See: http://www.postgresql.org/docs/current/static/sql-lock.html