Обсуждение: connection timeouts and "killing" users
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?
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 ...” ?
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?
Thanks in ADvance for any help.
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.
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.
On Thu, Sep 11, 2008 at 2:27 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote: > > How do I get the pids? Is there something specific I should look for in the > executable name I can see in "ps"? You can either use a combination of ps and grep: ps ax|grep postgres|grep dbname or use the pg_stat_activity table; > Will I break any remote server processes which are handeling remote > attaches if I do this? Yes, they would lose their connection. It's a choice you don't have anymore if you're renaming databases underneath them.
OK, killing the remote users is fine. Just want ot make sure I'm not killing some sort of shared remote server process(es) that would prevent future remotes to connect. -----Original Message----- From: Scott Marlowe [mailto:scott.marlowe@gmail.com] Sent: Thursday, September 11, 2008 5:35 PM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] connection timeouts and "killing" users On Thu, Sep 11, 2008 at 2:27 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote: > > How do I get the pids? Is there something specific I should look for in the > executable name I can see in "ps"? You can either use a combination of ps and grep: ps ax|grep postgres|grep dbname or use the pg_stat_activity table; > Will I break any remote server processes which are handeling remote > attaches if I do this? Yes, they would lose their connection. It's a choice you don't have anymore if you're renaming databases underneath them.
On Sep 11, 2008, at 12:02 PM, Scott Marlowe wrote: >> 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. Note that if he's not manually killing off each of the client connections only the first restart is necessary (to kill off the child connections) as config reload will take care of pg_hba.conf changes. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability (415) 963-4410 x 260 Location: US/Pacific IRC: mage2k