Re: Major upgrade from 9.6.15 to 12.2

Thank you for your recommendations!

I have a copy of the production system and I'll test the upgrade there.

Elizabeth

El mar., 10 mar. 2020 a las 22:07, Keith (<keith(at)keithf4(dot)com>) escribió:

> 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
>