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.

----- Original Message -----

> 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?