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.

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