Thread

1. Re: Consistent pg_dump's

   Doug McNaught <doug@wireboard.com> — 2001-03-29T02:48:38Z

   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
   
   

2. Consistent pg_dump's

   Alexey Rodriguez Yakushev <alexey@serinbol.com> — 2001-03-29T02:56:18Z

     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
   
   

3. Re: Consistent pg_dump's

   Manuel Sugawara <masm@fciencias.unam.mx> — 2001-03-29T22:56:30Z

   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.
   
   

4. Re: Consistent pg_dump's

   Doug McNaught <doug@wireboard.com> — 2001-03-29T23:09:25Z

   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
   
   

5. Re: Consistent pg_dump's

   Alexey Rodriguez Yakushev <alexey@serinbol.com> — 2001-03-29T23:12:56Z

   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?
   
   

6. Re: Consistent pg_dump's

   Alfred Perlstein <bright@wintelcom.net> — 2001-03-29T23:49:21Z

   * 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/
   
   

7. Re: Consistent pg_dump's

   Gregory Wood <gregw@com-stock.com> — 2001-03-30T05:25:29Z

   > > 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
   
   
   

8. Re: Consistent pg_dump's

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-31T04:56:26Z

   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