Data Migration in a Post-7.x World

How to complete a successful data migration

Introduction

In the earlier days of Liferay, many administrators missed the warning about not using HSQLDB in production.

By default, Liferay is configured to use HSQLDB so you can spin up a Liferay instance for development or testing or whatever, but HSQLDB was never to be used in production because it is not a production-ready database.

It's easy to set Liferay up to use a "real" database; create a new database with support for UTF-8 and the right correlation, point Liferay at it in the portal-ext.properties, then start Liferay up and it will take care of the rest.

But sometimes an administrator would miss this step or not get it set up correctly, but for whatever reason they would find they were using HSQLDB after they had completed site setup, page creation, data loads, etc.

They needed a way to get all of this data out of HSQLDB and into their real database and needed help with it.

And the Liferay Data Migration tool was born.

Available in the Server Admin control panel, the Data Migration tool would be used to get Liferay data from HSQLDB and populate a target "real" database so portal use could continue.

Tool Abuse

Awesome, right? Well not so much.

It started to be used to help migrating from any database to another, i.e. moving from Oracle to Postgres, long after an initial Liferay setup fail situation. And that's when the problems with the tool became apparent.

Any custom client tables are also not handled. So the migration might appear to work from a tool perspective but fail operationally.

The tool also wasn't really tested for this kind of thing, so support was not really available for the tool.

Ultimately because of these and other problems with the tool, it was deprecated in 7.0 and eventually removed.

How to Migrate a Liferay Database

So there are two ways to handle this kind of data migration.

1. Use Vendor-supported tools.

Many database vendors provide tools to assist migrating onto their platform. This can be an easy way to bring things over to the new database.

Don't assume, however, that the vendor's tool did the job correctly. I would recommend pointing your Liferay environment at a new database and let it create the tables, indexes, etc and then compare the DDL of the two and ensure they are the same. You want to verify that names, sizes, types, indexes, etc all match. This should ensure that not only did the migration work, but also everything is just the way Liferay would expect.

Aside from vendor tools, there may also be some third party tools (such as FlywayDB amongst others) to handle a migration, but you'll want to follow the same steps to verify.

2. Manual Migration

A manual migration may sometimes be a path to consider. If the vendor supported tools don't or can't convert the database correctly, or if the third party tools are too expensive, a manual migration might be your only option.

And it's not as hard as it could be for other databases. But let's start with the process...

First, you create the target database you'll be migrating to. Next, you point Liferay at the database and start it up, allowing Liferay to create all necessary tables, indexes, etc.

Once that is done, you can use your DB admin tool of choice to truncate all of the tables.

Finally, export the current database as insert statements, then run the scripts against the new database.

Why is it easier to migrate Liferay this way rather than other databases? Liferay doesn't use foreign keys. This means the tables can be loaded in any order and there are no foreign key constraints to get in the way.

It ends up being a pain and a lot of grunt work, but at the end of the day you'll have a migrated database ready for Liferay to use.

Final Steps

So your database has been migrated, so what's next?

Well you stop Liferay, edit portal-ext.properties to point at the new database, then start it up.

I would recommend reindexing everything; the index itself is not technically out of sync, but it can be a quick way to allow Liferay to verify all of the table data while attempting the reindex.

Conclusion

One final suggestion - treat the database migration as a project.

Approach it with a plan, document all of the steps, complete a POC using the available tools or manual steps, test the migration itself and fully test the outcome. Have a mitigation plan ready if the production migration fails, you want to be able to fall back to the current database.

In this way you will maximize the success of your migration effort while minimizing the risks.

My own recommendation? I'd use the 3rd party tools to move the data, but not the schema. Point Liferay at your target DB and start it up, let it create all of the tables, indexes, etc that it expects to be there. Then shut Liferay down and use the tools to move the data only.

Why such a manual process? I'm overly concerned with the 3rd party tools making a bad decision about column types, collation issues, sizing issues, etc. I'd rather let Liferay create the stuff it expects so the columns, types and sizes will all be correct from a Liferay perspective. And then by letting the tools move the data, everything should come over in the shortest time possible.

5
Blogs

Data migration was a great tool in liferay 6 and removing this feature was wrong.

I think this help us by migrating core tables that was the largest part of data and then we can migrate my custom porlets tables manualy.

Although I remember that data migration was based on models, which could have migrated all the portlets.

Well, unfortunately most users expected a perfect solution rather than as a basic starting point. It caused more support issues than it solved. Besides, when you get a look at some of the third party tools, you can find some really elegant solutions that can deal with the whole conversion, rather than just part.

I've just migrated a system from Hypersonic to MariaDB using David's 'Manual Migration' recommendations.

 

Depending on your environment the below may or may not be the case, however in my case I also had to:

 

1. In the SQL insert statement import, I had to convert the table names from all uppercase (Hypersonic) to mixed case (MariaDB), before the INSERT commands would find the table to insert into. I couldn't find a quick process to do that so ended up with find/replace of the table names for each table in the SQL script.

 

2. The Unicode text wasn't converted automatically on import into MariaDB so HSQL data like '\u000d' was imported into MariaDB as the literal text rather than a single Unicode character (even though the DB was in UTF8MB4 mode). I used Notepad++ HTMLTag plugin to convert the SQL script as described in: https://stackoverflow.com/a/39126723

 

3. On startup the Liferay then failed to start with configuration errors after 'Starting initial bundles'.

I traced this back to the values in the 'Configuration_' table in the database.

In particular this seemed to be issues of having an equals sign in quoted text - these needed a backslash added.

Eg. from:

osgi.jaxrs.application.select="(osgi.jaxrs.extension.select=\(osgi.jaxrs.name=jaxb-json\))"

to:

osgi.jaxrs.application.select="(osgi.jaxrs.extension.select\=\\(osgi.jaxrs.name\=jaxb-json\\))"

 

Same thing in one entry that had:

mapping=["DELETE,PATCH,POST,PUT=everything,everything.write" ...

 

Hope this helps anyone in the same scenario.

Cheers, Tony

Ugh, HSQL migrations can be especially painful. It's the big reason Liferay actually puts in your log, "Hey, you're using HSQL but you really shouldn't in production..."

 

If I could ask, Tony, how you might have missed that message and what, if anything, might have been done to stop you from getting to that situation in the first place?

Hi David - it's just a Dev server and we didn't realise that it would need proper DB access down the line. Don't worry, our prod and non-prod servers do use a DB.

 

But when I started, your blog made it look easier to do the migration than re-add all dev deployments, users, sites, pages, roles, etc. Thankfully it only took a few hours even with figuring out those notes.

 

Cheers, Tony