Re: stopping processes, preventing connections

On Mar 3, 2010, at 18:01 , Josh Kupershmidt wrote:

>
> On Wed, Mar 3, 2010 at 8:31 AM, Herouth Maoz <herouth(at)unicell(dot)co(dot)il> wrote:
>
> First, the easy part - regarding allowing/disallowing queries. Is it possible to GRANT or REVOKE access to tables based on the originating IP?
>
> I'd suggest separating out access to your tables by roles, and then restricting those roles to certain IP ranges in pg_hba.conf.

Thank you. I guess I will go for something simple - I'll give the lady in charge of the reports machine a new user/password to use, and revoke that user's access. I was hoping to avoid her needing to change settings in Windows, but it seems to be the easiest way.

>
> Second, and the more complicated one - what do I do about rogue queries that are running when my process starts? Today we had a query that ran since yesterday. I called pg_cancel_backend() on it several times and waited for almost two hours - to no avail. Eventually I had to ask our sysadmin to shutdown PostgreSQL, which took some five minutes, but eventually worked. Is there a way to do the same thing to a single process without shutting down the whole server, and without causing any harm to the database or memory corruption? Something I can call from within SQL? I run the nightly script from a linux user which is not "postgres", so I'd prefer a way that doesn't require using "kill".
>
>
> On 8.4, you can use pg_terminate_backend(), which sends a SIGTERM instead of pg_cancel_backend's SIGINT. If you're not on 8.4, you can just do this manually with a "kill -SIGTERM backend_pid". If that doesn't work either, you might have to resort to a "kill -SIGKILL backend_pid". Killing a single backend should be much better for you than restarting Postgres entirely. These operations shouldn't result in database corruption.
>
> You have to be database superuser to use pg_cancel_backend() or pg_terminate_backend(), or have a shell login as the database user to use "kill". No way around that for now.

Unfortunately, we only have 8.3 - is SIGTERM safe on 8.3?

I guess I'll have to sudo or use local ssh.

>
> Though next time you see a query which doesn't respond to pg_cancel_backend(), try gathering information about the query and what the backend is doing; either you're doing something unusual (e.g. an app is restarting the query automatically after getting canceled) or perhaps you've stumbled on a bug in Postgres.

I'd appreciate it if you tell me what to look for. It was running a join on several tables, but nothing too complicated. It may be that the query is not optimized (one of the tables is not indexed properly) but it still should respond to cancel - shouldn't it?

Thank you very much,
Herouth