Question

I ve got one Posgresql database (I m the owner) and I d like to drop it and re-create it from a dump.

Problem is, there re a couple applications (two websites, rails and perl) that access the db regularly. So I get a "database is being accessed by other users" error.

I ve read that one possibility is getting the pids of the processes involved and killing them individually. I d like to do something cleaner, if possible.

Phppgadmin seems to do what I want: I am able to drop schemas using its web interface, even when the websites are on, without getting errors. So I m investigating how its code works. However, I m no PHP expert.

I m trying to understand the phppgadmin code in order to see how it does it. I found out a line (257 in Schemas.php) where it 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 s using your database:

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

Second, kill the processes you don t want:

SELECT pg_terminate_backend(your_procpid);

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

To quickly drop all connections connected to a given database, this shortcut works nicely. Must run as superuser:

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

On more recent Postgres versions (at least 9.2+, likely earlier), the column names have changed and the query is:

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

Answer 2

Not sure about PostgreSQL but i think a possible solution would be to lock the table so other processes will fail when they try to access it.

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

友情链接