Re: Question about pg_dump

"Mitch Vincent" <mitch(at)venux(dot)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