Migrating a Java Spring application and its database from MariaDB to PostgreSQL using pgloader — a success story

Sooner or later in an application’s lifetime some “tough ”choices might be made, like switching the database — in this case from MariaDB to PostgreSQL.

The reasons for this switch, while being mostly of a technical nature, will not be addressed in this article. I will instead focus on the technical steps needed and challenges encountered.

The facts and the goal

The initial environment consisted of a MariaDB 10.x database serving a Java Spring application.

The database and the application were due to be migrated to PostgreSQL, version 11.

The plan

As the database migration was not necessary live, there were quite some options to choose from. I decided to first migrate everything to a temporary machine (having a similar version of MariaDB as the source and same version of PostgreSQL as the destination) as a testbed and then use it to export the data to the final destination.

The database migration

For reliability, freshness and simplicity of operation I chose to deploy a quick setup of Ubuntu Server 19.04 on a VM, but other options can be perfectly valid here for the reader.

Installing the needed packages is as simple as:

# apt-get install mariadb-server mariadb-client \
  postgresql-client-11 postgresql-11 pgloader

Setting up the local MariaDB replica can be done similar to:

# mysql -u root
> CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'Password1234';
> CREATE DATABASE `app_database`;
> GRANT ALL PRIVILEGES ON `app_database`.* TO 'app_user'@'localhost';

Replicating our remote working MariaDB database to our local intermediary server can be done by using a simple script, import_from_mysql.sh:

#!/bin/bash
REMOTE_PASS='AnotherPa$$w0rd'
REMOTE_USER=rem_user
REMOTE_DB=rem_db
REMOTE_SRV='someserver.domain.com'
LOCAL_PASS='Password1234'
LOCAL_USER=app_user
LOCAL_DB=app_database
mysqldump -u ${REMOTE_USER} -p${REMOTE_PASS} -h ${REMOTE_SRV} \
  --add-drop-table -R --single-transaction ${REMOTE_DB} | \
  mysql -u ${LOCAL_USER} -p${LOCAL_PASS} ${LOCAL_DB}

Notes:

• --add-drop-table is needed to make sure our local copy of the initial DB will be pristine on each iteration, if needed;
• -R is needed to make sure stored procedures are also dumped;
• -B is not used, as this will also include a USE `rem_db`; statement we might actually don’t want, if database names differ between local and remote;

Preparing PostgreSQL database:

# su - postgres
# psql
CREATE USER app_user WITH PASSWORD 'Password1234';
CREATE ROLE app_role;
GRANT app_role TO app_user;
CREATE DATABASE app_db OWNER app_user;
GRANT ALL PRIVILEGES ON DATABASE app_db TO app_user;
GRANT CONNECT ON DATABASE app_db TO app_user;

We can easily test the connection:

# psql -h localhost -U app_user -W -d app_db

, where -h localhost parameter is needed here, because without it, psql would try to connect using peer authentication by default, not password — and that won’t work.

Cleanup

The next logical step is to cleanup the database. It will differ from database to database and from application to application, but it needs to be pointed out in this article, because a lot of people carry on all the data in the DB, even if not actually needed. This might incur huge undesired performance penalties when importing.

Some might simply use, depending on their needs:

DELETE FROM `administrators` WHERE `id` != 1;
TRUNCATE TABLE `logs`;

, and so on.

pgloader

pgloader is quite a beautiful tool, which is available under the PostgreSQL free license. It can migrate data to PostgreSQL from SQLite or MySQL among others, while doing the proper transformations.

More about it can be read here:

https://pgloader.readthedocs.io/en/latest/

Migrating a database with it can be accomplished — theoretically at least — quite easily:

# USER='app_user'
# PASS='Password1234'
# pgloader mysql://${USER}:${PASS}@localhost/app_database \
 pgsql://${USER}:${PASS}@localhost/app_db

Note: in the example above the same user and password is used to connect both to MariaDB and PostgreSQL — so please adapt it to your needs.

First issue

On the first iteration of the pgloader command, I got a quick weird error message:

ERROR Database error 42701: column "id" specified more than once
QUERY: CREATE TABLE app_database.databasechangelog
(
  id            varchar(255) not null,
  id            varchar(255) not null,
  author        varchar(255) not null,
  author        varchar(255) not null,
  filename      varchar(255) not null,
  filename      varchar(255) not null,
  dateexecuted  timestamptz not null,
  dateexecuted  timestamptz not null,
  [.......]
FATAL Failed to create the schema, see above.

The error message was simply baffling. First, it shows that for a certain table, databasechangelog (which is part of Liquibase), all the fields (starting with id) are simply doubled. Second, the error always pointed to the same table, showing it’s not just some random error. But the table columns were obviously not doubled in the MariaDB database.

So what gives? After some wandering around on the wrong tracks, the problem finally became clear— the database had the same table defined twice, but with lowercase and uppercase names:

> show tables;
DATABASECHANGELOG
databasechangelog
[......]
> SELECT COUNT(*) FROM `DATABASECHANGELOG`;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.001 sec)
> SELECT COUNT(*) FROM `databasechangelog`;
+----------+
| COUNT(*) |
+----------+
|      115 |
+----------+
1 row in set (0.001 sec)

Checking the data and the code itself showed that just one of the tables was actually used. The other existed just because of some apparent malfunction of the Liquibase code somewhere in the past, but was not populated with useful data.

The error as it was reported by pgloader didn’t really help — it didn’t say the obvious: pgloader always converts identifiers (so also table names) to lowercase, unless specifically told not to do so using the switch --with "quote identifiers". So at this point, it was trying to create twice the same table structure.

