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