Thread: Consistent pg_dump's

  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?

  Thanks for your help. Bye!!

  Alexey

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

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?

I am pretty sure that pg_dump produces a consistent snapshot, using
the same transaction isolation mechanism (MVCC) as the database
itself.  If one of the gurus posts and says otherwise, believe him,
not me.  ;)

-Doug

* Doug McNaught <doug@wireboard.com> [010329 15:22] wrote:
> 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?
>
> I am pretty sure that pg_dump produces a consistent snapshot, using
> the same transaction isolation mechanism (MVCC) as the database
> itself.  If one of the gurus posts and says otherwise, believe him,
> not me.  ;)

My impression from reading the source was that it was consitant per-table
but not per-database.  Meaning you'd get a consistant snapshot of a table,
but not of the entire system.

I could be wrong though.

--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
Represent yourself, show up at BABUG http://www.babug.org/

Doug McNaught <doug@wireboard.com> writes:

> I am pretty sure that pg_dump produces a consistent snapshot, using
> the same transaction isolation mechanism (MVCC) as the database
> itself.  If one of the gurus posts and says otherwise, believe him,
> not me.  ;)

Yes, you are right, this is done in pg_dump with the command:

set transaction isolation level serializable

Regards,
Manuel.

> > I am pretty sure that pg_dump produces a consistent snapshot, using
> > the same transaction isolation mechanism (MVCC) as the database
> > itself.  If one of the gurus posts and says otherwise, believe him,
> > not me.  ;)
>
> My impression from reading the source was that it was consitant per-table
> but not per-database.  Meaning you'd get a consistant snapshot of a table,
> but not of the entire system.
>
> I could be wrong though.

I hope so... that could break some foreign key dependencies pretty badly...

Greg

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