Port data from H2 to PostgreSQL

[Tim Stevens]

Hello, I have researched this question quite a bit, but am not finding anything that works.  I would like to take the data in our GoCD H2 database and upload it/convert it to a PostgreSQL database.

Any help would be appreciated!

Tim

[Chad Wilson]

The main way to do this migration is using https://github.com/gocd/gocd-database-migrator after preparing an appropriate empty PostGres you want to populate.

It *should* still be working up until GoCD 22.2.0 at time of writing (as there have been no db schema changes for a long time) but to my knowledge has not been explicitly tested beyond 20.8.0.

-Chad

--
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/ee37d34c-a591-4ce4-8e26-37fb39f4182an%40googlegroups.com.

[Ketan Padegaonkar]

What version of GoCD are you using?

--

[Chad Wilson]

Tim had already asked about upgrades in https://groups.google.com/g/go-cd/c/ugTXHPOXNoQ/m/tQ8ovKq8AQAJ and https://groups.google.com/g/go-cd/c/EuIqzYsl7mU/m/kkj8b_L3AwAJ - in May they were on 20.4.0, and I had suggested the best time to do a DB switch is probably during the 20.4.0 -> 20.5.0 upgrade since the use of that migrator tool is basically mandatory during that upgrade regardless of whether the plan is to stay on H2 or not (due to breaking change in H2 database version).

But not sure if they have subsequently upgraded while keeping to H2 or not. I had assumed he had, but perhaps that was a mistake, so yeah - it would be useful to know.

-Chad

To view this discussion on the web visit https://groups.google.com/d/msgid/go-cd/CAMUPJd6nZqdW_rjZ-MANh%3Dh_bRZEB7qZxxenbpfQOLDUjR%3DV1A%40mail.gmail.com.

[Tim Stevens]

Hello all, still on GoCD 20.4.0.  Has anyone successfully used the gocd-database-migrator.1.0.0 tool?  I have tried everything that I know to get it to run and I simply cannot.  I would be interested to know if anyone has been able to use the tool successfully?

[Ketan Padegaonkar]

Can you elaborate what issues you faced with the migrator tool?

- Ketan

To view this discussion on the web visit https://groups.google.com/d/msgid/go-cd/ff1d45dc-b65d-4943-9426-18df58ad00ffn%40googlegroups.com.

[Tim Stevens]

Sure Ketan I cannot get the tool to connect to my H2 database.  I am getting this error message when I try to run the tool:

ERROR [main] DbSync:103 - null
java.sql.SQLException: Cannot load JDBC driver class ''org.h2.Driver''
        at org.apache.commons.dbcp2.DriverFactory.createDriver(DriverFactory.java:54)
        at org.apache.commons.dbcp2.BasicDataSource.createConnectionFactory(BasicDataSource.java:472)
        at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:538)
        at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:753)
        at com.thoughtworks.go.dbsync.Util.withDataSource(Util.java:95)
        at com.thoughtworks.go.dbsync.DbSync.export(DbSync.java:77)
        at com.thoughtworks.go.dbsync.cli.Main.main(Main.java:46)
Caused by: java.lang.ClassNotFoundException: 'org.h2.Driver'
        at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(Unknown Source)
        at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(Unknown Source)
        at java.base/java.lang.ClassLoader.loadClass(Unknown Source)
        at org.apache.commons.dbcp2.DriverFactory.createDriver(DriverFactory.java:49)
        ... 6 common frames omitted
java.lang.RuntimeException: java.sql.SQLException: Cannot load JDBC driver class ''org.h2.Driver''
        at com.thoughtworks.go.dbsync.Util.withDataSource(Util.java:104)
        at com.thoughtworks.go.dbsync.DbSync.export(DbSync.java:77)
        at com.thoughtworks.go.dbsync.cli.Main.main(Main.java:46)
Caused by: java.sql.SQLException: Cannot load JDBC driver class ''org.h2.Driver''
        at org.apache.commons.dbcp2.DriverFactory.createDriver(DriverFactory.java:54)
        at org.apache.commons.dbcp2.BasicDataSource.createConnectionFactory(BasicDataSource.java:472)
        at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:538)
        at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:753)
        at com.thoughtworks.go.dbsync.Util.withDataSource(Util.java:95)
        ... 2 more
Caused by: java.lang.ClassNotFoundException: 'org.h2.Driver'
        at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(Unknown Source)
        at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(Unknown Source)
        at java.base/java.lang.ClassLoader.loadClass(Unknown Source)
        at org.apache.commons.dbcp2.DriverFactory.createDriver(DriverFactory.java:49)
        ... 6 more

This is what I am using to run the tool from an elevated command window:

gocd-database-migrator --insert --progress --source-db-url='jdbc:h2:/h2/cruise' --source-db-driver-class='org.h2.Driver' --source-db-user='go' --source-db-password='g0get!t' --target-db-url='jdbc:postgresql://localhost:5432/cruise' --target-db-driver-class='org.postgresql.Driver' --target-db-user='postgres' --target-db-password='postgres'

Thanks in advance for your help!

[Chad Wilson]

Are you able to check you have the entire contents of the lib directory in tact from the migrator tool distribution ? it looks like you might be somehow missing lib/h2-1.4.200.jar or it cannot be found/loaded by Java.

