Thread: Question about pg_dump

Question about pg_dump

From
"Vilson farias"
Date:
Hi.

I started testing pg_dump and I have a little question :

What does pg_dump with data arrived while its running?

Lets suppose I'd started a pg_dump mybase > /home/postgres/text.txt and
while its running, new data was inserted into its tables.

All data that cames after the beginning of processing are ignored ? How can
I deal with it?


José Vilson de Mello de Farias
Dígitro Tecnologia ltda - Brazil


Re: Question about pg_dump

From
"Mitch Vincent"
Date:
Well, all you're doing with pg_dump is getting a snapshot of the database at
a single point in time -- as pg_dump reads the data out of the tables, I'm
sure that it won't go back and re-read tables that have changed since it was
first started. On the other hand, if pg_dump is busy on table A and table Z
changes, even though it's after the pg_dump process starts, I would assume
the changes to table Z would get dumped.

I'm assuming a lot -- all of that is based on the fact that I've never seen
pg_dump lock the whole database down (though I do think it locks the table
it's dumping while it's dumping it)..

-Mitch

----- Original Message -----
From: "Vilson farias" <vilson.farias@digitro.com.br>
To: <pgsql-general@postgresql.org>
Cc: "SIMONE Carla MOSENA" <simone.mosena@digitro.com.br>
Sent: Friday, October 13, 2000 11:53 AM
Subject: [GENERAL] Question about pg_dump


> Hi.
>
> I started testing pg_dump and I have a little question :
>
> What does pg_dump with data arrived while its running?
>
> Lets suppose I'd started a pg_dump mybase > /home/postgres/text.txt and
> while its running, new data was inserted into its tables.
>
> All data that cames after the beginning of processing are ignored ? How
can
> I deal with it?
>
>
> José Vilson de Mello de Farias
> Dígitro Tecnologia ltda - Brazil
>
>


Re: Question about pg_dump

From
Philip Warner
Date:
At 16:53 13/10/00 -0200, Vilson farias wrote:
>Hi.
>
>I started testing pg_dump and I have a little question :
>
>What does pg_dump with data arrived while its running?
>

pg_dump runs in a single transaction, so in theory it dumps a consistent
view of the data, not necessarily the absolute latest data.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Re: Question about pg_dump

From
Tom Lane
Date:
"Mitch Vincent" <mitch@venux.net> writes:
> Well, all you're doing with pg_dump is getting a snapshot of the database at
> a single point in time -- as pg_dump reads the data out of the tables, I'm
> sure that it won't go back and re-read tables that have changed since it was
> first started. On the other hand, if pg_dump is busy on table A and table Z
> changes, even though it's after the pg_dump process starts, I would assume
> the changes to table Z would get dumped.

> I'm assuming a lot -- all of that is based on the fact that I've never seen
> pg_dump lock the whole database down (though I do think it locks the table
> it's dumping while it's dumping it)..

Under MVCC, pg_dump doesn't lock much of anything --- ordinary read
and write transactions can proceed with abandon.  You would see some
interlocking behavior with schema-altering commands (eg, ALTER or DROP
TABLE) but not with updates of user data.  The general rule for
SELECT-only transactions, which is what a pg_dump run is, is that the
transaction sees all and only that data written by transactions that
committed before it started.

I've just finished putting together a talk about MVCC transaction
processing --- both how it behaves and how it's implemented --- for
the upcoming OSDN database conference.  I think the slides for that
talk are fairly self-contained and might be of general interest;
shall I brace Vince about putting them up on the website?

            regards, tom lane

Re: Question about pg_dump

From
"Dominic J. Eidson"
Date:
On Sat, 14 Oct 2000, Tom Lane wrote:

> I've just finished putting together a talk about MVCC transaction
> processing --- both how it behaves and how it's implemented --- for
> the upcoming OSDN database conference.  I think the slides for that
> talk are fairly self-contained and might be of general interest;
> shall I brace Vince about putting them up on the website?

I would certainly be interested in them, FWIW...


--
Dominic J. Eidson
                                        "Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/              http://www.the-infinite.org/~dominic/


Re: Question about pg_dump

From
Philip Warner
Date:
At 00:10 14/10/00 -0400, Tom Lane wrote:
>I think the slides for that
>talk are fairly self-contained and might be of general interest;
>shall I brace Vince about putting them up on the website?

This would be good to see. Would it fit in the documentation hierarchy
somewhere?


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Re: Question about pg_dump

From
Vince Vielhaber
Date:
On Sat, 14 Oct 2000, Philip Warner wrote:

> At 00:10 14/10/00 -0400, Tom Lane wrote:
> >I think the slides for that
> >talk are fairly self-contained and might be of general interest;
> >shall I brace Vince about putting them up on the website?
>
> This would be good to see. Would it fit in the documentation hierarchy
> somewhere?

Ok, what did I miss?

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================