search

FraunhoferIOSB / FROST-Server

A Complete Server implementation of the OGC SensorThings API
https://fraunhoferiosb.github.io/FROST-Server/
GNU Lesser General Public License v3.0
194 stars 70 forks source link

Any way of gracefully updating to Version 1.12.0 (JSONB)? #374

Open riedel opened 3 years ago

riedel commented 3 years ago

We have a live sensorthings database of around 260GB on disk. We have yet to make the switch to more current versions due to the conversions done in 1.12.0.

Is there a way to gracefully perform the ALTER Table switch or actually even skip this switch while upgrading the code? Are there any best practices (like update a snapshot and sync new data)?

My suspition is that we might lose a few hours of data if we would upgrade due to the lock.

riedel commented 3 years ago

The features table takes 1,5 hours to convert

22:56:10.761 [            main] INFO     l.executor.jvm.JdbcExecutor - ALTER TABLE "public"."FEATURES" ALTER COLUMN "PROPERTIES" TYPE JSONB USING ("PROPERTIES"::JSONB)
00:23:19.196 [            main] INFO   liquibase.changelog.ChangeSet - FEATURES.PROPERTIES datatype was changed to JSONB
00:23:34.211 [            main] INFO   liquibase.changelog.ChangeSet - ChangeSet tablesString.xml::2020-05-15-toJsonB-3::scf ran successfully in 5243451ms

couldn't get to observations due to #375

hylkevds commented 3 years ago

You'll never lose data due to the upgrade. If the upgrade fails, the old state is restored. How the upgrade happens:

  1. a transaction is started
  2. the new column is added, with the value copied from the old column
  3. the old column is deleted
  4. the transaction is committed

It just takes a long time, and requires quite some disk space. For really large tables it may be more efficient to initialise a new database, and import the data.

On the other hand, frost should work normally on the old tables, so you don't have to do the upgrade to use the new FROST version.

riedel commented 3 years ago

It seems to me that the REST API does not respond and incoming data is not accepted. So we are losing all data that is send, don't we. Our embedded clients have no send queue.

hylkevds commented 3 years ago

Yes, that would be a problem, during the upgrade the table is locked, since the change affects all rows.

You could

  1. make a duplicate of the table schema (OBSERVATIONS2),
  2. copy the data to the duplicate,
  3. swap the two tables by renaming
  4. copy the rows that were created while the copy process was running

That should allow you to make the conversion without downtime. Once the new version is in place, the "upgrade" becomes a no-op, and should give no problems.