Re: [GENERAL] database-level locking

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...