Обсуждение: pg_dump and data consistency in the backup

How pg_dump honor data consistency on its output?

ie:

I have production system with some transaction in progress, some commited,
some not yet, clients normally work with the database.
Let database have some tables like "data",
"archive","something","something_else".

when i start pg_dump for creating backup

(like $ pg_dump mydb > output_file )

 on production and loaded system ,  IS IT   teoretically POSIBLE  that
somebody update (and commit) for example table "something", before
execution of  pg_dump ended ??   For example :pg_dump just process table
"data", but  other users can change table "archive" ?
or other example : pg_dump processed table "data", just process table
"archive" and while processing somebody can updata table "data" (witch is
currently dumped) and table "something" ?So it can lead to  an unconsistent
backup ????

Or pg_dump lock entire database mydb and backup is  perfectly consistent
and make (useful and consistent) backup from running server is complettly
safe ????

Thanx for answer and execuse my wrong english.



---
Odchozí zpráva neobsahuje viry.
Zkontrolováno antivirovým systémem AVG (http://www.grisoft.cz).
Verze: 6.0.483 / Virová báze: 279 - datum vydání: 19.5.2003

On Wed, May 21, 2003 at 19:12:31 +0200,
  "ing.Martin Prá?ek" <prasek@silesia.cz> wrote:
> How pg_dump honor data consistency on its output?

The dump is done in a transaction. Only transactions committed at the
start of the dump will be included in the dump. The database is not
locked while this is going on, so that it is still available for use
while the dump is being done.

On Wed, 21 May 2003, [iso-8859-2] ing.Martin Prá¹ek wrote:

> How pg_dump honor data consistency on its output?
>
> ie:
>
> I have production system with some transaction in progress, some commited,
> some not yet, clients normally work with the database.
> Let database have some tables like "data",
> "archive","something","something_else".
>
> when i start pg_dump for creating backup
>
> (like $ pg_dump mydb > output_file )
>
>  on production and loaded system ,  IS IT   teoretically POSIBLE  that
> somebody update (and commit) for example table "something", before
> execution of  pg_dump ended ??   For example :pg_dump just process table
> "data", but  other users can change table "archive" ?
> or other example : pg_dump processed table "data", just process table
> "archive" and while processing somebody can updata table "data" (witch is
> currently dumped) and table "something" ?So it can lead to  an unconsistent
> backup ????
>
> Or pg_dump lock entire database mydb and backup is  perfectly consistent
> and make (useful and consistent) backup from running server is complettly
> safe ????
>
> Thanx for answer and execuse my wrong english.

Postgresql uses the MVCC system's ability to "snapshot" data to ensure
that your backup is entirely consistent at the moment it starts.

Reference:

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=backup.html#BACKUP-DUMP

sepecifically the last paragraph:

"Dumps created by pg_dump are internally consistent, that is, updates to
the database while pg_dump is running will not be in the dump. pg_dump
does not block other operations on the database while it is working.
(Exceptions are those operations that need to operate with an exclusive
lock, such as VACUUM FULL.)"

Don't worry about your english, it's a second language for many on this
list.  :-)