Thread: 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
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 > >
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 |/
"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
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/
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 |/
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 ==========================================================================