Re: Need solution for weekly database "snapshot"

On Mon, Apr 22, 2013 at 11:58:58AM -0400, Moshe Jacobson wrote:

> We have a report in our system that is pulled many times each week, but
> needs to be based off data from Sunday at noon. So every Sunday at noon, we
> back up our main database and restore it into a new reporting snapshot
> database.
>
> The problem is that backing up and restoring the database every week is a
> very costly operation that can take many hours. It brings the system to its
> knees.

Setup a slave.

Sunday at noon let main DB reach a consistent state, "stop"
it, let slave catch up, detach slave Sunday at noon, restart
main, backup from slave, re-attach slave.

Similar things can be done with things like Btrfs at the FS level.

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346