Thread: Preventing access temporarily.
PG V9.0.1 on Linux
I want to temporarily prevent users from connecting to a DB, let the existing connections finish, <do some work>, re-enable connections.
What's the best way to do that?
Thanks in Advance
On Thu, Jan 26, 2012 at 3:05 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote: > PG V9.0.1 on Linux > > > > I want to temporarily prevent users from connecting to a DB, let the > existing connections finish, <do some work>, re-enable connections. > > What's the best way to do that? Edit pg_hba.conf to reject all connections and reload. current connections will stay connected, new ones will be refused. use pg_stat_activity to monitor connections til they're all gone / idle.
I found something else on the web. update pg_database set datallowconn = false where datname = 'foo'; update pg_database set datallowconn = true where datname = 'foo'; Seems to have worked OK. Thanks for the pg_hab.conf suggestion. I'll add that to my notes. -----Original Message----- From: Scott Marlowe [mailto:scott.marlowe@gmail.com] Sent: Thursday, January 26, 2012 5:39 PM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Preventing access temporarily. On Thu, Jan 26, 2012 at 3:05 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote: > PG V9.0.1 on Linux > > > > I want to temporarily prevent users from connecting to a DB, let the > existing connections finish, <do some work>, re-enable connections. > > What's the best way to do that? Edit pg_hba.conf to reject all connections and reload. current connections will stay connected, new ones will be refused. use pg_stat_activity to monitor connections til they're all gone / idle.
On Thu, Jan 26, 2012 at 3:55 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote: > I found something else on the web. > > update pg_database set datallowconn = false where datname = 'foo'; > update pg_database set datallowconn = true where datname = 'foo'; > > Seems to have worked OK. > > Thanks for the pg_hab.conf suggestion. I'll add that to my notes. Yeah either of those will work. pg_hba.conf is nice for more complex setups and you can have several pg_hba.conf.whatever files laying about, link the right one and reload. So it's pretty easy to script and back out for complex stuff.
On Thu, Jan 26, 2012 at 3:58 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Thu, Jan 26, 2012 at 3:55 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote: >> I found something else on the web. >> >> update pg_database set datallowconn = false where datname = 'foo'; >> update pg_database set datallowconn = true where datname = 'foo'; >> >> Seems to have worked OK. >> >> Thanks for the pg_hab.conf suggestion. I'll add that to my notes. > > Yeah either of those will work. pg_hba.conf is nice for more complex > setups and you can have several pg_hba.conf.whatever files laying > about, link the right one and reload. So it's pretty easy to script > and back out for complex stuff. Oh and also you can revoke connect by user which allows for finer grained control of connections as well.