On Apr 6, 1:34 pm, cr...@xxxxxxxxxxxxxxxxxxxxx (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
