Re: [HACKERS] Hot Backup Ability

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