Fixing this was very simple — drop the unused table: DROP TABLE `DATABASECHANGELOG`;

But this had left us wondering — should we accept the default behavior of converting all identifiers to lower-case or should we stick with the original ones when migrating to Postgresql?

After reading around, we went ahead with the default conversion to lower-case. And this has proven later to be for the best, as the Postgresql implementation in JPA and Hibernate driver itself seems to favor this, always converting identifiers to lower-case, unless otherwise specified (like quoting).

Second issue

Another problem that immediately popped-up:

ERROR PostgreSQL Database error 42804: foreign key constraint "categories_fk" cannot be implemented
DETAIL: Key columns "category_id" and "id" are of incompatible types: numeric and bigint.

This showed a issue with pgloader: it doesn’t always guess the best conversion rules for the data types. In this case it also makes it impossible to re-create the foreign keys. Fixing it involves using cast modifiers, like so:

pgloader --cast "type float to real drop typemod" \
 --cast "type int to integer drop typemod" \
 --cast "type bigint when (= 20 precision) to bigint drop typemod" \
 mysql://${USER}:${PASS}@localhost/app_database \
 pgsql://${USER}:${PASS}@localhost/app_db

So — MySQL’s float to PostgreSQL’s real, int to integer, and bigint with a precision of 20 to bigint. These were determined by trial and error. Your needs might be similar.

Using these casts the import went ahead with no error, and the data was present and accounted for.

Third issue

Analyzing the migrated database by hand also showed something not obvious at first: all DATETIME and DATE fields that do or do not accept NULLs and had a set or default value of 0000-00-00 were converted to accept NULLs and have a NULL value.

The conversion is documented in pgloader and makes sense, because Postgresql does not accept year 0000 (as it never actually existed).

This signals possible troubles later — application code needs to be checked to see if it actually expects and uses year 0000, and if it does, it should be changed to use NULLs instead.

The application

After carefully test-migrating the database, it was clear that the application itself needed a few code changes, because different issues were found.

The text column problem

Our application was using, as many others, columns of type text in MariaDB — mediumtext and longtext. These were migrated by pgloader to PostgreSQL’s text type, using the default cast rules:

type text       to text using remove-null-characters
type mediumtext to text using remove-null-characters
type longtext   to text using remove-null-characters

In the code itself they were annotated as follows:

@Column(name="some_column_name")
@Lob
public String getSomeColumnName() {
   return someColumnName;
}

Unfortunately, after the migration to PostgreSQL these were no longer properly accessible, crashing the application in critical points.

This happens because the implementation of such fields is vendor-specific, varying from driver to driver.

Searching around the web for the proper solution didn’t get any useful results — I found instead all kind of advises that simply didn’t work: changing the annotation to Clob or Blob, or maybe byte array bytea. Even a table detailing why we should not use text anymore — because what works with either MySQL/MariaDB or PostgreSQL or Oracle doesn’t work with the other two.

The solution was found to be quite stunningly simple: remove the @Lob annotation and replace it with the column type definition. So for our example above, the code should be changed to:

@Column(name="some_column_name", columnDefinition="text")
public String getSomeColumnName() {
   return someColumnName;
}

The transactional problem

The application was found to throw errors in certain points where transactions were used. More exactly, in a lot of places where read-only transactions were used. And always hinting that the errors were generated by the fact they were nested.

The same transactions were working just fine using the MariaDB driver.

These ones were harder to debug, but the problem became clear:

• never use read-only transaction directly at class-level unless you really know what you are doing;
• never use read-only transaction even at method level if nested with others that are not supposed to be read-only.

This proved to be quite a change from the way MariaDB driver used to handle these situations. This is not actually a bug, but a different way to actually implement the transaction model by PostgreSQL — and maybe a better one.

Solving it was simple — remove the read-only attribute where not appropriate:

/* @Transactional(readOnly = true) */
@Transactional

The reserved problem

PostgreSQL has certain reserved words, of which some were used as column names in the original MariaDB (like user), so the driver will quickly complain about this.

Possible solutions are obvious: quote the identifier everywhere in the code where present, if possible. Rename the column and change the code everywhere. Or just rename the column and add name annotation, for example:

@Column(name = "username", length=255)
public String getUser() {
  return user;
}

The last solution will imply minimal changes propagated around the code.

Liquibase

Do you remember that I was talking about Liquibase above? All the changes done to the DB in the course of the migration made all existent Liquibase changelogs obsolete.

Fortunately, Liquibase does has a very good mechanism to regenerate the change log anew, from an already existing database.

Using it will create one huge changelog file:

/opt/liquibase/liquibase --driver=org.postgresql.Driver \
 --classpath=/opt/liquibase/postgresql-42.2.6.jar \
 --changeLogFile=app_db.create.json \
 --url="jdbc:postgresql://app.server.org:5432/app_db" \
 --username=app_user \
 --password=ThePassw00rd \
 --changeSetAuthor="some@email" \
 generateChangeLog

This can be used as a very good starting point, from which all future changes can be derived.

Conclusions

pgloader turned out to be a very useful tool for database migrations. However, the user must take care that the initial DB is in the most possible sane state, and understand the casting rules (the defaults and the ones needed) and what they entail.

All PostgreSQL’s quirks and details of implementing the SQL standard and the driver itself must be taken in consideration — not all things that used to work on MariaDB will work anymore — some for a good reason.

Also, note that the initial estimation of time cost can be exceeded due to unexpected issues, like the ones above.

This article was written expecting that it will help someone that intends to do such a migration in the future. If not, at least I hope you had a good read.