By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,834 Members | 2,234 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,834 IT Pros & Developers. It's quick & easy.

full db locking during dump

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Gregory Stone <gu***@yahoo.com> 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*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #2

P: n/a
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.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.