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.
