Re: upgrade and migrate

These are great points. Thanks! I will push for some testing environment

________________________________
From: Justin <zzzzz(dot)graf(at)gmail(dot)com>
Sent: Friday, December 6, 2019 10:06 AM
To: Julie Nishimura <juliezain(at)hotmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; Stephen Frost <sfrost(at)snowman(dot)net>; Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>; Michael Paquier <michael(at)paquier(dot)xyz>; 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

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<mailto: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<mailto: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<mailto:sfrost(at)snowman(dot)net>>
Cc: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at<mailto:laurenz(dot)albe(at)cybertec(dot)at>>; Michael Paquier <michael(at)paquier(dot)xyz<mailto:michael(at)paquier(dot)xyz>>; Julie Nishimura <juliezain(at)hotmail(dot)com<mailto:juliezain(at)hotmail(dot)com>>; pgsql-general(at)lists(dot)postgresql(dot)org<mailto:pgsql-general(at)lists(dot)postgresql(dot)org> <pgsql-general(at)lists(dot)postgresql(dot)org<mailto:pgsql-general(at)lists(dot)postgresql(dot)org>>; pgsql-general <pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>>
Subject: Re: upgrade and migrate

Stephen Frost <sfrost(at)snowman(dot)net<mailto:sfrost(at)snowman(dot)net>> writes:
> * Laurenz Albe (laurenz(dot)albe(at)cybertec(dot)at<mailto: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