Changing Databases

Introduction

I've worked with clients that, for one reason or another, needed to change
their database platform.

This ask has increased with the move to DXPCloud and MySQL database.

I'm not sure there's a well defined plan for how to migrate a Liferay
database, so I thought I'd blog about it.

Now, if you know anything about me, you know I'm a planner and I create
(sometimes intricate) processes to complete a task, and this is no
different.

It is important to understand why this process is so detailed...

The foundation of any Liferay environment is its database. If the foundation
(database) is weak, the house (LIferay) may crumble.

So yes, I put a great deal of time and effort into defining this process
because I want to have the strongest foundation possible for the Liferay
environment going forward.

Any misstep will be hard to recover from in the future; if you complete a
migration, let the environment run for a week and then realize something
didn't migrate correctly, well at that point you're in big trouble. You have a
weeks worth of data and changes that might be difficult or impossible to
discard, and something from the old DB that needs to be moved over... It can
be a real disaster.

So sure, this process is detailed and thorough, but hopefully the reasons for
that are clear now, so let's get started.

Oh, one more thing - Let's not do this in production first, yeah? Test out
your migration plan in the lower lanes, it will give you an opportunity to
evaluate the plan and get it working to your satisfaction before you attempt
it in your production environment.

Preparation

There's a couple of things to do before the DB migration...

Prune the Database

The first thing you should consider is cleaning up your database. Any cruft
you have in the database is cruft you have to move and cruft that will be in
your new database.

Eliminating the cruft will help to speed up the actual migration process, too.

Liferay has a page to help with data pruning here:
https://learn.liferay.com/dxp/latest/en/installation-and-upgrades/upgrading- liferay/upgrade-stability-and-performance/database-pruning-for-faster- upgrades.html

Alternatively, you might want to try my DB Pruner to help automate some of
this effort: https://web.liferay.com/marketplace/-/mp/application/167037532

Download the DB Driver

If moving to a commercial database or even to use a later driver on an open
source database, you may need to download the JDBC driver for Liferay use.

In versions up to 7.3, the driver jar file would go in Tomcat's lib/ext (or
equivalent on other platforms). For 7.4+, the driver jar would go to the
ROOT/WEB-INF/shielded-container-lib directory (unless you're using JNDI, in
which case you use Tomcat's lib directory).

Create the Target Database

Whatever database you're migrating to, you'll need to create the Liferay
database and credentials so Liferay will be able to connect.

A few items to remember:

Create a database that uses (at least) UTF-8 as the character set. If you
plan on supporting emoticons, use UTF-16.

Create a user for Liferay to connect as. This user needs to be able to create,
alter and delete tables and indexes. The easiest path is often to create a
special database for Liferay tables and create a user that has DBA privileges
but only on that special database; use different connections for other
business tables and databases. This way Liferay will have the ability to do
what it needs on its own database, but you limit access to the business data
to only what Liferay requires.

Follow the target database guidance with respect to setting up tablespaces,
datafiles, and database engines.

Create the Tables

So this may seem weird, but the next step is actually to configure your
Liferay environment by pointing it at the new database and then starting it so
Liferay can create all of the tables.

In some of the migration tools that I'll be covering in the next section will
not say something like this is necessary. The tools will make decent guesses
about what a column type should be in the target database given the column
type in the source database.

Most of the time the tool likely would make the right guess about what the new
column type would be. But me, I'm like "why risk it?"

Seriously, Liferay knows what the tables should be called in the target
database, how they should be capitalized, etc., so let Liferay create the
tables for you.

Afterwards you are free to truncate the tables to make them ready to receive
the migrated data, but the types and sizes and what not will be exactly what
Liferay expects to find.

Understand Capitalization Concerns

One issue that consistently trips people up are the internal database
capitalization rules.

MySQL is probably the worst here as they have different capitalization rules
depending upon which platform is hosting the database. In Windows, tables are
case insensitive, in Linux tables are case sensitive, and on MacOS they might
be case sensitive depending upon which filesystem you're using.

The point here is that you need to know what the capitalization is in the
source database as well as in the target database as there may be a need to
"massage" the process with a little bit of remapping.

So going from MySQL on a Windows host to MySQL on a Linux host, you could face
problems because a statement like CREATE OR REPLACE TABLE user_ would update
an existing User_ table Liferay generated, but it would be a completely new
table on the Linux side, resulting in a User_ table and a user_ table
and bad outcomes will stem from that.

In any route you take to migrate the data, be sure to take capitalization into
account so your data ends up in the right table for Liferay to use.

One case that stands out... A client migrated their Windows MySQL database
over to Linux, then they fired up Liferay pointed at the new database
expecting to see all of their existing content still there, but found that
they had a clean, empty Liferay database! Since Windows is not case
sensitive, all of the tables were in the Linux database as lower case; when
Liferay was looking for the Release table, it didn't find one (there was the
release table, but that failed the lookup) and so Liferay created all new
tables. All of their tables were effectively duplicated; lowercase tables from
the source database and mixed case tables from the Liferay startup.

