Migrating the Database

For support, maintenance, or performance reasons, you may decide to migrate from the database you use to one of the other databases Liferay supports. Though the exact steps and tools are beyond the scope of the documentation, there are some pitfalls you should avoid when migrating databases.

Liferay accesses databases through Service Builder, which maps Liferay’s data types to the most appropriate column types on the supported databases. When migrating databases, often people use various third-party tools to migrate the schema definition and data, but that’s where the “gotcha” is: these tools don’t know anything about Liferay’s data mapping. It is highly likely for them to pick the wrong column types and/or naming conventions when doing the migration.

For example, Liferay for different databases expects different types:

Liferay type	DB2	MySQL	Oracle	PostgreSQL	SQL Server
Boolean	`smallint`	`tinyint`	`number(1, 0)`	`bool`	`bit`
Long	`bigint`	`bigint`	`number(30,0)`	`bigint`	`bigint`
Text	`clob(2G)`	`longtext`	`clob`	`text`	`nvarchar(max)`
Varchar	`varchar`	`varchar`	`varchar2`	`varchar`	`nvarchar`

Additionally, Liferay’s naming conventions for table names and columns differ per database:

PostgreSQL: Lowercase → layoutset and headid

Oracle and DB2: Uppercase → LAYOUTSET and HEADID

SQL Server: Unchanged → LayoutSet and headId

MySQL: Depends on the database environment and its lower_case_table_names setting

Three problems can occur when using third-party tools to migrate to a different supported database:

Columns may be mapped to the wrong data type (i.e., varchar instead of varchar2 in Oracle; a number type instead of bool in PostgreSQL).
Tables and column names may be created using the wrong naming convention.
Index names can be lost, which creates a performance issue when they must be recreated.

For these reasons, third-party migration tools are only recommended for data migration, NOT schema migration.

Migrating to PostgreSQL

Liferay’s default database is PostgreSQL. Liferay has a beta, unsupported tool for migrating to PostgreSQL, which is documented here.

Migrating to Any Supported Database

You can still migrate to any supported database by allowing Liferay to create the schema and then migrating the data.

Configure the new database for Liferay.
Start Liferay and point it to the new database you just configured. Liferay creates its schema in the database.
Stop Liferay. Connect to the database and use SQL to delete the data in every table in the database (i.e., use TRUNCATE TABLE [Table]; or DELETE FROM [Table];). Now you have a blank Liferay schema in your new database.
Migrate the data from your old database to your new database, mapping the existing table and column names from the old database to the new database.

If You Are Upgrading

If you are migrating your database while upgrading Liferay, migrate the database on the old version of Liferay first. Make sure Liferay performs to your expectations on the new database before attempting an upgrade.

Capability:

DXP Self-Hosted Installation, Maintenance, and Administration

Resource Type: