Re: full db locking during dump

guomo(at)yahoo(dot)com (Gregory Stone) writes:
> I have a situation where I am doing a pg_dump from my database. I want to
> make sure that noone is inserting into the tables being dump during the
> dump. Does the MVCC architecture make it a moot point or do I need to
> disable insert privileges during the dumping window? If so, is there an
> easy way (i.e. one liner kind of thing) to do the temporary lock? I was
> thinking I might need to write a stored procedure to change the write
> privileges for the groups that have write access.

MVCC makes it pretty much a moot point. (You spelled "moot" right! I
usually see it spelled "mute," much as people that have never visited
a real parliament imagine that you shout "Here, here!" rather than
"Hear, hear! I digress...)

It should be quite unnecessary to do any lock because you cannot find
data committed later, because the dump is done in "SERIALIZED"
transaction mode.

That does not mean that there cannot be _some_ oddities that take
place during pg_dump. Since the dump has to have data as at the time
of the start of the dump, various tuples will be 'locked' against
being cleaned out. As a result:

- A vacuum done while pg_dump is running won't delete any of the
obsolete tuples that are part of the pg_dump.

- If you try to drop a table/index/whatever that is in the dump, the
DROP action may wind up deferred until pg_dump completes.
--
let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)