Message-ID: <4F982725.103@d2.com>
Date: Wed, 25 Apr 2012 09:32:37 -0700
From: amador alvarez <aalvarez@d2.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686 (x86_64); en-US;
	rv:1.9.2.27) Gecko/20120216 Lightning/1.0b2 Thunderbird/3.1.19
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: Backup/disaster recovery and bandwidth (long)
References: <da3b784d-86db-4b60-a6cd-149efaa58c0e@zimbra.int.journyx.com>
In-Reply-To: <da3b784d-86db-4b60-a6cd-149efaa58c0e@zimbra.int.journyx.com>
Content-Type: multipart/alternative;
	boundary="------------090506070407040001060809"

This is a multi-part message in MIME format.
--------------090506070407040001060809
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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?

--------------090506070407040001060809
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
    <title></title>
  </head>
  <body bgcolor="#ffffff" text="#000000">
    Hi Scott,<br>
    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.<br>
    For the backup turn to hot backup (tar $PGDATA)+ archiving, easier,
    faster and more efficient rather than a logical copy with pgdump.<br>
    <br>
    A.A <br>
    <br>
    On 04/25/2012 09:11 AM, Scott Whitney wrote:
    <blockquote
      cite="mid:da3b784d-86db-4b60-a6cd-149efaa58c0e@zimbra.int.journyx.com"
      type="cite">
      <style type="text/css">p { margin: 0; }</style>
      <div style="font-family: Times New Roman; font-size: 12pt; color:
        rgb(0, 0, 0);">Hello, everyone. I want to throw a scenario out
        there to see what y'all think.<br>
        <br>
        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.<br>
        <br>
        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.<br>
        <br>
        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.<br>
        <br>
        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.<br>
        <br>
        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.<br>
        <br>
        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.<br>
        <br>
        Enter my outside-the-box thinking.<br>
        <br>
        I rather assume that you cannot do a start/stop backup on a
        hot-standby server. HOWEVER, what if....<br>
        <br>
        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.<br>
        <br>
        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.<br>
        <br>
        Does this sound like a viable option? Or does someone have
        additional suggestions?<br>
      </div>
    </blockquote>
  </body>
</html>

--------------090506070407040001060809--