Re: Strategy for upgrade highly used server

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(dot)gamarra(at)gmail(dot)com <mailto:monica(dot)gamarra(at)gmail(dot)com>/