Subject: Re: Strategy for upgrade highly used server
To: =?UTF-8?Q?M=c3=b3nica_Gamarra?= <monica.gamarra@gmail.com>,
 pgsql-admin@lists.postgresql.org
References: <5ce6203a-78cb-25b0-3b91-65179aa280e0@zunibal.com>
 <CAO6m0p3hBf1AoNpYNYQx01HaVZXaZrozjtsWj0hLbcmvZbLGyQ@mail.gmail.com>
From: Ekaterina Amez <ekaterina.amez@zunibal.com>
Message-ID: <42f0c04c-efeb-3388-7489-8dbfb75a3ca4@zunibal.com>
Date: Fri, 15 Nov 2019 10:13:41 +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: 
 <CAO6m0p3hBf1AoNpYNYQx01HaVZXaZrozjtsWj0hLbcmvZbLGyQ@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------857CE6E8E9B87410EB4050DE"
Content-Language: en-US
Precedence: bulk

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

Hi Monica,

Please, remember to answer to the list or your answer will be only 
available to me.

El 11/11/19 a las 14:37, Mónica Gamarra escribió:
> You say ...
> 1 -  we want to have the old version available  just in case newer one 
> gives us any problem.
> And also
> 2 - This server has problems with free disk space
> And
> 3 -  And the database needs to be up almost 24x7 (please describe 
> ALMOST in hours/min/sec and
> indicate if it applies to all dbs)
>
> 1 and 2 are not compatible for your task. If you can't solve 2 you've 
> only have pg_upgrade, so, you
> will lose old version.

Using pg_upgrade it's possible to have old and new postgres versions 
running. You only need minor reconfiguration. Of course, this is only 
possible if the --link option is not used, which is the option that 
needs less disk space.


>
> I you can solve 2  as  you need old version you must have double the 
> space of PG_DATA.
>
> You have to decide this before building a plan.
>
> Talking about 3, all dba's here this, negotiate.
>
> Your timeline depends on   main_db  (332 GB). You have to test 
> pg_upgrade time for this database
> in a similar server, and also test pd_dump/pg_restore.

Testing before any change in production environment is a must, always.


>
> IF databases are not related ... and only in this case you have 
> another option that is install new version
> and migrate or upgrade bid database first and then add databases db1 
> to db7  (pg_dump/pg_restore)
> one by one to new version (with this you minimize the amount of disk 
> space you need  for pg_upgrade
> and downtime).

I've used this strategy before, when upgrading other servers with 
databases without the almost-no-downtime needs.


>
> Choices.
> A) all with pg_upgrade
> - less space needed
> - NO old version
Wrong: this is only true if --link option is used. All the servers I've 
upgraded by now have both versions available, though the old one is down 
most of the time.
>
> B) pg_dump/pg_restore
> - you need double disk space
> - you will have to versions
> - more downtime needed (you need to test it no know how much)
>
> C) A combination of both - ONLY for desperates
> [ dump databases db1 to db7,  delete databases  db1 to db7 from old 
> version,  pg_upgrade  main_db, pg_restore db1 to db 7 ]
> - less space needed
> - NO old version
> - less downtime for main_db
>
> You have to TEST all plans before.
>
> Good luck!

