Обсуждение: Major upgrade from 9.6.15 to 12.2
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
On Tue, Mar 10, 2020 at 5:39 PM Elizabeth Fernandez <elizabethfordonez@gmail.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.3and 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
Hello!
For postgres i do not see any problems. For postgis you need to read the changes, there are possible isues. But for a first try you can keep your postgis version.
Bye Uwe
Am Di., 10. März 2020 um 22:39 Uhr schrieb Elizabeth Fernandez <elizabethfordonez@gmail.com>:
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.3and 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
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@keithf4.com>) escribió:
On Tue, Mar 10, 2020 at 5:39 PM Elizabeth Fernandez <elizabethfordonez@gmail.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.3and 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 advancedThere'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