Andreas Pflug <[EMAIL PROTECTED]> writes:Shouldn't be a problem, since there are few databases out there worldwide exceeding today's average disk capacity...
This is somewhat complementary to WAL and PITR. I'm seeking for a fast way to dump and restore a complete database, like physical file copy, without shutting down the backend. I was thinking of a BACKUP command that streams out the files including any indexes and non-vacuumed tuples. A database recreated from that wouldn't be as clean as a pg_dump/pg_restored database, but it would be up much faster, and there wouldn't be any dependency problem.
It's already intended to support this as part of the PITR work. The idea is you force a checkpoint and then make a tar-format dump of the database tree (tar or whatever floats your boat, but anyway a filesystem-level backup). The database need not be stopped while you do this, and you don't need a filesystem that can do snapshots or anything fancy like that. The tar dump itself most likely does not represent a consistent state of the database by the time you are done making it. That is okay, because you have also been archiving off to tape (or someplace) all the WAL data generated since that pre-dump checkpoint. You can continue archiving the WAL series for however far forward from the original dump you feel like. If you need to recover, you reload the database from the tar dump and then replay the WAL series against it. This is indistinguishable from a crash recovery situation --- the "inconsistent" tar dump looks just like a disk that has received some but not all of the updates since the last checkpoint. Replay will fix it.
The cool thing about this is you can actually bring the DB to the state
it was in at any specific point in time covered by your WAL archive ---
just run the WAL replay as far as you want, then stop. Solves the
"junior DBA deleted all my data Saturday morning" problem, thus "PITR".
Now the uncool thing is you need massive amounts of secondary storage to
archive all that WAL data,
PITR is cool, no question, it's more than I've been requesting. When the database server burns, I'd be quite happy if I could restore to my latest tape's point in time, since the WAL log disk probably isn't functional too. So having a fast backup of the snapshot when the backup CHECKPOINT was issued would be enough, no WAL replay needed.if your installation has heavy update activity. But it seems to me it would address the need you mention above --- you'd just not bother to continue archiving WAL past the end of the dump operation.
In principle you could do this today, but we don't have enough
support code in place to make it work smoothly, eg WAL segment files
aren't labeled with enough identifying information to let you manage
an archive full of 'em. Still it doesn't seem that far away.
So I issue CHECKPOINT, and tar the cluster or database. Still, I got two questions:
- how to restore a single database
- while tar is running, CHECKPOINT(n+1) might be recorded in some files, while others have CHECKPOINT(n). How does the backend know to rollback to CHECKPOINT(n)?
Regards, Andreas
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