Thank you for the schematic summary of this options (I' already knew). 
You've commented anything about replication option though.


>
> -- 
> /............................................
> Mónica Gamarra Barrios
> monica.gamarra@gmail.com <mailto:monica.gamarra@gmail.com>/

--------------857CE6E8E9B87410EB4050DE
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>Hi Monica,</p>
    <p>Please, remember to answer to the list or your answer will be
      only available to me.<br>
    </p>
    <div class="moz-cite-prefix">El 11/11/19 a las 14:37, Mónica Gamarra
      escribió:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAO6m0p3hBf1AoNpYNYQx01HaVZXaZrozjtsWj0hLbcmvZbLGyQ@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div>You say ...</div>
        <div>1 -  we want to have the old version available  just in
          case newer one gives us any problem.<br>
        </div>
        <div>And also <br>
        </div>
        <div>2 - This server has problems with free disk space </div>
        <div>And <br>
        </div>
        <div>3 -  And the database needs to be up almost 24x7 (please
          describe ALMOST in hours/min/sec and</div>
        <div>indicate if it applies to all dbs)</div>
        <div><br>
        </div>
        <div>1 and 2 are not compatible for your task. If you can't
          solve 2 you've only have pg_upgrade, so, you </div>
        <div>will lose old version. <br>
        </div>
      </div>
    </blockquote>
    <p>Using pg_upgrade it's possible to have old and new postgres
      versions running. You only need minor reconfiguration. Of course,
      this is only possible if the --link option is not used, which is
      the option that needs less disk space.</p>
    <p><br>
    </p>
    <blockquote type="cite"
cite="mid:CAO6m0p3hBf1AoNpYNYQx01HaVZXaZrozjtsWj0hLbcmvZbLGyQ@mail.gmail.com">
      <div dir="ltr">
        <div><br>
        </div>
        <div>I you can solve 2  as  you need old version you must have
          double the space of PG_DATA.</div>
        <div><br>
        </div>
        <div>You have to decide this before building a plan.</div>
        <div><br>
        </div>
        <div>Talking about 3, all dba's here this, negotiate.</div>
        <div><br>
        </div>
        <div>Your timeline depends on   main_db  (332 GB). You have to
          test pg_upgrade time for this database<br>
        </div>
        <div>in a similar server, and also test pd_dump/pg_restore.</div>
      </div>
    </blockquote>
    <p>Testing before any change in production environment is a must,
      always.</p>
    <p><br>
    </p>
    <blockquote type="cite"
cite="mid:CAO6m0p3hBf1AoNpYNYQx01HaVZXaZrozjtsWj0hLbcmvZbLGyQ@mail.gmail.com">
      <div dir="ltr">
        <div><br>
        </div>
        <div>IF databases are not related ... and only in this case you
          have another option that is install new version<br>
        </div>
        <div>and migrate or upgrade bid database first and then add 
          databases db1 to db7  (pg_dump/pg_restore) </div>
        <div>one by one to new version (with this you minimize the
          amount of disk space you need  for pg_upgrade </div>
        <div>and downtime). <br>
        </div>
      </div>
    </blockquote>
    <p>I've used this strategy before, when upgrading other servers with
      databases without the almost-no-downtime needs.</p>
    <p><br>
    </p>
    <blockquote type="cite"
cite="mid:CAO6m0p3hBf1AoNpYNYQx01HaVZXaZrozjtsWj0hLbcmvZbLGyQ@mail.gmail.com">
      <div dir="ltr">
        <div><br>
        </div>
        <div>Choices.</div>
        <div>A) all with pg_upgrade </div>
        <div>- less space needed</div>
        <div>- NO old version</div>
      </div>
    </blockquote>
    Wrong: this is only true if --link option is used. All the servers
    I've upgraded by now have both versions available, though the old
    one is down most of the time.<br>
    <blockquote type="cite"
cite="mid:CAO6m0p3hBf1AoNpYNYQx01HaVZXaZrozjtsWj0hLbcmvZbLGyQ@mail.gmail.com">
      <div dir="ltr">
        <div><br>
        </div>
        <div>B) pg_dump/pg_restore</div>
        <div>- you need double disk space </div>
        <div>- you will have to versions</div>
        <div>- more downtime needed (you need to test it no know how
          much)</div>
      </div>
    </blockquote>
    <blockquote type="cite"
cite="mid:CAO6m0p3hBf1AoNpYNYQx01HaVZXaZrozjtsWj0hLbcmvZbLGyQ@mail.gmail.com">
      <div dir="ltr"><br>
        <div>C) A combination of both - ONLY for desperates<br>
        </div>
        <div>[ dump databases db1 to db7,  delete databases  db1 to db7
          from old version,  pg_upgrade  main_db, pg_restore db1 to db 7
          ]</div>
        <div>- less space needed</div>
        <div>- NO old version</div>
        <div>- less downtime for main_db</div>
        <div><br>
        </div>
        <div>You have to TEST all plans before.</div>
        <div><br>
        </div>
        <div>Good luck!</div>
      </div>
    </blockquote>
    <p>Thank you for the schematic summary of this options (I' already
      knew). You've commented anything about replication option though.</p>
    <br>
    <blockquote type="cite"
cite="mid:CAO6m0p3hBf1AoNpYNYQx01HaVZXaZrozjtsWj0hLbcmvZbLGyQ@mail.gmail.com">
      <div><br>
      </div>
      -- <br>
      <div dir="ltr" class="gmail_signature">
        <div dir="ltr">
          <div><i>............................................<br>
              Mónica Gamarra Barrios<br>
              <a href="mailto:monica.gamarra@gmail.com" target="_blank"
                moz-do-not-send="true">monica.gamarra@gmail.com</a></i></div>
        </div>
      </div>
    </blockquote>
  </body>
</html>

--------------857CE6E8E9B87410EB4050DE--