For what it's worth, you shouldn't need to do anything elevated here, as long as you have read permissions to the source H2 database file (which you have implicitly specified is available at /h2/cruise.h2.db in the below command.

To view this discussion on the web visit https://groups.google.com/d/msgid/go-cd/cb9b3591-2557-4e3d-9038-64ce67f92b77n%40googlegroups.com.

[Ketan Padegaonkar]

I see 2 issues:

1. You are probably running from the wrong directory. Run from the top level directory as bin/gocd-database-migrator

2. Your source db username/password may be wrong. The default H2 credentials are "sa", with a blank password

To view this discussion on the web visit https://groups.google.com/d/msgid/go-cd/cb9b3591-2557-4e3d-9038-64ce67f92b77n%40googlegroups.com.

[Tim Stevens]

Hi, thanks for the quick reply.  I ran from the top level directory and am getting this error now:

Error: Could not find or load main class com.thoughtworks.go.dbsync.cli.Main
Caused by: java.lang.ClassNotFoundException: com.thoughtworks.go.dbsync.cli.Main
Press any key to continue . . .

[Ketan Padegaonkar]

What OS are you using?

To view this discussion on the web visit https://groups.google.com/d/msgid/go-cd/cde02eea-4c6e-40dd-bce8-389d31c3bbcen%40googlegroups.com.

[Tim Stevens]

Windows Server 2022 Standard.  

[Chad Wilson]

Are you able to check you have the full zip/tgz unpacked in its original folders (scripts in bin/, jars in lib/ etc)? Something seems awry here, as if you don't have things in the folders that should be there.

It's also be useful if you can confirm which specific version you are using from the GitHub releases?

To view this discussion on the web visit https://groups.google.com/d/msgid/go-cd/71adaff6-d9b5-4900-92e1-f44810be40dcn%40googlegroups.com.

[Tsanko Stoev]

This may sound odd but can you try replacing the single quotes with double quotes in the command you are executing in Windows. I think I remember having to do that for the migration tool to work on Windows. 

Thanks.

Tsanko Stoev

To view this discussion on the web visit https://groups.google.com/d/msgid/go-cd/CAA1RwH_E4uuFUGyCfgkANaT4VBhW%2BvhMxKD9D5xSJa%2BO9SR88Q%40mail.gmail.com.

[Tim Stevens]

Thank you for the suggestion to change from single to double quotes - that was part of the problem.  Now, I am facing a different problem.  Chad, the version of the migrator tool that I am using is 1.0.0.  I also installed the H2 console application (can't tell you the version number) and in doing that, it created a test.mv.db file.  The H2 console application was able to connect to that empty database just fine.  The database that I have and am trying to convert to PostgersSQL in named cruise.h2.db.  The H2 console application cannot connect to my database at all.  It is throwing an error saying that the database C:\Users\Tim\cruise could not be found.  I'm not sure why this is happening, or how to fix it, but I am past the driver issues I was having with the H2 database driver - I am thankful for that.

Any advice would be greatly appreciated!

[Chad Wilson]

So is the migrator tool working now without errors to migrate your data into PostGres?

Not sure what you are trying to achieve with the h2 console (and support for this is really separate to anything GoCD related) but h2 versions have very limited limited backward compatibility. They don't use semver.

So your H2 DB from GoCD 20.4.0 and earlier won't be usable with h2 console 2.x if this is the version you downloaded. it may also have issues with h2 1.4.x rather than 1.3.x. if you want/need to browse with the h2 console you'll probably need to use the appropriate version for the database you're trying to open.

For wider context, the need for this forced db migration at GoCD 20.5.0 is partly due to such H2 breaking changes in H2 1.4.200. since GoCD did a migration in that version from H2 1.3 to 1.4 all GoCD H2 users are forced to do this migration.

To minimise number of manual migrations needed and give more flexibility to users to switch away from H2 this was combined with an internal GoCD switch to a different db deploy automation tool (dbdeploy -> liquibase) as well as open sourcing and inlining into GoCD core the previously commercial 'extension' support for running with non-H2 databases. The migrator tool was written to support all of these breaking change migrations in one GoCD upgrade.

-Chad

To view this discussion on the web visit https://groups.google.com/d/msgid/go-cd/f0d25489-e6ec-4e86-9bce-f6f14ecc3e25n%40googlegroups.com.

[Tim Stevens]

Thanks for the reply and information Chad.  What I am trying to accomplish with the H2 console, is simply connect to my cruise.h2.db file.  I haven't been able to do this yet.  No, the migrator tool is not working as it will not connect to my cruise.h2.db file regardless of the fact that I have the userid and password to the database.  I will try finding the appropriate version of the H2 console, as yes I would like to simply browse the database as a first step.

Thanks!

Tim

[Tim Stevens]

I have good news to report, I downloaded  Version 1.3.156 (2011-06-17) of the H2 database tools and I was able to connect to my database and browse it.  Now, when I try to run the migrator, it is throwing the same error as before:

2022-09-07 03:24:51,263 ERROR [main] DbSync:103 - null

java.sql.SQLException: Cannot load JDBC driver class ''org.h2.Driver''
    at org.apache.commons.dbcp2.DriverFactory.createDriver(DriverFactory.java:54)
    at org.apache.commons.dbcp2.BasicDataSource.createConnectionFactory(BasicDataSource.java:472)
    at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:538)
    at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:753)
    at com.thoughtworks.go.dbsync.Util.withDataSource(Util.java:95)
    at com.thoughtworks.go.dbsync.DbSync.export(DbSync.java:77)
    at com.thoughtworks.go.dbsync.cli.Main.main(Main.java:46)
Caused by: java.lang.ClassNotFoundException: 'org.h2.Driver'

    at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:641)
    at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:188)
    at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:520)

    at org.apache.commons.dbcp2.DriverFactory.createDriver(DriverFactory.java:49)
    ... 6 common frames omitted

Any help would be appreciated!

Tim