H2 database upgrades

[Tim Stevens]

Hi, we are considering migrating from the H2 database to another database such as Postgresql.  My question is once we do that, if there are schema changes made to the H2 database, will there be sql scripts provided so that we can update the Postgresql database?

[Sriram Narayanan]

On Sun, Jun 12, 2022 at 10:44 PM Tim Stevens <timi...@gmail.com> wrote:

Hi, we are considering migrating from the H2 database to another database such as Postgresql.  My question is once we do that, if there are schema changes made to the H2 database, will there be sql scripts provided so that we can update the Postgresql database?

The various SQL scripts are here: https://github.com/gocd/gocd/tree/master/db-support/db-migration

Could you share what nature of schema changes have you made to the H2DB?

-- Sriram

 

--
You received this message because you are subscribed to the Google Groups "go-cd" group.
To unsubscribe from this group and stop receiving emails from it, send an email to go-cd+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/go-cd/82b219fb-0242-434e-beec-b81f0e1e3154n%40googlegroups.com.

[Chad Wilson]

I'm not sure I understand your question correctly, but GoCD manages its own database schema and will upgrade it automatically if necessary on first launch of any GoCD server version that has a schema change. These changes are supported across H2/Postgres/Mysql and are expressed in a DB-agnostic way. The need to manage schema is effectively why the documentation implies that the GoCD user needs quite broad permissions rather than DML alone.

Accordingly to migrate from H2 to Postgres, you'd need to do so with https://github.com/gocd/gocd-database-migrator using a similar process as noted in https://docs.gocd.org/current/installation/upgrading_go/upgrade_to_gocd_20.5.0.html to ensure the metadata for determining the schema version comes across correctly with your data.

Since you earlier asked about upgrading GoCD, if you haven't yet upgraded past 20.5.0, doing the switch to Postgres during the 20.4.0 to 20.5.0 upgrade is probably the best time since you have to do manual DB migration work at this step anyway.

-Chad

On Sun, Jun 12, 2022 at 10:44 PM Tim Stevens <timi...@gmail.com> wrote:

Hi, we are considering migrating from the H2 database to another database such as Postgresql.  My question is once we do that, if there are schema changes made to the H2 database, will there be sql scripts provided so that we can update the Postgresql database?

--