Thread: PG_DUMP and table locking in PG7.4

PG_DUMP and table locking in PG7.4

From
Yann Michel
Date:
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


Re: PG_DUMP and table locking in PG7.4

From
Yann Michel
Date:
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


Re: PG_DUMP and table locking in PG7.4

From
Alvaro Herrera
Date:
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.


Re: PG_DUMP and table locking in PG7.4

From
Christopher Kings-Lynne
Date:
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



Re: PG_DUMP and table locking in PG7.4

From
Yann Michel
Date:
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


Re: PG_DUMP and table locking in PG7.4

From
Andrew - Supernews
Date:
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


Re: PG_DUMP and table locking in PG7.4

From
Christopher Kings-Lynne
Date:
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



Re: PG_DUMP and table locking in PG7.4

From
Yann Michel
Date:
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


Re: PG_DUMP and table locking in PG7.4

From
Christopher Kings-Lynne
Date:
> 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



Re: PG_DUMP and table locking in PG7.4

From
Martijn van Oosterhout
Date:
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.

Re: PG_DUMP and table locking in PG7.4

From
Michael Paesold
Date:
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


Re: PG_DUMP and table locking in PG7.4

From
Yann Michel
Date:
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


Re: PG_DUMP and table locking in PG7.4

From
Martijn van Oosterhout
Date:
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.

Re: PG_DUMP and table locking in PG7.4

From
Rod Taylor
Date:
> 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.

-- 



Re: PG_DUMP and table locking in PG7.4

From
Tom Lane
Date:
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


Re: PG_DUMP and table locking in PG7.4

From
Yann Michel
Date:
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


Re: PG_DUMP and table locking in PG7.4

From
Yann Michel
Date:
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


Re: PG_DUMP and table locking in PG7.4

From
Alvaro Herrera
Date:
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.