Subject: Re: Strategy for upgrade highly used server
To: Victor Yegorov <vyegorov@gmail.com>
Cc: pgsql-admin@postgresql.org
References: <5ce6203a-78cb-25b0-3b91-65179aa280e0@zunibal.com>
 <CAGnEbohrCb+jGvZ+TU4PJ6jY2Q_ussqM=S0ttsqZhK_Zrx4x7w@mail.gmail.com>
From: Ekaterina Amez <ekaterina.amez@zunibal.com>
Message-ID: <d1ae3a03-bb89-96a0-4733-cdf21e8aa29e@zunibal.com>
Date: Fri, 15 Nov 2019 10:25:26 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.9.0
MIME-Version: 1.0
In-Reply-To: 
 <CAGnEbohrCb+jGvZ+TU4PJ6jY2Q_ussqM=S0ttsqZhK_Zrx4x7w@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------4205AB44174DBF8F39207690"
Content-Language: en-US
Precedence: bulk

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


El 11/11/19 a las 15:24, Victor Yegorov escribió:
> пн, 11 нояб. 2019 г. в 11:13, Ekaterina Amez 
> <ekaterina.amez@zunibal.com <mailto:ekaterina.amez@zunibal.com>>:
>
>     I'm trying to upgrade all postgres servers at my work place. I've
>     began
>     with oldest versions moving them to newer ones, basically from
>     8.4/9.2
>     to 9.6. When all are in 9.6, I'll go with v10 and after that...
>     we'll see.
>
>
> I would go to 11.6 straight away (11.6 will be available later this week).
> By making intermediate stops you'll make total downtime bigger.

I intended to have all database servers at the same version level, 
that's why I have chosen 9.6 (when I began with this task we had v7.4 in 
one of the servers, and now lower version in our database servers is 9.2).


>
>     We are going to free some space in main_db moving old data to another
>     server (~90GB) and changing the app that uses it but this db grows
>     quite
>     fast and we'll be probably in the same size in a few months.
>
>     I know that using --link would make pg_upgrade much faster but by now
>     we're not using it because we want to have the old version available
>     just in case newer one gives us any problem.
>
>
> Before upgrading master DB, we always make sure that we have physical 
> replica, that is up to date.
> Then we're free to use pg_upgrade in link mode. In any case, having 
> replica around for the major maintenance activities
> is a good thing to do in any case.
>
> For the upgrade, typical procedure is:
> - create a new cluster on the upgrade-to version (11)
> - open configuration files of new and old cluster side by side and 
> transfer settings.
>   Do not overwrite new configuration file, as it typically contains 
> quite some new options.
>   By overwriting the config, you will not be able to “see” them.
> - transfer pg_hba (and other) settings
> - transfer any custom extensions/FTS dictionaries/etc.
> - make sure pg_wal points to the right location, if you're using symlinks
> - do a schema-only dump of the old cluster and try to load it into the 
> new cluster.
>   If fails, correct errors, re-initdb new cluster and try again, till 
> schema loads fine.
> - run pg_upgrade in the `--check` mode to make sure no surprises will 
> pop up during the upgrade
>   (typical thing to remember — tablescpaces locations)
> - perform the upgrade
>
> We've been using this procedure without issues for years.

When you describe the upgrade procedure, do you mean upgrading the 
master or the slave? Do you promote server as master while upgrading 
original master? Let people access only to the replica in read-only mode 
while upgrading the master?


