On Wednesday 11 March 2009, Glen Parker <glenebob@nwlink.com> wrote:
> We have yet to recover from a PG disaster. We back up every night, and
> never use the back ups for anything. To me, it seems perfectly
> reasonable to get a quicker back up every night, with the remote
> possibility of ever having to pay the price for it.
This isn't totally relevant, but as a way to speed up base backups, I keep a
copy of the whole database rsync'd on the backup machine. The base backup
consists of refreshing the rsync, and then tarring it offline (of course
with the PITR backup commands in the sequence). My database is currently
750GB on disk and gets many tens of millions of updates a day, and the
rsync still runs in less than an hour per night.
I have done PITR recoveries (unfortunately). The log replay time dwarfs the
time it takes to copy the index files back over (it is, honestly, slower
than the original transactions were). Additionally, copying them is faster
than rebuilding them would be. Also, I can't imagine bringing a database
online without the majority of the indexes in place. The first dozen
non-indexed queries against large tables would kill the machine; not only
would you not be servicing users, but the rest of your restore would be
slowed immensely.
--
Even a sixth-grader can figure out that you can’t borrow money to pay off
your debt