You can use the lower_case_table_names property in MySQL to control how the
tables will be named, but you could also take care to map old names to new
names and avoid having to track and manage that property separately.

Migrate the Data

Note that this section is titled Migrate the Data and not Migrate the
Database... Because of our last prep step in the last section, the tables and
indexes and everything have all been created, so the only thing left to do is
to migrate the data.

There are a bunch of different strategies for you to try here, let's review
the possibilities...

Manual Data Migration

This one is likely the most labor intensive.

Basically for your source tables, you want the source database native tools to
export the data as SQL INSERT INTO statements.

With these files, you can use the target database native tools to run the SQL
and insert the data into the new Liferay database.

This is obviously the most tedious as it gets repeated across all of the
source tables, but it can be an effective way to handle the table renaming
issue from the previous section... Just use an editor to do a global search
and replace of the lowercase name to the mixed case Liferay uses and you'll
have the right outcome.

SQLines Data

Tooling can help avoid the manual migration effort. One open source tool to
consider is SQLines. It supports multiple different
flavors of open source and commercial databases. Since we let Liferay create
all of the target tables, we really only need to move the data using SQLines
Data which is the command line tool for
migrating data from the source to the target database.

To deal with possible table name mapping (for the capitalization issue
discussed before), the SQLines Data tool has built in support for table name
mapping.

It has plenty of other features to help you move data from the source database
to the target database.

MySQL Workbench

If you are moving to MySQL or MariaDB, MySQL
Workbench can help with
the migration.

While the tool does allow for table name mapping, it can be difficult to
process mass table renames prior to starting the migration.

So this tool is best used when you're doing a simple data migration w/o table
name remapping, but if remapping is necessary I'd consider another tool
instead.

SymmetricDS

The open source tool I've had the most success with is
SymmetricDS. It is written in Java and based
on a fork of an old and long since deprecated Apache database project, but it
is alive and well. Since it is written in Java it is cross-platform and can
run on any host you want.

SymmetricDS has documentation specifically to support data
migration.

Using this method, there is normally a two step process, the first is to
export the data to a file and the second is to import the data into the target
database. Since a file is holding the data, you can easily complete the
search/replace in the file to change table names for case sensitivity issues.

Target Database Native Tools

Many target databases will come with their own tools to support migrating onto
their database. Oracle's SQL Workbench, for example, can help migrate from
other vendors into an Oracle database.

Features and capabilities and even availability though will vary depending
upon the platform that you're moving to, so your own mileage may vary here.

Try, Try Again

The rule in selecting a tool from this list: Try others until you find one
that works.

Not all tools are going to be right for every situation. Some may handle your
data better than others. Some will be able to handle the inserts without
hitting constraint problems that cause problems on others.

Give them a try until you find one that gives you a clean migration and passes
the testing phase.

Conclusion

Well, it's the conclusion of the blog, but not the conclusion of the process.

You've just completed your data migration onto the database that Liferay
created for you. If you completed the database pruning earlier you have a slim
database ready to go.

But is it ready to go?

Maybe, maybe not. You still want to complete a round of testing. Make sure
your articles are in the new database, all of your users and permissions, all
of the documents, workflow, etc. Do a complete test of your environment to
ensure it actually is ready to go.

Hope this helps with your Liferay Database Migration needs!

DXP Cloud Conclusion

Hey, so although this blog is generally about Liferay database migration, more
often these days it is being driven by a move to DXP Cloud.

Effectively everything I've blogged about here still absolutely applies,
you're just going to be doing this work on a local MySQL database, not the one
in the cloud.

Complete the work to migrate onto MySQL, and complete all of the testing to
verify your MySQL local database is acting exactly as you expect.

When you're done with testing, you'll take a dump of the local MySQL database
and will use that to populate your DXP Cloud database.

When you get to this point and want help loading your dump to your DXP Cloud
environment, the DXP Cloud support team will be happy to help you with that.

link