Re: connection timeouts and "killing" users

Yes, issue a kill on the pid from the command line as either postgres
or root. note I didn't say kill -9 there.

How do I get the pids? Is there something specific I should look for in
the executable name I can see in "ps"?

Will I break any remote server processes which are handeling remote
attaches if I do this?

-----Original Message-----
From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
Sent: Thursday, September 11, 2008 3:03 PM
To: Gauthier, Dave
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] connection timeouts and "killing" users

On Thu, Sep 11, 2008 at 10:42 AM, Gauthier, Dave
<dave(dot)gauthier(at)intel(dot)com> wrote:
> Hi:
>
>
>
> Here's the problem...
>
>
>
> I have a read-only DB that gets reloaded from scratch every night.
This
> takes several hours and I don't want any late night users to have to
wait
> for this process to complete, so I have 2 DBs. The first DB is the
one the
> users access. Call it "main_db". I load a second DB which has an
identical
> architecture at night. Call it "standby_db". When the load finishes,
I
> rename "main_db" to "tmp", then rename "standby_db" to "main_db", then
> rename "tmp" to "standby_db". So, the users should have access to a
> "main_db" all the time (except for a second when the renames happen).
And
> "standby_db" serves as a full backup which I can use should I need it.
>
>
>
> Here's the problem...
>
>
>
> Sometimes the renames fail because people are still attached to either
> "main_db" or "standby_db". The error messages indicate this is the
problem
> anyway. Someof those users (most of them) are probably fast asleep at
home
> and forgot to exit the interactive session that was connected to the
DB.
>
> Q: Is there a way I can set a timeout where, if a user is inactive for
say
> an hour, they get disconnected?

Not that I know of.

> Q Is there a way to "kill" all active users without having to cycle
the DB
> server with something like "pg_ctl stop -m fast -D ..." ?

Yes, issue a kill on the pid from the command line as either postgres
or root. note I didn't say kill -9 there.

> Q: (the best option)... Is there a way I can leave those users
attached to
> their DB regardless of the fact that it's name changed while they were
> attached?

I don't think so. What might work best is to have two pg_hba.conf
files, and link to each one. so one is pg_hba.conf.lockout and one is
pg_hba.conf.normal, let's say. lockout is set to only answer to the
postgres user. Switch the pg_hba.conf files, and do a pg_ctl
stop;pg_ctl start or equivalent (/etc/init.d/postgresql stop / start)
and then do your processing. switch them back and restart pgsql
again.