Re: Backup/disaster recovery and bandwidth (long)

Both good points, thanks, although I suspect that a direct network copy of the pg_data directory will be faster than a tar/untar event.

---

Hi Scott,
Why you do not replicate this master to the other location/s using other
methods like bucardo?, you can pick the tables you really want get
replicated there.
For the backup turn to hot backup (tar $PGDATA)+ archiving, easier,
faster and more efficient rather than a logical copy with pgdump.

A.A

On 04/25/2012 09:11 AM, Scott Whitney wrote:
> Hello, everyone. I want to throw a scenario out there to see what
> y'all think.
>
> Soon, my cluster backups will be increasing in size inordinately.
> They're going to immediately go to 3x as large as they currently are
> with the potential to be about 20x within a year or so.
>
> My current setup uses a single PG 8.x server doing nightly dumps (not
> ideal but sufficient for the moment, and one of the main reasons to
> move to PG 9) which are then downloaded from my hosting center to our
> offices for DR purposes. Each night I pull down roughly 5GB of
> compressed pg_dump data. Dumping this takes about 1.5hrs. Downloading
> this at 15Mbps takes about an hour. Soon I'll be looking at somewhere
> around 7hrs for the dumps to complete and downloading a 12GB file
> (which will take about 3 hrs). Oh, and I'll have to pay for
> significant bandwidth overage since I'm charged on a 95%, and while an
> hour a day does NOT kick me up to 15Mbps usage at 95%, 3hrs per night
> certainly will, so there's a real cost associated with this strategy
> as well.
>
> While the time of the actual dumps is not a huge issue, the time of
> the download IS a large concern, especially since my support folks use
> that file daily to extract individual customer databases for restore
> in assisting customer support issues.
>
> So, while now I have my pg_dumps completed around 2AM and downloaded
> to my local network at about 3AM, with the increase in our database
> sizes, what will be happening is that my pg_dump will not be completed
> until around 7AM, and the download would not be completed until around
> 10AM, best-case scenario. Add into that support trying to restore a
> database...more on that in a moment.
>
> My _new_ setup will instead be 2 PG 9.x servers with hot-standby
> enabled (at my hosting center) and a 3rd PG 9.x server at my local
> office also replicating off of the master. Each one of those servers
> will perform his own pg_dumps of the individual databases for
> backup/disaster recovery purposes, and while each dump might not be
> consistent with one another, each SERVER will have dumps consistent to
> itself, which is viable for our situation, and does not require me to
> download 12GB (or more) each night with all of those associated
> nightmares, costs and other problems.
>
> Alright, well, I've got that part all thought out, and it seems like a
> good way to do it to me, but I'm _still_ running into the situation
> that I've got to take 8hrs-ish to run the pg_dump no matter where it
> runs, and when my support folks need it (which they do daily), this
> basically means that if they have to have a customer database up NOW
> NOW NOW for support reasons, they simply cannot have it within an hour
> in many cases. Specifically, one database takes between 2 and 7.5hrs
> to pg_dump depending on which format I use, so if they need a CURRENT
> copy, they're at least 4 hours out. Additionally, they can't directly
> use the replicating server at my local office, because they need to
> test the problems the customers are having which include pesky things
> like INSERT, UPDATE and DELETE, so they have to restore this data to
> another internal PG backend.
>
> Enter my outside-the-box thinking.
>
> I rather assume that you cannot do a start/stop backup on a
> hot-standby server. HOWEVER, what if....
>
> I set up a 4th database server internally at my office. Each night I
> stop PG on my 3rd server (the local one replicating off of the master)
> and rsync my pg_data directory to this new 4th server. I bring up the
> 4th server NOT as a standby, but as a master. They would then have all
> customer data on an internal, usable PG system from the time of the
> rsync, and while it might not reflect the immediate state of the
> database, that's pretty well always true, and they're used to that,
> since whenever they "clone" a site, they're using the dumps done
> around midnight anyway.
>
> I believe, then, that when I restart server #3 (the standby who is
> replicating), he'll say "oh, geez, I was down, let me catch up on all
> that crap that happened while I was out of the loop," he'll replay the
> WAL files that were written while he was down, and then he'll catch
> back up.
>
> Does this sound like a viable option? Or does someone have additional
> suggestions?