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.