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.

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