What's the recommended way to migrate from H2 1.3.175 to 1.4.195

Question

Now that H2 1.4 is out of beta, I'd like to migrate my old 1.3.175 database to 1.4.195.

Background info:

• In the docs, database upgrade does not mention 1.4 yet.
• The roadmap still lists "Automatic migration from 1.3 databases to 1.4." as "planned change".
• The current state of MVStore is still labeled as "experimental".

So, what's the recommended way to migrate?

Additional aspects/bonus questions:

Should I enable MVStore or stick with PageStore (pros/cons)? Which one delivers better performance (multithreading is not important for me), which one better stability, especially resilience against OutOfMemoryErrors?

Answer 1

A database created with 1.3.175 can be read and opened with 1.4.195 without any additional work. H2 will automatically detect that it is using the Page Store and treat it as such. There will be no problems with doing this.

The advantage to doing this is that while the MVStore was being developed, the Page Store continued to receive performance improvements and bug fixes. Consequently H2 with the Page Store has become an extremely stable database store.

There is as yet no automatic upgrade procedure for converting a database from using the Page Store to using the MVStore. If you do want to do this, you'll need to do it manually. With the latest H2 Jar, use H2's SCRIPT command to export SQL from your 1.3 database, then use RUNSCRIPT into a freshly created db with 1.4.195.

If your H2 JDBC URL doesn't explicitly specify ;mv_store=false, note that H2 will first look to see if a page store database already exists. If it doesn't then it will create an MVStore database. This will appear seamless to you, your app, and your users. The only superficial difference you'll notice is that the database file on disk has a different file extension.

Finally, a suggestion. If your customer databases are large, consider only using the page store. I'm a heavy user of H2. (My commercial product built on H2 has thousands of users who typically have databases many gigabytes in size.) I still use the page store for all my customers, even though I use the latest H2 Jar. There are still some performance issues with the MVStore that start to appear as databases get large. With time, I expect the cause of the problems to be identified and fixed.