Search Postgresql Archives

Re: drop database regardless of connections

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux