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?

Ionian

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.

Catwalk

I'm wondering—if I stick with PageStore, do I need to dump and recover the database as it's often recommend in the H2 context when upgrading from 1.x to 1.y? – Ionian

@hendrik, between 1.3.175 and 1.4.191 there's no need to dump and recover. The advice you read is more important for moving from 1.1 or 1.2. – Catwalk

Does the PageStore support MVCC in 1.4 version? And is multi threading enabled? Our initial observation indicates that multi threading is not enabled. We would like to have MVCC and multi threading BOTH enabled, so I guess when migrating from 1.3 to 1.4 we need to manually use the H2's SCRIPT command? – Lanciform

Answer 2

@Steve McLeod's answer is on point. For completeness, here are the exact commands:

//Do a backup of current .h2.db file
//Connect to current DB with same URL as always

SCRIPT TO 'fileName'

//Rename the .h2.db to something else, possibly another backup
//Connect to database with same URL as before. The new MVStore engine will be chosen by default, and the .mv.db file will be created

RUNSCRIPT FROM 'fileName'

Documentation, H2 Grammar

Moreover, if you prefer using the H2 jar for this, refer to Thomas's answers (1 and 2). Concretely, the corresponding classes are org.h2.tools.Script and org.h2.tools.RunScript

Unterwalden

Answer 3

To highlight another alternative for similar requests I would like to mention a tool which allows an automatized migration of an old H2 database into a new H2 database:

https://github.com/manticore-projects/H2MigrationTool

Cardoon