Обсуждение: Run postgresql engine in readonly mode?
Hi all! I would like to run all my databases in a readonly mode just for a few hours (migration plan). Is it a way to tell the postgresql engine to run in readonly? Sincerly G.Galland
On Thu, Oct 9, 2008 at 2:37 AM, Galland Gregoire <greg.pg@practeo.ch> wrote: > Hi all! > > I would like to run all my databases in a readonly mode just for a few > hours (migration plan). > > Is it a way to tell the postgresql engine to run in readonly? > > Sincerly > > G.Galland > This comes up periodically on lists. There's not really a way. You can set transactions as read only (see docs for SET TRANSACTION) but that's probably not feasible, as you'd have to change your application. You might create triggers preventing INSERTs and UPDATEs, but a good bet is just to back up before hand and warn your users. - Josh / eggyknap
On Sat, Oct 11, 2008 at 7:41 PM, Joshua Tolley <eggyknap@gmail.com> wrote: > On Thu, Oct 9, 2008 at 2:37 AM, Galland Gregoire <greg.pg@practeo.ch> wrote: >> Hi all! >> >> I would like to run all my databases in a readonly mode just for a few >> hours (migration plan). >> >> Is it a way to tell the postgresql engine to run in readonly? > > This comes up periodically on lists. There's not really a way. You can > set transactions as read only (see docs for SET TRANSACTION) but > that's probably not feasible, as you'd have to change your > application. You might create triggers preventing INSERTs and UPDATEs, > but a good bet is just to back up before hand and warn your users. There are a few other ways to skin this cat, depending on your use case. If you have a slony master and a slony slave, you can set the pg_hba.conf file to disallow connections on the master (except to the replication slaves) and let the users keep hitting the read only slave. Since Slony was designed to run on different versions of pgsql to allow for migration, this might be a good answer for the OP.
On Oct 11, 2008, at 6:41 PM, Joshua Tolley wrote: > On Thu, Oct 9, 2008 at 2:37 AM, Galland Gregoire > <greg.pg@practeo.ch> wrote: >> Hi all! >> >> I would like to run all my databases in a readonly mode just for a >> few >> hours (migration plan). >> >> Is it a way to tell the postgresql engine to run in readonly? >> >> Sincerly >> >> G.Galland >> > > This comes up periodically on lists. There's not really a way. You can > set transactions as read only (see docs for SET TRANSACTION) but > that's probably not feasible, as you'd have to change your > application. You might create triggers preventing INSERTs and UPDATEs, > but a good bet is just to back up before hand and warn your users. set default_transaction_read_only to true? Not entirely proof against a determined user, but good enough for most things, I'd guess. Cheers, Steve