> -- 
> Victor Yegorov

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

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <p><br>
    </p>
    <div class="moz-cite-prefix">El 11/11/19 a las 15:24, Victor Yegorov
      escribió:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAGnEbohrCb+jGvZ+TU4PJ6jY2Q_ussqM=S0ttsqZhK_Zrx4x7w@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr">пн, 11 нояб. 2019 г. в 11:13, Ekaterina Amez &lt;<a
            href="mailto:ekaterina.amez@zunibal.com"
            moz-do-not-send="true">ekaterina.amez@zunibal.com</a>&gt;:<br>
        </div>
        <div class="gmail_quote">
          <blockquote class="gmail_quote" style="margin:0px 0px 0px
            0.8ex;border-left:1px solid
            rgb(204,204,204);padding-left:1ex">I'm trying to upgrade all
            postgres servers at my work place. I've began <br>
            with oldest versions moving them to newer ones, basically
            from 8.4/9.2 <br>
            to 9.6. When all are in 9.6, I'll go with v10 and after
            that... we'll see.<br>
          </blockquote>
          <div><br>
          </div>
          <div>I would go to 11.6 straight away (11.6 will be available
            later this week).<br>
            By making intermediate stops you'll make total downtime
            bigger.<br>
             </div>
        </div>
      </div>
    </blockquote>
    <p>I intended to have all database servers at the same version
      level, that's why I have chosen 9.6 (when I began with this task
      we had v7.4 in one of the servers, and now lower version in our
      database servers is 9.2).</p>
    <p><br>
    </p>
    <blockquote type="cite"
cite="mid:CAGnEbohrCb+jGvZ+TU4PJ6jY2Q_ussqM=S0ttsqZhK_Zrx4x7w@mail.gmail.com">
      <div dir="ltr">
        <div class="gmail_quote">
          <div><br>
          </div>
          <blockquote class="gmail_quote" style="margin:0px 0px 0px
            0.8ex;border-left:1px solid
            rgb(204,204,204);padding-left:1ex">
            We are going to free some space in main_db moving old data
            to another <br>
            server (~90GB) and changing the app that uses it but this db
            grows quite <br>
            fast and we'll be probably in the same size in a few months.<br>
            <br>
            I know that using --link would make pg_upgrade much faster
            but by now <br>
            we're not using it because we want to have the old version
            available <br>
            just in case newer one gives us any problem.<br>
          </blockquote>
          <div><br>
          </div>
          <div>Before upgrading master DB, we always make sure that we
            have physical replica, that is up to date.<br>
            Then we're free to use pg_upgrade in link mode. In any case,
            having replica around for the major maintenance activities</div>
          <div>is a good thing to do in any case.</div>
          <div><br>
          </div>
          <div>For the upgrade, typical procedure is:<br>
            - create a new cluster on the upgrade-to version (11)<br>
            - open configuration files of new and old cluster side by
            side and transfer settings.<br>
              Do not overwrite new configuration file, as it typically
            contains quite some new options.<br>
              By overwriting the config, you will not be able to “see”
            them.<br>
            - transfer pg_hba (and other) settings<br>
            - transfer any custom extensions/FTS dictionaries/etc.</div>
          <div>- make sure pg_wal points to the right location, if
            you're using symlinks<br>
            - do a schema-only dump of the old cluster and try to load
            it into the new cluster.<br>
              If fails, correct errors, re-initdb new cluster and try
            again, till schema loads fine.<br>
            - run pg_upgrade in the `--check` mode to make sure no
            surprises will pop up during the upgrade<br>
              (typical thing to remember — tablescpaces locations)<br>
            - perform the upgrade</div>
          <div><br>
          </div>
          <div>We've been using this procedure without issues for years.</div>
        </div>
      </div>
    </blockquote>
    <p>When you describe the upgrade procedure, do you mean upgrading
      the master or the slave? Do you promote server as master while
      upgrading original master? Let people access only to the replica
      in read-only mode while upgrading the master? <br>
    </p>
    <p><br>
    </p>
    <blockquote type="cite"
cite="mid:CAGnEbohrCb+jGvZ+TU4PJ6jY2Q_ussqM=S0ttsqZhK_Zrx4x7w@mail.gmail.com">
      <div dir="ltr">-- <br>
        <div dir="ltr" class="gmail_signature">
          <div dir="ltr">
            <div>Victor Yegorov</div>
          </div>
        </div>
      </div>
    </blockquote>
  </body>
</html>

--------------4205AB44174DBF8F39207690--