Message-ID: <54D51B08.5050901@gmail.com>
Date: Fri, 06 Feb 2015 14:50:32 -0500
From: John Scalia <jayknowsunix@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:31.0) Gecko/20100101 Thunderbird/31.4.0
MIME-Version: 1.0
To: Matheus de Oliveira <matioli.matheus@gmail.com>
CC: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Hourly backup using pg_basebackup
References: <54D50D9B.1090905@gmail.com>
 <CAJghg4+JXVs1_k12M=Ozu_pzma0eBPQO++hA5b7C2vhXAh2-tg@mail.gmail.com>
In-Reply-To: 
 <CAJghg4+JXVs1_k12M=Ozu_pzma0eBPQO++hA5b7C2vhXAh2-tg@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------010409080106080402030909"
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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


On 2/6/2015 2:25 PM, Matheus de Oliveira wrote:
>
> On Fri, Feb 6, 2015 at 4:53 PM, John Scalia <jayknowsunix@gmail.com <mailto:jayknowsunix@gmail.com>> wrote:
>
>     We have a python script called by cron on an hourly basis to back up our production database. Currently, the script invokes pg_dump and takes more than hour to complete.
>     Hence the script looks to see if it's already running and exits if so. I want to change the script so it uses pg_basebackup instead since that's so much faster.
>
>
> Have you considered using incremental backup (continuous archiving) instead of a such small backup window?
>
> See [1]
>
>     My problem is, however, that while I'd like to just have it build a tarball, maybe compressed,  I can't use a "-X s" option for the wal segments. I think I understand why I
>     can't use the streaming option with a "-Ft" specified. I'm just concerned about the docs saying that the backup may have problems with fetch as a wal segment may have
>     expired. Manually testing is showing that the Db needs about 11 minutes to backup with pg_basebackup, and our wal_keep_segments setting is 6.  This said, an hour's worth of
>     wal segments should be available, but the six that were there at the beginning of the backup are not the same six there at the end. I don't think this is really a problem,
>     but I'd like to get it confirmed. Wouldn't the backup actually have to take more than hour for this to be an issue?
>
>
> If you use archiving [1], you don't need to worry about saving the segments withing the backup, just let it be done through archive_command. Isn't that an option for you? If 
> don't, why?
>
Oh, yes, I did fail to mention that the system where I'm trying this is the primary in a streaming replication cluster with (2) hot standby servers. I've mentioned without much 
traction, that we don't really even need a backup with 3 servers ready to do the work without delays. Like I said in the last post, it's all political.
>
> [1] http://www.postgresql.org/docs/current/static/continuous-archiving.html
>
> Best regards,
> -- 
> Matheus de Oliveira
> Analista de Banco de Dados
> Dextra Sistemas - MPS.Br nível F!
> www.dextra.com.br/postgres <http://www.dextra.com.br/postgres/>
>


--------------010409080106080402030909
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <br>
    <div class="moz-cite-prefix">On 2/6/2015 2:25 PM, Matheus de
      Oliveira wrote:<br>
    </div>
    <blockquote
cite="mid:CAJghg4+JXVs1_k12M=Ozu_pzma0eBPQO++hA5b7C2vhXAh2-tg@mail.gmail.com"
      type="cite">
      <div dir="ltr">
        <div class="gmail_extra"><br>
          <div class="gmail_quote">On Fri, Feb 6, 2015 at 4:53 PM, John
            Scalia <span dir="ltr">&lt;<a moz-do-not-send="true"
                href="mailto:jayknowsunix@gmail.com" target="_blank">jayknowsunix@gmail.com</a>&gt;</span>
            wrote:<br>
            <blockquote class="gmail_quote" style="margin:0px 0px 0px
              0.8ex;border-left:1px solid
              rgb(204,204,204);padding-left:1ex">
              <div id=":lv" class="" style="overflow:hidden">
                We have a python script called by cron on an hourly
                basis to back up our production database. Currently, the
                script invokes pg_dump and takes more than hour to
                complete. Hence the script looks to see if it's already
                running and exits if so. I want to change the script so
                it uses pg_basebackup instead since that's so much
                faster.<br>
                <br>
              </div>
            </blockquote>
            <div><br>
              <div class="gmail_extra">Have you considered using
                incremental backup (continuous archiving) instead of a
                such small backup window?<br>
                <br>
                See [1]<br>
              </div>
              <div class="gmail_extra"><br>
              </div>
            </div>
            <blockquote class="gmail_quote" style="margin:0px 0px 0px
              0.8ex;border-left:1px solid
              rgb(204,204,204);padding-left:1ex">
              <div id=":lv" class="" style="overflow:hidden">
                My problem is, however, that while I'd like to just have
                it build a tarball, maybe compressed,  I can't use a "-X
                s" option for the wal segments. I think I understand why
                I can't use the streaming option with a "-Ft" specified.
                I'm just concerned about the docs saying that the backup
                may have problems with fetch as a wal segment may have
                expired. Manually testing is showing that the Db needs
                about 11 minutes to backup with pg_basebackup, and our
                wal_keep_segments setting is 6.  This said, an hour's
                worth of wal segments should be available, but the six
                that were there at the beginning of the backup are not
                the same six there at the end. I don't think this is
                really a problem, but I'd like to get it confirmed.
                Wouldn't the backup actually have to take more than hour
                for this to be an issue?</div>
            </blockquote>
          </div>
          <br>
        </div>
        <div class="gmail_extra">If you use archiving [1], you don't
          need to worry about saving the segments withing the backup,
          just let it be done through archive_command. Isn't that an
          option for you? If don't, why?<br>
          <br>
        </div>
      </div>
    </blockquote>
    Oh, yes, I did fail to mention that the system where I'm trying this
    is the primary in a streaming replication cluster with (2) hot
    standby servers. I've mentioned without much traction, that we don't
    really even need a backup with 3 servers ready to do the work
    without delays. Like I said in the last post, it's all political.<br>
    <blockquote
cite="mid:CAJghg4+JXVs1_k12M=Ozu_pzma0eBPQO++hA5b7C2vhXAh2-tg@mail.gmail.com"
      type="cite">
      <div dir="ltr">
        <div class="gmail_extra"><br clear="all">
          [1] <a moz-do-not-send="true"
href="http://www.postgresql.org/docs/current/static/continuous-archiving.html">http://www.postgresql.org/docs/current/static/continuous-archiving.html</a><br
            clear="all">
          <br>
          Best regards, <br>
          -- <br>
          <div class="gmail_signature">Matheus de Oliveira<br>
            Analista de Banco de Dados<br>
            Dextra Sistemas - MPS.Br nível F!<br>
            <a moz-do-not-send="true"
              href="http://www.dextra.com.br/postgres/" target="_blank">www.dextra.com.br/postgres</a><br>
            <br>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
  </body>
</html>

--------------010409080106080402030909--