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