Re: Major upgrade from 9.6.15 to 12.2

On Tue, Mar 10, 2020 at 5:39 PM Elizabeth Fernandez <
elizabethfordonez(at)gmail(dot)com> wrote:

> Hello,
>
> We have installed this versions:
>
> - Ubuntu 16.04.6 LTS Xenial
> - PostgreSQL 9.6.15 on x86_64-pc-linux-gnu (Ubuntu 9.6.15-1.pgdg16.04+1),
> compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
> - PostGIS lib version 2.3.3
> and we want to upgrade to PostgreSQL 12.2.
>
> Taking into account that there are three major versions between the source
> and the target, in addition to the PostGIS extension installed, is this
> upgrade possible?
> It would be better to upgrade one version at a time?
> Do you have any additional recommendation?
>
> Thanks in advanced
>

There's no reason to go one major version at a time in PG. You should be
able to go from any fairly recent version of PG (8.x or later) to the
latest in one straight shot. Your choice really comes down to either using
pg_upgrade or pg_dump(all)/pg_restore.

In most cases, pg_upgrade will work fine. What may be more difficult in
your case here is the PostGIS upgrade. That extension can be very tricky to
upgrade depending on your usage, so I would concentrate on reading the
release notes for that. The chain of other libraries dependencies between
its versions can be tricky.

If you don't need to upgrade PostGIS at this time, you may be able to get
by just upgrading PostgreSQL itself and keep PostGIS at the same version.
Otherwise, you may need to dump/restore as well as update PostGIS at that
time too.

So get a copy of your database going on a separate system and start testing
the different upgrade methods. Definitely do not want go upgrading your
main production system right away until you nail down a tested, working
method.

Keith