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
