Thread: [GENERAL] pg_upgrade 9.0 to 9.6

Hi all,
We've tested upgrading from postgresql 9.0.x to 9.6.1 on the test environment and everything passed fluently. But there are concerns about the production environments upgrade because of notes to some interim updates that recommends reindex and/or vacuuming to exclude data corruption, index results and etc.
Some examples:
9.6.0 to 9.6.1 https://www.postgresql.org/docs/9.6/static/release-9-6-1.html
  if your installation has been affected by the bugs described in the first two changelog entries below, then after updating you may need to take action to repair corrupted free space maps and/or visibility maps.
9.5.1 to 9.5.2 https://www.postgresql.org/docs/9.6/static/release-9-5-2.html 
  you may need to REINDEX some indexes after applying the update, as per the first changelog entry below
9.4.5 to 9.4.6 https://www.postgresql.org/docs/9.6/static/release-9-4-6.html 
  if you are upgrading an installation that contains any GIN indexes that use the (non-default) jsonb_path_ops operator class, see the first changelog entry below.
9.4.1 to 9.4.2 https://www.postgresql.org/docs/9.6/static/release-9-4-2.html 
  if you use contrib/citext's regexp_matches() functions, see the changelog entry below about that
9.3.4 to 9.3.5 https://www.postgresql.org/docs/9.6/static/release-9-3-5.html 
  this release corrects a logic error in pg_upgrade, as well as an index corruption problem in some GiST indexes. See the first two changelog entries below to find out whether your installation has been affected and what steps you should take if so
... and so on.

Should i check all the production environments for the problems, mentioned in all interim versions release notes, is it enough only to check the last minor upgrade release note (9.6 --> 9.6.1) or there is another quick way to check if i should apply some additional actions to standard pg_upgrade+stats_collect?

Regards, Mikhail Balayan

On 12/13/2016 1:57 AM, Mikhail wrote:
> Should i check all the production environments for the problems,
> mentioned in all interim versions release notes, is it enough only to
> check the last minor upgrade release note (9.6 --> 9.6.1) or there is
> another quick way to check if i should apply some additional actions
> to standard pg_upgrade+stats_collect?

if you're upgrading directly from 9.0.x to 9.6.1, I would only check the
release notes for 9.0.latest (and upgrade to that, following those notes
and any 9.0.x release newer than your current which are referenced in
hte 9.0.latest notes), then do the pg_upgrade directly to 9.6.1

now, there could be application compatibility issues going from 9.0 to
9.6, those would best be addressed by 9.1.0, 9.2.0, ... release notes as
9.6 will include all those functional changes.


--
john r pierce, recycling bits in santa cruz

John, thanks! Your approach significantly reduces the number of checks.

Вторник, 13 декабря 2016, 13:34 +03:00 от John R Pierce <pierce@hogranch.com>:

Mikhail schrieb am 13.12.2016 um 10:57:
> Should i check all the production environments for the problems,
> mentioned in all interim versions release notes, is it enough only to
> check the last minor upgrade release note (9.6 --> 9.6.1) or there is
> another quick way to check if i should apply some additional actions
> to standard pg_upgrade+stats_collect?

There is a nice page which consolidates all release notes:

   https://bucardo.org/postgres_all_versions.html

The nice thing about that is, that for each change in one release, it also mentions if that was a change in other
releasestoo, e.g.:  

   https://bucardo.org/postgres_all_versions.html#version_9.6.1

Thomas

Thomas, very handy page, thanks for the link.

If I understand it correctly, ideally the upgrade process should look like:
9.0.x --> 9.0.23 + recommended fixes (the main is about table's relfrozenxid)
9.0.23 --> 9.4.5 (as the last version, where 9.0.23 was supported)
9.4.5 --> 9.4.10 + fixes
9.4.10 --> 9.6.1

Wednesday, December 14, 2016 12:55 PM +03:00 from Thomas Kellerer <spam_eater@gmx.net>: