Search Postgresql Archives

Re: connection timeouts and "killing" users

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

 



On Thu, Sep 11, 2008 at 10:42 AM, Gauthier, Dave
<dave.gauthier@xxxxxxxxx> 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.


[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