Thread: PG_DUMP and table locking in PG7.4
Hi, I'm using PG_DUMP for backing up a postgres 7.4 database. As I have seen, the pg_dump aquires a table lock while dump the table's content. What will happen, if I have a basic table and several inherited tables. Will the PG_DUMP tool only aquire locks on the inherited tables or on the parent-table, too? Thanks in advance! Cheers, Yann
Hi all, On Sun, Nov 13, 2005 at 03:22:23AM +0100, Yann Michel wrote: > > I'm using PG_DUMP for backing up a postgres 7.4 database. As I have > seen, the pg_dump aquires a table lock while dump the table's content. > What will happen, if I have a basic table and several inherited tables. > Will the PG_DUMP tool only aquire locks on the inherited tables or on > the parent-table, too? Is anyone out there who could answer this question or even give me a hint about where to read about this if there is any writen things about that? Thanks! Yann
Yann Michel wrote: > Hi all, > > On Sun, Nov 13, 2005 at 03:22:23AM +0100, Yann Michel wrote: > > > > I'm using PG_DUMP for backing up a postgres 7.4 database. As I have > > seen, the pg_dump aquires a table lock while dump the table's content. > > What will happen, if I have a basic table and several inherited tables. > > Will the PG_DUMP tool only aquire locks on the inherited tables or on > > the parent-table, too? > > Is anyone out there who could answer this question or even give me a > hint about where to read about this if there is any writen things about > that? You could find out exactly which commands it sends by setting log_statement=all in a test environment and then dumping things to your heart's content. Looking at that output you should be able to easily determine which tables are locked. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
It acquires share locks on EVERY table. Yann Michel wrote: > Hi all, > > On Sun, Nov 13, 2005 at 03:22:23AM +0100, Yann Michel wrote: > >>I'm using PG_DUMP for backing up a postgres 7.4 database. As I have >>seen, the pg_dump aquires a table lock while dump the table's content. >>What will happen, if I have a basic table and several inherited tables. >>Will the PG_DUMP tool only aquire locks on the inherited tables or on >>the parent-table, too? > > > Is anyone out there who could answer this question or even give me a > hint about where to read about this if there is any writen things about > that? > > Thanks! > > Yann > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
Hi, On Wed, Nov 16, 2005 at 09:59:44AM +0800, Christopher Kings-Lynne wrote: > It acquires share locks on EVERY table. do you mean on EVERY inherited table once one of them is dumped? Or do you mean that a share lock is requested(and hold) on each of them once one is dumped, i.e., sequentially? Thanks, Yann
On 2005-11-16, Yann Michel <yann-postgresql@spline.de> wrote: > Hi, > > On Wed, Nov 16, 2005 at 09:59:44AM +0800, Christopher Kings-Lynne wrote: >> It acquires share locks on EVERY table. > > do you mean on EVERY inherited table once one of them is dumped? Or do > you mean that a share lock is requested(and hold) on each of them once > one is dumped, i.e., sequentially? pg_dump obtains an ACCESS SHARE lock on _every_ table it dumps, including the inherited child tables individually, _before_ it starts dumping anything. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
I belive a lock is acquired on every table including inherited children BEFORE doing ANY dumping. To allow pg_dump to get a consistent dump snapshot. Chris Yann Michel wrote: > Hi, > > On Wed, Nov 16, 2005 at 09:59:44AM +0800, Christopher Kings-Lynne wrote: > >>It acquires share locks on EVERY table. > > > do you mean on EVERY inherited table once one of them is dumped? Or do > you mean that a share lock is requested(and hold) on each of them once > one is dumped, i.e., sequentially? > > Thanks, > Yann
Hi, On Wed, Nov 16, 2005 at 01:25:43PM +0800, Christopher Kings-Lynne wrote: > I belive a lock is acquired on every table including inherited children > BEFORE doing ANY dumping. To allow pg_dump to get a consistent dump > snapshot. Well, thanks for all the answers. Are the locks then released once they are not needed any more like in 2PC? That should still leaqve the taken snapshot of the released table in a consistent state but might enable other transactions to work on that one table once it is released. I'm asking, because we have a bigger datawarehouse and dump the data for a backup every night. Unfortunately, the backup now takes realy long. That means, other processes that insert data will have to wait which is sometime really long! I was searching for a way to avoid this. I thought besides the query-speedub we could also gain some benefit for the backup timing... but it sounds, that this will not automatically help me with that. :-( Cheers, Yann
> I'm asking, because we have a bigger datawarehouse and dump the data for > a backup every night. Unfortunately, the backup now takes realy long. > That means, other processes that insert data will have to wait which is > sometime really long! I was searching for a way to avoid this. I thought > besides the query-speedub we could also gain some benefit for the backup > timing... but it sounds, that this will not automatically help me with > that. :-( No, all the tables are locked in ACCESS SHARE mode. All that means is that you cannot alter the schema of the tables in any way. Chris
On Wed, Nov 16, 2005 at 08:09:31AM +0100, Yann Michel wrote: > Well, thanks for all the answers. Are the locks then released once they > are not needed any more like in 2PC? 2PC doesn't release any locks, it can't to maintain integrity. > That should still leaqve the taken snapshot of the released table in a > consistent state but might enable other transactions to work on that one > table once it is released. ACCESS SHARE means what it says, it stops the table being VACUUMed and a few other things, but doesn't block INSERTs, UPDATEs or DELETEs. > I'm asking, because we have a bigger datawarehouse and dump the data for > a backup every night. Unfortunately, the backup now takes realy long. > That means, other processes that insert data will have to wait which is > sometime really long! I was searching for a way to avoid this. I thought > besides the query-speedub we could also gain some benefit for the backup > timing... but it sounds, that this will not automatically help me with > that. :-( pg_dump doesn't blocks inserts, so your problem must be somewhere else... Are you running VACUUM anywhere. It's possible that pg_dump is blocking VACUUM which blocks your inserts... Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Yann Michel wrote: > Hi, > > On Wed, Nov 16, 2005 at 01:25:43PM +0800, Christopher Kings-Lynne wrote: > >>I belive a lock is acquired on every table including inherited children >>BEFORE doing ANY dumping. To allow pg_dump to get a consistent dump >>snapshot. > > Well, thanks for all the answers. Are the locks then released once they > are not needed any more like in 2PC? > That should still leaqve the taken snapshot of the released table in a > consistent state but might enable other transactions to work on that one > table once it is released. > I'm asking, because we have a bigger datawarehouse and dump the data for > a backup every night. Unfortunately, the backup now takes realy long. > That means, other processes that insert data will have to wait which is > sometime really long! I was searching for a way to avoid this. No, a share lock on the table does not mean that other transactions can't insert or update anymore. What it does, is to prevent tables from being dropped or truncated. To get a consistent snapshot of the data in the tables itself, pg_dump just uses the SERIALIZABLE transaction isolation level. This is implemented via MVCC in postgresql, which makes sure that neither readers nor writers will block waiting. So only if you do full table locks in your application (using LOCK TABLE statements), you will suffer from pg_dump backups. Best Regards, Michael Paesold
Hi, first of all, thanks to all, that replied! On Wed, Nov 16, 2005 at 08:28:31AM +0100, Martijn van Oosterhout wrote: > On Wed, Nov 16, 2005 at 08:09:31AM +0100, Yann Michel wrote: > > Well, thanks for all the answers. Are the locks then released once they > > are not needed any more like in 2PC? > > 2PC doesn't release any locks, it can't to maintain integrity. Aehm. sorry I meant 2PL ... all this accronyms... ;-) The normal 2PL releases the locks once they are not needed anymore but can not aquire new ones. Strict 2PL releases them all at one point. > > That should still leaqve the taken snapshot of the released table in a > > consistent state but might enable other transactions to work on that one > > table once it is released. > > ACCESS SHARE means what it says, it stops the table being VACUUMed and > a few other things, but doesn't block INSERTs, UPDATEs or DELETEs. Thanks. BTW: Is there anything about locks and their meaning inside of the Docs? If not, wouldn't that be nice? > pg_dump doesn't blocks inserts, so your problem must be somewhere > else... Are you running VACUUM anywhere. It's possible that pg_dump is > blocking VACUUM which blocks your inserts... Well, now that I'm thinking about, what you've written I think this is exactly the point. I think, that there is a VACUUM waiting for the dump to finish whereas the INSERTS are waiting for the VACUUM to finish. Thannks! Cheers, Yann
On Wed, Nov 16, 2005 at 09:27:55AM +0100, Yann Michel wrote: > On Wed, Nov 16, 2005 at 08:28:31AM +0100, Martijn van Oosterhout wrote: > > On Wed, Nov 16, 2005 at 08:09:31AM +0100, Yann Michel wrote: > > > Well, thanks for all the answers. Are the locks then released once they > > > are not needed any more like in 2PC? > > > > 2PC doesn't release any locks, it can't to maintain integrity. > > Aehm. sorry I meant 2PL ... all this accronyms... ;-) > The normal 2PL releases the locks once they are not needed anymore but > can not aquire new ones. Strict 2PL releases them all at one point. Ah yes, PostgreSQL doesn't use 2PL, it uses MVCC. quick overview here: http://linuxgazette.net/issue68/mitchell.html > Thanks. BTW: Is there anything about locks and their meaning inside of > the Docs? If not, wouldn't that be nice? Check here: http://www.postgresql.org/docs/8.0/interactive/mvcc.html Under "Explicit Locking" it lists all the locks and what they're for. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
> I'm asking, because we have a bigger datawarehouse and dump the data for > a backup every night. Unfortunately, the backup now takes realy long. You may want to consider upgrading and using PITR backups instead. They can be much faster to both backup and to restore if something goes wrong. --
Yann Michel <yann-postgresql@spline.de> writes:
> Well, now that I'm thinking about, what you've written I think this is
> exactly the point. I think, that there is a VACUUM waiting for the dump
> to finish whereas the INSERTS are waiting for the VACUUM to finish.
Only if it's a VACUUM FULL ... plain VACUUM neither blocks INSERT nor is
blocked by pg_dump.
The short answer may be "don't use VACUUM FULL" (at least not for
routine automatic vacuums).
regards, tom lane
Hi Martijn, On Wed, Nov 16, 2005 at 09:47:33AM +0100, Martijn van Oosterhout wrote: > > Ah yes, PostgreSQL doesn't use 2PL, it uses MVCC. quick overview here: > http://linuxgazette.net/issue68/mitchell.html THX! That was interesting! > > Thanks. BTW: Is there anything about locks and their meaning inside of > > the Docs? If not, wouldn't that be nice? > > Check here: > http://www.postgresql.org/docs/8.0/interactive/mvcc.html > > Under "Explicit Locking" it lists all the locks and what they're for. Yes, thanks once more! Cheers, Yann
Hi, On Wed, Nov 16, 2005 at 10:07:24AM -0500, Tom Lane wrote: > Yann Michel <yann-postgresql@spline.de> writes: > > Well, now that I'm thinking about, what you've written I think this is > > exactly the point. I think, that there is a VACUUM waiting for the dump > > to finish whereas the INSERTS are waiting for the VACUUM to finish. > > Only if it's a VACUUM FULL ... plain VACUUM neither blocks INSERT nor is > blocked by pg_dump. > > The short answer may be "don't use VACUUM FULL" (at least not for > routine automatic vacuums). ... I guiess that the AUTOVACUUM switch only does an automated VACUUM but no VACUUM FULL? Cheers, Yann
Yann Michel wrote: > ... I guiess that the AUTOVACUUM switch only does an automated VACUUM > but no VACUUM FULL? Certainly. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.