Upgrade to PostgreSQL 12.x

[Saijin-Naib]

Similar to the move to Python3 and possibly to latest LTS (20.04), PostgreSQL 9.x is deprecated above 16.04 LTS and is replaced by PostgreSQL 10.x on 18.04.3 and 12.x on 20.04.

18.04.3:
https://packages.ubuntu.com/bionic/database/postgresql

20.04:
https://packages.ubuntu.com/focal/database/postgresql

[Saijin-Naib]

Thanks again, Piero. I still don't understand what dependencies are for each component only 🤣

Of course, we might be able to avoid the whole PGSQL upgrade dance with OGC GeoPackage ;)
https://community.opendronemap.org/t/ogc-geopackage-for-data-storage-back-end/3650?u=saijin_naib

[ghost]

@pierotofy I figured I would take a stab at this, since 9 is SUPER old... any reason we are using our own dockerfile vs using a base postgis image like postgis/postgis:14-3.3 and throwing in scripts in the init.db folders??

Switching to a base image should make it a lot easier to upgrade versions

[pierotofy]

We use our own (compiled from source) version of postgres/postgis, because they don't publish 9.x releases on docker hub anymore.

The problem with postgres/postgis, using docker, is that there's no easy way to perform a fully automated migration for dozens of thousands of users that are using WebODM via docker.

The (probably) correct way to perform the migration, is to create a new docker image which has the newer postgres/postgis version and perform the migration on startup. The procedure is messy, as postgres does not offer an automated way to do this. So it's going to be a bit tricky.

In the meanwhile, Postgres 9.x continues to work just fine (aside from being old, we haven't had a good reason to migrate).

We'd welcome help to do this, but note that simply updating to 12.x is not sufficient, the migration of existing data volumes from 9.x --> 12.x is also needed, and needs to be fully automated (work via ./webodm.sh update for all platforms).

[ghost]

That makes sense. I will start working on the migration scripts first then

[ghost]

https://www.cloudytuts.com/tutorials/docker/how-to-upgrade-postgresql-in-docker-and-kubernetes/

Seems pretty straight forward. So I'm thinking we add a upgrade_db option to webodm.sh. That will do a docker compose up to a new file docker-compose.upgrade_db.yml. This will stand up the new database. Then it will run the pg_dumpall on the old database. Then we will push the dump file into the new databse. Finally we will stop the old one. Since the base docker-compose would already have been "updated" it will work correctly going forward.

I am sure I'm missing some steps here. But that's where I will be starting

[smathermather]

Sounds great. I've got a server with lots of projects in it and the ability to snapshot it at the VM level, so I'd be happy to be a victim volunteer for testing.

[pierotofy]

The issue with the proposed approach is that it's not automated; it requires user intervention. Please bear in mind that for us technical folks using the command line this is not a problem, but there are lots of people that have installed WebODM via the installers from https://opendronemap.org/webodm/download/, and this approach is going to break their installation and does not provide a path for them to upgrade.

[ghost]

Who said anything about manual? Everything in that article I believe can be automated. If not, then maybe it's time to have a 2nd offering. I agree that many are not tech savvy, but having vulnerable software running on their computers can't be good either.

…

On Thu, Oct 13, 2022, 2:23 PM Piero Toffanin ***@***.***> wrote: The problem with the proposed approach is that it's not automated; it requires user intervention. Please bear in mind that for us technical folks using the command line this is not a problem, but there are lots of folks that have installed WebODM via the automated installers, and this approach is going to break their installation and does not provide a path for them to upgrade. — Reply to this email directly, view it on GitHub <#826 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/A3OZFKDIF4FT6BA2R6XGRWLWDBOTHANCNFSM4K5CDFLQ> . You are receiving this because you commented.Message ID: ***@***.***>

[ghost]

Ok so I'm moving over to the pull request I'm about to make. It's nowhere NEAR ready, but it's a start, and since I'm still very green with the codebase I will still need some hand holding! But running the commands by hand dumped and pushed the data from 9.6 to 14!

[NtskwK]

We use our own (compiled from source) version of postgres/postgis, because they don't publish 9.x releases on docker hub anymore.

The problem with postgres/postgis, using docker, is that there's no easy way to perform a fully automated migration for dozens of thousands of users that are using WebODM via docker.

The (probably) correct way to perform the migration, is to create a new docker image which has the newer postgres/postgis version and perform the migration on startup. The procedure is messy, as postgres does not offer an automated way to do this. So it's going to be a bit tricky.

In the meanwhile, Postgres 9.x continues to work just fine (aside from being old, we haven't had a good reason to migrate).

We'd welcome help to do this, but note that simply updating to 12.x is not sufficient, the migration of existing data volumes from 9.x --> 12.x is also needed, and needs to be fully automated (work via ./webodm.sh update for all platforms).

The version of PostgreSQL was been writen in $PGDATA/pg_hba.conf, we can use it to determine if we need to upgrade. Then, pg_upgrade can help us with data volume migration. There is a problem with live update functions. pg_upgrade can only be used when both database servers are stopped using. New servers can be stopped, but old cannot be stopped while webodm is running with it or webodm is running without database util new one has been launched.