Обсуждение: database-level locking
Does anyone know a way to lock a database under postgres while leaving available the other databases managed by postmaster? We are running postgreSQL 6.4.2 on our web site for content serving and for registration information. There are 4 separate databases served by postmatser to support these functions. Every now and the we have manually deleted temporary sort files to keep disk usage in control. We'd like to automate this process, but to do so safely we need to ensure that no sorts are being run while we delete the accumualted files. Thus, we'd like to lock a database, delete its accumulated wastage, unlock the database, and move on to the next. Of course the better solution would be if postgres was always able to identify and clear out its excesses, but we have found that is not the case. Karl
try this: begin; lock <tname1>; ... end; In the meantime, while you locked every table in your database with locks, and before "end" or "commit" or "rollback", no user can read/write none of your tables. You can delete temporary files, and then restore database functionality. It should word in 6.4.2, i'm not sure if it could work in 6.5 because of major changes in locking subsystem (readers'll have access to locked tables?). Second way it's simply to modify pghba.conf, to revoke access to databases for every user/domain; delete smth, whatever you want and restore access. I'm not sure if it break current connections, rollback transactions etc. - you should test it ;) At 13:57 99-03-19 -0500, Karl DeBisschop wrote: > >Does anyone know a way to lock a database under postgres while >leaving available the other databases managed by postmaster? > >We are running postgreSQL 6.4.2 on our web site for content serving >and for registration information. There are 4 separate databases >served by postmatser to support these functions. > >Every now and the we have manually deleted temporary sort files to >keep disk usage in control. We'd like to automate this process, but >to do so safely we need to ensure that no sorts are being run while we >delete the accumualted files. Thus, we'd like to lock a database, >delete its accumulated wastage, unlock the database, and move on to >the next. > >Of course the better solution would be if postgres was always able to >identify and clear out its excesses, but we have found that is not the >case. > >Karl > > Marcin Grondecki ojciec@mtl.pl +48(604)468725 ***** I'm not a complete idiot, some parts are missing...
While both suggestions should work, I'd like another. Locking each table one-by-one could be done, but some of these databases are kept current by sliding unique (and arbitrarily) named tables under a view with a fixed named that www selects from. Of course, I could generate a list of all tables, lock them one-by-one, then clean, but it just seems there should be a simpler way. As for modifying pg_hba.conf, we can do this securely in theory. But actually implementing a strategy of routinely modifying access on the fly seems like a painful way to identify unknown security holes. > try this: > begin; > lock <tname1>; > ... > > > > end; > > In the meantime, while you locked every table in your database with > locks, and before "end" or "commit" or "rollback", no user can > read/write none of your tables. You can delete temporary files, > and then restore database functionality. > It should word in 6.4.2, i'm not sure if it could work in 6.5 because > of major changes in locking subsystem (readers'll have access to locked > tables?). > > Second way it's simply to modify pghba.conf, to revoke access to databases > for every user/domain; delete smth, whatever you want and restore access. > I'm not sure if it break current connections, rollback transactions etc. > - you should test it ;) > > At 13:57 99-03-19 -0500, Karl DeBisschop wrote: > > > >Does anyone know a way to lock a database under postgres while > >leaving available the other databases managed by postmaster? > > > >We are running postgreSQL 6.4.2 on our web site for content serving > >and for registration information. There are 4 separate databases > >served by postmatser to support these functions. > > > >Every now and the we have manually deleted temporary sort files to > >keep disk usage in control. We'd like to automate this process, but > >to do so safely we need to ensure that no sorts are being run while we > >delete the accumualted files. Thus, we'd like to lock a database, > >delete its accumulated wastage, unlock the database, and move on to > >the next. > > > >Of course the better solution would be if postgres was always able to > >identify and clear out its excesses, but we have found that is not the > >case. > > > >Karl > > > > > Marcin Grondecki > ojciec@mtl.pl > +48(604)468725 > ***** I'm not a complete idiot, some parts are missing...