Thread: drop database regardless of connections
Hi everyone,
I'd like to drop a database to which users have idle connections. I'm
running pgsql 8.2 on win32, soon 8.3, using mod_perl2 and
connect_cached.
I know this issue has been brought up several times over the past few
years, but I don't quite understand the reason for it not being
supported as an sql command or command-line utility.
Over on pgsql.hackers Stuart Bishop posted this code:
> CREATE OR REPLACE FUNCTION _killall_backends(text)
> RETURNS Boolean AS $$
> import os
> from signal import SIGTERM
>
> plan = plpy.prepare(
> "SELECT procpid FROM pg_stat_activity WHERE datname=$1",
['text']
> )
> success = True
> for row in plpy.execute(plan, args):
> try:
> plpy.info("Killing %d" % row['procpid'])
> os.kill(row['procpid'], SIGTERM)
> except OSError:
> success = False
>
> return success
> $$ LANGUAGE plpythonu;
However, I'd like to avoid installing Python just for this task, if
possible. So I tried to do this in Perl, but for some reason neither
kill() nor Win32::Process::KillProcess() actually terminate the
threads. I also tried clearing CachedKids, but that doesn't seem to
have any effect. So even with no client apps running, I am unable to
drop the database without restarting the whole database service.
Does this mean I have to run a separate database server for each
database just to be able to boot people off a particular database
before dropping it? It seems like a basic thing to be able to drop a
database unconditionally without restarting the service for people who
are using other databases on the same service. Maybe I'm missing
something that everyone else who asked about this eventually realized?
Thanks,
Kev
Kev wrote: > So I tried to do this in Perl, but for some reason neither > kill() nor Win32::Process::KillProcess() actually terminate the > threads. Threads? Each backend is a distinct process. I haven't the foggiest why they might be ignoring the signal, but then I'm very far from clueful about Pg on win32. Anyway, you can use `psql' to query the activity tables using something like "SELECT procpid FROM pg_stat_activity WHERE datname = 'dbtodrop'" and see which backend pids need to be killed, then use 'pg_ctl kill signame pid' to kill them. A bit of powershell, cmd.exe, etc should do the job, though I agree that for win32 a builtin "pg_kill_backend()" function would be nicer, in that you could just execute a query like: SELECT pg_kill_backend(procpid) FROM pg_stat_activity WHERE datname = 'dbtodrop'; You can use pg_cancel_backend() to cancel queries, but there doesn't seem to be an equivalent to actually disconnect / terminate a backend. Note that you can also update the system tables to prevent new connections being made to the database you're about to drop by setting pg_database.datallowconn to 'f' for the DB in question. That way, while you're killing off backends you won't have more joining. I'm curious about why you need to drop and create so many databases that this is an issue, though. -- Craig Ringer
On Apr 6, 1:34 pm, cr...@postnewspapers.com.au (Craig Ringer) wrote: > Kev wrote: > > So I tried to do this in Perl, but for some reason neither > > kill() nor Win32::Process::KillProcess() actually terminate the > > threads. > > Threads? Each backend is a distinct process. I haven't the foggiest why > they might be ignoring the signal, but then I'm very far from clueful > about Pg on win32. Ah, sorry, I meant processes. > Anyway, you can use `psql' to query the activity tables using something > like "SELECT procpid FROM pg_stat_activity WHERE datname = 'dbtodrop'" > and see which backend pids need to be killed, then use 'pg_ctl kill > signame pid' to kill them. A bit of powershell, cmd.exe, etc should do > the job, though I agree that for win32 a builtin "pg_kill_backend()" > function would be nicer, in that you could just execute a query like: Oh, of course! I'll go try that. For some reason I had forgotten about pg_ctl. > SELECT pg_kill_backend(procpid) > FROM pg_stat_activity > WHERE datname = 'dbtodrop'; > > You can use pg_cancel_backend() to cancel queries, but there doesn't > seem to be an equivalent to actually disconnect / terminate a backend. Exactly... > Note that you can also update the system tables to prevent new > connections being made to the database you're about to drop by setting > pg_database.datallowconn to 'f' for the DB in question. That way, while > you're killing off backends you won't have more joining. Ah, this is new...thanks for the tip! > I'm curious about why you need to drop and create so many databases that > this is an issue, though. Well, frankly, it was just the *one* time that I was having enough trouble with, but I also wanted to automate it so that I could do things like refresh our sandbox database easily, and test going live with my development database--drop it, load a copy of the production one onto it, and then apply all the updates I had done since then. Kev
> > Anyway, you can use `psql' to query the activity tables using something
> > like "SELECT procpid FROM pg_stat_activity WHERE datname = 'dbtodrop'"
> > and see which backend pids need to be killed, then use 'pg_ctl kill
> > signame pid' to kill them. A bit of powershell, cmd.exe, etc should do
> > the job, though I agree that for win32 a builtin "pg_kill_backend()"
> > function would be nicer, in that you could just execute a query like:
Yep, pg_ctl did the trick in the end. Thanks!
$ENV{PATH} = ''; # this automatically gets reset (and retainted) after
this script finishes
local our $sth = $dbh->prepare("SELECT procpid FROM pg_stat_activity
WHERE datname=? order by procpid") or die("Couldn't prepare statement:
" . $dbh->diestr);
$sth->execute($ENV{db}) or die("Couldn't execute statement: " . $sth-
>diestr);
local our @data;
while (@data = $sth->fetchrow_array())
{
$r->print("Killing $data[0]...<br>");
system('"c:\program files\postgresql\8.3\bin\pg_ctl.exe" kill TERM
' . $data[0]) == 0 or die("Couldn't kill process $data[0].");
}
$sth->finish;
$r->print("All done.");
Kev