Hi Mike.

Get that job in Ottawa?

Anyway, version 6.5 apparently supports hot backups by using MVCC
to give a view of a consistent version of the database during a
pg_dump (http://postgresql.nextpath.com/doxlist.html  search for 'backup')

What you explain below is basically version-controlled additions anyway.

Anyone correct me if I'm wrong...

Duane

> Hi.
> I've been mulling around a lot with this idea. I've looked around a bit
> for info on being able to do hot backups on a running database, but there
> isn't a lot of info available. The problem with just pg_dumping the data
> is that it doesn't work well with large databases that are expected to be
> processing transactions during the backup time period.
> 
> Dropping postgres down to a select-only lock level on all databases at
> once was my thought. In order to keep the system running hot, you'd have
> to set a flag to say that database is being backed up. My idea is to allow
> a special directory where the deltas are written. IE: Someone inserts a
> record, it would need to write that page to a file in the temp dir for
> both the table, and its indexes. Then, when a select is run, it would have
> to first check the delta table files, then the real indexes for the page
> it's looking for.
> 
> This way, you could guarantee that the files being backed up would not be
> altered in any way during the backup, and the deltas would be the only
> overhead. Using the hole in file feature, I think that page changes could
> be added to the file without making to too large, but I've not looked
> closely on how indexes are physically stored to see this. I suppose the NT
> port would require double the size of the database to do this, since I
> don't think winblows supports holes in a file.
> 
> With the database in select-only mode, someone could either do a pg_dump
> style backup, or backup the actual tables. I am guessing that it's more of
> a restore time / backup size tradeoff with each backup method.
> 
> One reason I am looking at this (a possible 6.6 feature?) is that we are
> using postgresql for a classifieds database which will replace a
> SQL-Server. The database will easily be in the 10's of gigabytes range
> with a few million items. I will of course need to backup this beast
> without preventing the clients from adding things.
> 
> If someone can point me in the right direction, I can attempt to make it
> work and submit a pile 'o patches againt 6.5.
> 
> Comments? 
> 
> -Michael
> 
> 


Tue, 29 Jun 1999 17:58:26 -0300 (ADT)
Duane Currie <dcurrie@sandman.acadiau.ca>
Re: [HACKERS] Hot Backup Ability
Hi.
I've been mulling around a lot with this idea. I've looked around a bit
for info on being able to do hot backups on a running database, but there
isn't a lot of info available. The problem with just pg_dumping the data
is that it doesn't work well with large databases that are expected to be
processing transactions during the backup time period.

Dropping postgres down to a select-only lock level on all databases at
once was my thought. In order to keep the system running hot, you'd have
to set a flag to say that database is being backed up. My idea is to allow
a special directory where the deltas are written. IE: Someone inserts a
record, it would need to write that page to a file in the temp dir for
both the table, and its indexes. Then, when a select is run, it would have
to first check the delta table files, then the real indexes for the page
it's looking for.

This way, you could guarantee that the files being backed up would not be
altered in any way during the backup, and the deltas would be the only
overhead. Using the hole in file feature, I think that page changes could
be added to the file without making to too large, but I've not looked
closely on how indexes are physically stored to see this. I suppose the NT
port would require double the size of the database to do this, since I
don't think winblows supports holes in a file.

With the database in select-only mode, someone could either do a pg_dump
style backup, or backup the actual tables. I am guessing that it's more of
a restore time / backup size tradeoff with each backup method.

One reason I am looking at this (a possible 6.6 feature?) is that we are
using postgresql for a classifieds database which will replace a
SQL-Server. The database will easily be in the 10's of gigabytes range
with a few million items. I will of course need to backup this beast
without preventing the clients from adding things.

If someone can point me in the right direction, I can attempt to make it
work and submit a pile 'o patches againt 6.5.

Comments? 

-Michael


Tue, 29 Jun 1999 19:53:00 -0300 (ADT)
Michael Richards <miker@scifair.acadiau.ca>
Hot Backup Ability

Hot backups were added in 6.5.


> Hi.
> I've been mulling around a lot with this idea. I've looked around a bit
> for info on being able to do hot backups on a running database, but there
> isn't a lot of info available. The problem with just pg_dumping the data
> is that it doesn't work well with large databases that are expected to be
> processing transactions during the backup time period.
> 
> Dropping postgres down to a select-only lock level on all databases at
> once was my thought. In order to keep the system running hot, you'd have
> to set a flag to say that database is being backed up. My idea is to allow
> a special directory where the deltas are written. IE: Someone inserts a
> record, it would need to write that page to a file in the temp dir for
> both the table, and its indexes. Then, when a select is run, it would have
> to first check the delta table files, then the real indexes for the page
> it's looking for.
> 
> This way, you could guarantee that the files being backed up would not be
> altered in any way during the backup, and the deltas would be the only
> overhead. Using the hole in file feature, I think that page changes could
> be added to the file without making to too large, but I've not looked
> closely on how indexes are physically stored to see this. I suppose the NT
> port would require double the size of the database to do this, since I
> don't think winblows supports holes in a file.
> 
> With the database in select-only mode, someone could either do a pg_dump
> style backup, or backup the actual tables. I am guessing that it's more of
> a restore time / backup size tradeoff with each backup method.
> 
> One reason I am looking at this (a possible 6.6 feature?) is that we are
> using postgresql for a classifieds database which will replace a
> SQL-Server. The database will easily be in the 10's of gigabytes range
> with a few million items. I will of course need to backup this beast
> without preventing the clients from adding things.
> 
> If someone can point me in the right direction, I can attempt to make it
> work and submit a pile 'o patches againt 6.5.
> 
> Comments? 
> 
> -Michael
> 
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 29 Jun 1999 19:36:26 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Hot Backup Ability
On Tue, 29 Jun 1999, Duane Currie wrote:

> Anyway, version 6.5 apparently supports hot backups by using MVCC
> to give a view of a consistent version of the database during a
> pg_dump (http://postgresql.nextpath.com/doxlist.html  search for
'backup')
Hrm. Nothing pops out.
    
Just out of curiosity, I did a DUMP on the database while running a script
that ran a pile of updates. When I restored the database files, it was so
corrupted that I couldn't even run a select. vacuum just core dumped...
    
If I can just run pg_dump to back it up, how does this guarantee any sort
of referential integrity? Also during such a dump, it seems that things   
block while waiting for a lock. This also happens during a pg_vacuum. I   
thought that mvcc was supposed to stop this...
    
-Michael
    


Tue, 29 Jun 1999 21:46:21 -0300 (ADT)
Michael Richards <miker@scifair.acadiau.ca>
Re: [HACKERS] Hot Backup Ability
> On Tue, 29 Jun 1999, Duane Currie wrote:
> 
> > Anyway, version 6.5 apparently supports hot backups by using MVCC
> > to give a view of a consistent version of the database during a
> > pg_dump (http://postgresql.nextpath.com/doxlist.html  search for
> 'backup')
> Hrm. Nothing pops out.
>     
> Just out of curiosity, I did a DUMP on the database while running a script
> that ran a pile of updates. When I restored the database files, it was so
> corrupted that I couldn't even run a select. vacuum just core dumped...

When you say DUMP, you mean pg_dump, right?  Are you using 6.5?

> If I can just run pg_dump to back it up, how does this guarantee any sort
> of referential integrity? Also during such a dump, it seems that things   
> block while waiting for a lock. This also happens during a pg_vacuum. I   
> thought that mvcc was supposed to stop this...

OK, sounds like you are using pg_dump, and 6.5.  pg_vacuum still blocks,
but pg_dump shouldn't.  This sounds unusual.  You should have gotten
everything at the time of the _start_ of the dump.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 29 Jun 1999 21:09:48 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Hot Backup Ability
On Tue, 29 Jun 1999, Duane Currie wrote:

> Anyway, version 6.5 apparently supports hot backups by using MVCC
> to give a view of a consistent version of the database during a
> pg_dump (http://postgresql.nextpath.com/doxlist.html  search for 'backup')

The string "backup" does not appear on that page, or on the "Documentation"
page linked off of there.  The section on backups in the integrated manual
doesn't say anything about MVCC.

Did any MVCC docs ever get written?

--
Todd Graham Lewis                        Postmaster, MindSpring Enterprises
tlewis@mindspring.net                                (800) 719-4664, x22804

                          "There is no spoon."


Tue, 29 Jun 1999 22:10:41 -0400 (EDT)
Todd Graham Lewis <tlewis@mindspring.net>
Re: [HACKERS] Hot Backup Ability
Todd Graham Lewis wrote:
> 
> On Tue, 29 Jun 1999, Duane Currie wrote:
> 
> > Anyway, version 6.5 apparently supports hot backups by using MVCC
> > to give a view of a consistent version of the database during a
> > pg_dump (http://postgresql.nextpath.com/doxlist.html  search for 'backup')
> 
> The string "backup" does not appear on that page, or on the "Documentation"
> page linked off of there.  The section on backups in the integrated manual
> doesn't say anything about MVCC.
> 
> Did any MVCC docs ever get written?

http://postgresql.nextpath.com/docs/user/index.html:

10. Multi-Version Concurrency Control

Vadim

Wed, 30 Jun 1999 10:28:46 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Hot Backup Ability
On Wed, 30 Jun 1999, Vadim Mikheev wrote:

> > Did any MVCC docs ever get written?
> 
> http://postgresql.nextpath.com/docs/user/index.html:
> 
> 10. Multi-Version Concurrency Control

Very groovy!

--
Todd Graham Lewis                        Postmaster, MindSpring Enterprises
tlewis@mindspring.net                                (800) 719-4664, x22804

                          "There is no spoon."


Tue, 29 Jun 1999 22:31:18 -0400 (EDT)
Todd Graham Lewis <tlewis@mindspring.net>
Re: [HACKERS] Hot Backup Ability
On Tue, 29 Jun 1999, Bruce Momjian wrote:

> > Just out of curiosity, I did a DUMP on the database while running a script
> > that ran a pile of updates. When I restored the database files, it was so
> > corrupted that I couldn't even run a select. vacuum just core dumped...
> 
> When you say DUMP, you mean pg_dump, right?  Are you using 6.5?

Erm. Well, no. I was running ufsdump. Once I read the section on mvcc and
re-did the test with the pg_dump, I realised that it does work as
documented...

I should think this is a good feature to broadcast to everyone. I don't
think other free systems support it.

The thing I got confuzed with that blocked transactions was the pg_vacuum.
Seeing as how it physically re-arranges data inside the tables and
indexes, is there any hope for not blocking the table for a long time as
it re-arranges a 15 gig table?

Will re-usable page support (whenever it is expected) eliminate the need
for vacuum?

Would it be easy to come up with a scheme for the vacuum function defrag a
set number of pages and such, release its locks if there is another
process blocked and waiting, then resume after that process is finished?

-Michael


Wed, 30 Jun 1999 01:55:24 -0300 (ADT)
Michael Richards <miker@scifair.acadiau.ca>
Re: [HACKERS] Hot Backup Ability
> On Tue, 29 Jun 1999, Bruce Momjian wrote:
> 
> > > Just out of curiosity, I did a DUMP on the database while running a script
> > > that ran a pile of updates. When I restored the database files, it was so
> > > corrupted that I couldn't even run a select. vacuum just core dumped...
> > 
> > When you say DUMP, you mean pg_dump, right?  Are you using 6.5?
> 
> Erm. Well, no. I was running ufsdump. Once I read the section on mvcc and
> re-did the test with the pg_dump, I realised that it does work as
> documented...


Woh!  Not a good idea.  We can't get a proper snapshot if the ufs blocks
are moving around while we are doing the backup.  We need pg_dump.

Glad it worked when you did it with pg_dump.

> I should think this is a good feature to broadcast to everyone. I don't
> think other free systems support it.

Probably not.  We have it as one of our main items in the release notes,
and on the web page describing the release.  We need people like you to
tell others about it.

> 
> The thing I got confuzed with that blocked transactions was the pg_vacuum.
> Seeing as how it physically re-arranges data inside the tables and
> indexes, is there any hope for not blocking the table for a long time as
> it re-arranges a 15 gig table?

Not really.  In fact, it even shrinks the table to give back free space.
The 6.5 pg_vacuum is much faster than earlier versions, but on a 15gig
table, it is going to take some time.

Some day, it would be nice to allow re-use of expired rows without
vacuum.  It is on our TODO list.

> Will re-usable page support (whenever it is expected) eliminate the need
> for vacuum?

It will allow you to vacuum less frequently, and perhaps never if you
don't want space back from expired rows.

> Would it be easy to come up with a scheme for the vacuum function defrag a
> set number of pages and such, release its locks if there is another
> process blocked and waiting, then resume after that process is finished?

That is a very nice idea.  We could just release and reaquire the lock,
knowing that if there is someone waiting, they would get the lock. 
Maybe someone can comment on this?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 30 Jun 1999 01:15:37 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Hot Backup Ability
At 09:09 PM 6/29/99 -0400, Bruce Momjian wrote:
  
>> Just out of curiosity, I did a DUMP on the database while running a script
>> that ran a pile of updates. When I restored the database files, it was so
>> corrupted that I couldn't even run a select. vacuum just core dumped...

>When you say DUMP, you mean pg_dump, right?  Are you using 6.5?

In his first note, he was proposing a scheme that would allow either
filesystem dumps or pg_dumps, which I think a couple of respondents
missed.

So I suspect he means a filesystem dump in this case.  Which of course
won't work in postgres, or in Oracle.
>
>


- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, and other goodies at
  http://donb.photo.net

Tue, 29 Jun 1999 23:09:02 -0700
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Hot Backup Ability
Oops... sorry guys...  forgot about the frames.

The doc I was referring to was:
http://www.postgresql.org/docs/admin/release.htm

Duane

> On Tue, 29 Jun 1999, Duane Currie wrote:
> 
> > Anyway, version 6.5 apparently supports hot backups by using MVCC
> > to give a view of a consistent version of the database during a
> > pg_dump (http://postgresql.nextpath.com/doxlist.html  search for 'backup')
> 
> The string "backup" does not appear on that page, or on the "Documentation"
> page linked off of there.  The section on backups in the integrated manual
> doesn't say anything about MVCC.
> 
> Did any MVCC docs ever get written?
> 
> --
> Todd Graham Lewis                        Postmaster, MindSpring Enterprises
> tlewis@mindspring.net                                (800) 719-4664, x22804
> 
>                           "There is no spoon."
> 


Wed, 30 Jun 1999 09:20:27 +0000 (AST)
Duane Currie <dcurrie@sandman.acadiau.ca>
Re: [HACKERS] Hot Backup Ability
On Wed, 30 Jun 1999, Bruce Momjian wrote:

> > Would it be easy to come up with a scheme for the vacuum function defrag a
> > set number of pages and such, release its locks if there is another
> > process blocked and waiting, then resume after that process is finished?
> 
> That is a very nice idea.  We could just release and reaquire the lock,
> knowing that if there is someone waiting, they would get the lock. 
> Maybe someone can comment on this?

My first thought is "doesn't this still require the 'page-reusing'
functionality to exist"?  Which virtually eliminates the problem...

If not, then why can't something be done where this is transparent
altogther?  Have some sort of mechanism that keeps track of "dead
space"...a trigger that says after X tuples have been deleted, do an
automatic vacuum of the database?

The automatic vacuum would be done in a way similar to Michael's
suggestion above...scan through for the first 'dead space', lock the table
for a short period of time and "move records up".  How many tuples could
you move in a very short period of time, such that it is virtually
transparent to end-users?

As a table gets larger and larger, a few 'dead tuples' aren't going to
make much of a different in performance, so make the threshold some
percentage of the size of the table, so at it grows, the number of 'dead
tuples' has to be larger...

And leave out the truncate at the end...

The 'manual vacuum' would still need to be run periodically, for the
truncate and for stats...

Just a thought...:)

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Wed, 30 Jun 1999 09:40:42 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Vaccum (Was: Re: [HACKERS] Hot Backup Ability)
On Wed, 30 Jun 1999, The Hermit Hacker wrote:

> On Wed, 30 Jun 1999, Bruce Momjian wrote:
> 
> > > Would it be easy to come up with a scheme for the vacuum function defrag a
> > > set number of pages and such, release its locks if there is another
> > > process blocked and waiting, then resume after that process is finished?
> > 
> > That is a very nice idea.  We could just release and reaquire the lock,
> > knowing that if there is someone waiting, they would get the lock. 
> > Maybe someone can comment on this?
> 
> My first thought is "doesn't this still require the 'page-reusing'
> functionality to exist"?  Which virtually eliminates the problem...
> 
> If not, then why can't something be done where this is transparent
> altogther?  Have some sort of mechanism that keeps track of "dead
> space"...a trigger that says after X tuples have been deleted, do an
> automatic vacuum of the database?
> 
> The automatic vacuum would be done in a way similar to Michael's
> suggestion above...scan through for the first 'dead space', lock the table
> for a short period of time and "move records up".  How many tuples could
> you move in a very short period of time, such that it is virtually
> transparent to end-users?
> 
> As a table gets larger and larger, a few 'dead tuples' aren't going to
> make much of a different in performance, so make the threshold some
> percentage of the size of the table, so at it grows, the number of 'dead
> tuples' has to be larger...
> 
> And leave out the truncate at the end...
> 
> The 'manual vacuum' would still need to be run periodically, for the
> truncate and for stats...
> 
> Just a thought...:)

Why not one step further.  Constant background vacuuming.  Do away with
the need to vacuum altogether.  Have something in the backend always 
scanning for dead tuples/dead space and when it finds some it can lock-
move-unlock as it goes.  This way it's not working with a set number or
looking for a certain threshold, just a constant maintenance process.

No?

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null
       # include <std/disclaimers.h>                   TEAM-OS2
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Wed, 30 Jun 1999 10:27:35 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
Re: Vaccum (Was: Re: [HACKERS] Hot Backup Ability)