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.marlowe@gmail.com]
Sent: Thursday, September 11, 2008 3:03 PM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] connection timeouts and "killing" users

On Thu, Sep 11, 2008 at 10:42 AM, Gauthier, Dave

<dave.gauthier@intel.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.