Thread

Hot Backup Ability

Michael Richards <miker@scifair.acadiau.ca> — 1999-06-29T22:53:00Z

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