full db locking during dump

[Gregory Stone]

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.

Thanks,

Gregory

=====
--
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~
Gregory Stone | "Suppose you were an idiot, and suppose you were
guomo ( at ) yahoo.com | a member of congress; but I repeat myself."
| - Mark Twain
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~

_______________ _______________ ____
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Gregory Stone <gu***@yahoo.co m> 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

It's moot. You get a consistent snapshot as of the dump start time,
regardless.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Christopher Browne]

gu***@yahoo.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.libert yrms.com/>
Christopher Browne
[phone] x124 (land)