Re: 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.