Re: Consistent pg_dump's

On Wednesday 28 March 2001 22:48, you wrote:

Alexey Rodriguez Yakushev <alexey@serinbol.com> writes:

Are pg_dump's consistent? Do i have to shutdown the system in order
to make a pg_dump? What other alternatives are for backing up a
database without shutting down the system in order to allow
consistent backups? Another question: Can the rows that haven't
been vaccumed yet retrieved?

Yes. No. None. Theoretically, but no tools exist AFAIK.

HTH. HAND.

-Doug

Let me rephrase the question, i have a web-based application which uses
postgres as its backend. It must be operating 24 hours 7 days. If i want to
make a backup of the database using pg_dump, wouldn't it be possible that a
transaction occurs in the middle of the backup, leaving the backup
inconsistent?

Alexey Rodriguez Yakushev <alexey@serinbol.com> writes:

Let me rephrase the question, i have a web-based application which uses
postgres as its backend. It must be operating 24 hours 7 days. If i want to
make a backup of the database using pg_dump, wouldn't it be possible that a
transaction occurs in the middle of the backup, leaving the backup
inconsistent?

No. pg_dump will produce a consistent snapshot that does not show the
effects of transactions that start (or complete) after the pg_dump
transaction starts. See the "multi-version concurrency control" chapter
of the User's Guide.

Postgres has its share of problems for 24x7 operation (read about VACUUM
if you haven't yet) ... but producing self-consistent backups is not
one of 'em.

regards, tom lane