473,326 Members | 2,126 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

full db locking during dump

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
2 1536
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: Puvendran | last post by:
Hi, We have encountered deadlock on a table which is used to generate sequential numbers for different categories eg typical entries Category Value TRADE_NO ...
9
by: john smile | last post by:
Hi All, I want to lock 2 tables on 2 servers using TABLOCKX hint. These tables function as semaphores in my application. It means when the tables are locked then other users will not be able to...
16
by: Nid | last post by:
How do I do row-level locking on SQL Server? Thanks, Nid
1
by: Robert Oschler | last post by:
I read somewhere that showing the full path during an error dump is a "bad thing": /dir1/dir2/somedir/script_that_died.php Like during a parse error or a warning from the interpreter. How can...
1
by: alex | last post by:
Hi ! I couldn't make backups with our new system using db2 8.2. Every time I trigger a backup I get this error message: BACKUP DATABASE EBUERO2 ONLINE TO "/raid/backup/ebuero2/part1",...
28
by: robert | last post by:
In very rare cases a program crashes (hard to reproduce) : * several threads work on an object tree with dict's etc. in it. Items are added, deleted, iteration over .keys() ... ). The threads are...
4
by: Paddy | last post by:
We have a recurring problem where a (long-running) service throws an exception while trying to access a file. The problem is that this is very rare (happens once every week and without warning) but...
6
by: brian.j.parker | last post by:
I inherited an application (or two) that run on SQL Server 6.5, which I haven't used in years, and am having a problem. I get the error: ...
5
by: FredC | last post by:
I have an application that uses mulitple timers. Each of the timer event handlers manipulate a common array of data. I'm getting Null refererance errors - should I put a lock on the array when I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.