Re: lock entire database - Mailing list pgsql-novice

We moved away from transaction based database structure changes due to possible effects of long transactions causing the updates to abort - the second conf file sounds the best bet. That way we can ensure that root only has access while the changes take place.

Thanks for the help - much appreciated

On Fri, 2004-08-06 at 18:23, Scott Marlowe wrote:

Generally the "solution" to locking the entire database is to keep a
spare pg_hba.conf around that only allows a certain user to connect to
do these things, and switching out from one pg_hba.conf to another as
needed.

Note, however, that DDL in PostgreSQL is fully transactable, so it's
possible to do something like:

begin;
alter table xyz ...
drop table abc...
create table abc as...
create index yada...
commit;

And none of the changes will show up for other users until the commit. 
Note that locking issues may lock users out of those tables being
modified until the commit, but they definitely won't see the changes
until commit.

On Fri, 2004-08-06 at 10:28, Steve Tucknott wrote:
> We have a similar request. 
> We have a 'program' that does database 'structure' updates and do not
> want the users touching the database while this is going on. In
> Informix this was achieved by placing and EXCLUSIVE lock on the
> database itself. 
> Is there a (simple) way of 'locking out' specific users under PostGre
> to achieve the same end? 
> On Fri, 2004-08-06 at 16:32, Ron St-Pierre wrote: 
>         Benjamin wrote:
>         
>         >
>         > In PGSQL, is it possible to lock the entire database??!! 
>         
>         Not that I know of, but why would you want to anyway?
>         
>         >
>         > This could be done with locking with individual table locking, but any 
>         > way to lock the entire db??
>         > If this is the only way, how do i get the listing of the tables? thru 
>         > '\d' ?? and then 'cut' or 'sed ' on it to get the individual table names? 
>         
>         Check out the docs at 
>         http://www.postgresql.org/docs/7.4/static/app-psql.html to see the psql 
>         commands. With the \d you can see just the tables, views, etc (eg \dt).
>         Ron
>         
>         
>         
>         ---------------------------(end of broadcast)---------------------------
>         TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 
> 
> Regards,
> 
> Steve Tucknott
> 
> ReTSol Ltd
> 
> DDI: 01903 828769

Regards, Steve Tucknott ReTSol Ltd DDI: 01903 828769