Re: upgrade and migrate

Off topic but food for thought given the jump in versions 8.3 to (9.6 or
greater.)

List of major changes i can think of to watch out for that can bite without
warning..

SQL operators~=~ for Like were drop in 8.4 => for hstore was drop in 9.0
in 9.1 standard_conforming_string is ON by default previous the default
was Off..
9.4 affected arrays.
9.5 operator precedence was changed this affects output and near
impossible to find all the code it can affect..
10 removed contrib/tsearch2 (added to the core in 8.3)

There are allot more minor changes that can bit....

Has a test machine been setup and run yet?? just dumping the schema and
restore to later version will point out several issues with custom
data-types, plpgsql, other PL languages and any missing extensions...

On Fri, Dec 6, 2019 at 11:16 AM Julie Nishimura <juliezain(at)hotmail(dot)com>
wrote:

> Thank you everybody for your suggestions.
> So, to summarize - we can run pg_upgrade from 8.3 to 9.4 (in place), fix
> app related issues (if any), then migrate to a version more recent than
> 9.6, either through pg_basebackup or through logical replication (if we
> would upgrade to version 10).
>
> First step requires downtime. Second does not. Correct?
>
> -Julie
>
> ------------------------------
> *From:* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> *Sent:* Wednesday, December 4, 2019 5:28 AM
> *To:* Stephen Frost <sfrost(at)snowman(dot)net>
> *Cc:* Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>; Michael Paquier <
> michael(at)paquier(dot)xyz>; Julie Nishimura <juliezain(at)hotmail(dot)com>;
> pgsql-general(at)lists(dot)postgresql(dot)org <pgsql-general(at)lists(dot)postgresql(dot)org>;
> pgsql-general <pgsql-general(at)postgresql(dot)org>
> *Subject:* Re: upgrade and migrate
>
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > * Laurenz Albe (laurenz(dot)albe(at)cybertec(dot)at) wrote:
> >> Right, Slony is the way to go, since pg_upgrade doesn't support 8.3.
> >> I would upgrade to a version more recent than 9.6.
>
> > So... there's a bit of history here. pg_upgrade in 9.4 actually does
> > support upgrading from 8.3.X. Support for upgrading from 8.3 was
> > removed in 2209b3923a7afe0b6033ecfea972219df252ca8e.
>
> Yeah. Also note that 8.3 to 9.6-or-newer is going to be a pretty
> huge jump in terms of minor compatibility issues (have you read
> all the relevant release notes?). So there's something to be said
> for breaking this down into two steps: update to 9.4, test/fix
> your applications against that, then make a second jump to something
> current. Each of those jumps could be handled by the respective
> version of pg_upgrade. I concur with Laurenz's advice that stopping
> at 9.6 is probably not your best choice for a migration today.
>
> regards, tom lane
>