Archive for mai 2010

PostgreSQL : kick everyone but me

postgsql logoSometimes as a postgresql administrator, you need to drop a database during development or do some dirty things with your database.

A database connot be deleted if there is connections on it. And it is not possible to simply kick an user. The recommanded way for closing the connection beetwen a client and the postgresql server is to log in on the server, look for the postgresql process running for the specific connection, and then kill it.

The kill_process function

This stored function just kills a process on the server, using its pid. As it is quite dangerous, you should have a strict policy on who has the right to run it and who doesn’t, because letting anyone kill random processes on the postgreql server is a GIGANTIC security hole !

The function is written using the pl/perl procedure language. use createlang, or the specific SQL commands above to install it.
createlang plperlu (see 'man createlang')

create or replace function kill_process(signum integer, pid integer) returns integer as $$
my ($signum, $pid) = @_;
$cnt = kill $signum, $pid;
return $cnt;
$$ language plperlu;

It returns 1 when the process has been signaled, 0 when there is no matching pid.

Kill every connection from a particular database user :

you can use the pg_stat_activity view in order to know who is connected to which database, and the pid of each process. If you want to kill, let say, every connection for the user ‘robert’ :

select kill_process(6, procpid) from pg_stat_activity where usename = 'robert'

In most database systems, the same db user is used for many connections from different clients, so a more practical solution would be to kick every connection made to a particular database :

select kill_process(6, procpid) from pg_stat_activity where datname = 'My_Database'

Kick everyone but me

If you are really upset and want to kick everyone (but yourself, off course), type in this query :

select kill_process(6, procpid) from pg_stat_activity where procpid <> pg_backend_pid();

YOU are the administrator, and you database engine owe you some respect. That’s said.

Catégories :Uncategorized Étiquettes : , ,