Migrating Your Database from MySQL to PostgreSQL

Introduction

Liferay adopted PostgreSQL as the default database for PaaS and SaaS projects in October 2024. Since then, all new Liferay PaaS and SaaS deployments use PostgreSQL. To maintain compatibility and ensure optimal performance, consider migrating your existing MySQL databases.

This recipe walks you through the migration process using Liferay's built-in migration tools available in Liferay DXP 2024.Q4 and later. You'll learn how to export your MySQL data, import it into a PostgreSQL database, and configure your Liferay instance to connect to PostgreSQL.

Prerequisites

Liferay DXP environment

MySQL database

PostgreSQL database

A user who has administrative access to the database

A user who has administrative access to Liferay's Control Panel

Steps

Sign in to your Liferay DXP instance.
Open the Global Menu (), navigate to Control Panel, and click System Settings.
Under Platform, click Upgrades.
Go to the Database Schema Definition Exporter tab.

If you don’t see it, enable the Database Schema Definition Exporter System Settings (LPD-23840) feature flag. Open the Global Menu, go to Control Panel > System Settings > Feature Flags > Beta. After enabling it, refresh the Upgrades page.
Select PostgreSQL as the destination database type.
Specify the desired path in the Path field where the generated output files will be saved.

Click Save.

A success message appears and several log messages are generated in your application server logs. Examples:

2025-02-28 14:26:39.510 INFO  [CM Event Dispatcher (Fire ConfigurationEvent: pid=com.liferay.portal.db.schema.definition.internal.configuration.DBSchemaDefinitionExporterConfiguration)][DBSchemaDefinitionExporter:103] Start database schema definition export
2025-02-28 14:26:39.644 INFO  [CM Event Dispatcher (Fire ConfigurationEvent: pid=com.liferay.portal.db.schema.definition.internal.configuration.DBSchemaDefinitionExporterConfiguration)][DBSchemaDefinitionExporter:125] Finished database schema definition export to /home/me/database-recipe

On Liferay versions earlier than 2025.Q2, migrating Commerce data may trigger a bug (see LPD-53545). This recipe will be updated once the fix is backported to 2025.Q1.

Check the export report for errors. You'll find the report and database scripts in the directory you specified for export, which includes the following files:
- tables.sql: This file has the table creation script.
- indexes.sql: This file has the index creation script.
- db_schema_definition_export_report: This is the report generated during the export.
Shut down all Liferay nodes connected to the MySQL database.

The import tool requires the server to be stopped during the final copy to prevent users from inserting data during the process. Keep in mind that querying data may impact the source database's performance.
Once the Liferay nodes are shut down, ensure that your PostgreSQL database is up and running.
Create an empty database in the PostgreSQL server to import your Liferay database schema.

Navigate to the .../bundles/tools/portal-tools-db-schema-importer directory and run the db_schema_importer.sh script, replacing the placeholders with your environment-specific settings. For example:

./db_schema_importer.sh --path /home/[PATH-TO-EXPORTED-SCRIPTS] --source-jdbc-url jdbc:mysql://[MY-MYSQL-SERVER]:3306/[NAME-OF-MYSQL-DB] --source-user [MYSQL-USER-NAME] --source-password [MYSQL-PASSWORD] --target-jdbc-url jdbc:postgresql://[MY-POSTGRESQL-SERVER]:5432/[NAME-OF-MY-POSTGRESQL-DB] --target-user [POSTGRESQL-USER] --target-password [POSTGRESQL-PASSWORD]

Windows support for this tool is not yet available.

Review the db_schema_import_report file, generated in the specified path, for any errors.

The import process duration depends on the database size and the configuration of the MySQL and PostgreSQL servers.
Configure your Liferay node(s) to use the new PostgreSQL database by editing the portal-ext.properties file. Replace the MySQL configurations with the appropriate PostgreSQL settings, as shown in the example below.

Replace the placeholder values with your own.
```
# PostgreSQL Database Configuration
    jdbc.default.driverClassName=[POSTGRESQL-DRIVER-CLASS-NAME]
    jdbc.default.url=jdbc:postgresql://[MY-POSTGRESQL-SERVER]:5432/[NAME-OF-MY-POSTGRESQL-DB]
    jdbc.default.username=[POSTGRESQL-USER]
    jdbc.default.password=[POSTGRESQL-PASSWORD]
```
This configures the connection between Liferay and the PostgreSQL database.

Make sure all Liferay nodes are configured to use the new PostgreSQL database and not the old MySQL database. Additionally, create a PostgreSQL backup after migration but before launching Liferay to ensure a fallback restore point if needed.
After updating portal-ext.properties, start your first Liferay node up.
Once the server(s) have completed startup, open a browser and navigate to your Liferay DXP instance to confirm the migration was successful.

Conclusion

Congratulations, you've successfully migrated your MySQL database to PostgreSQL.

Tips

The migration time largely depends on the size of your existing MySQL database. Reducing its size can help speed up the process.

Tips

Stop your MySQL database before launching the Liferay node(s) to prevent any missed nodes from connecting to it. Keep the MySQL database as a fallback, but ensure Liferay cannot connect to and use it.

Tips

MySQL: The import tool only reads from MySQL, so configuring the database for read-only access can improve performance. For more details, refer to MySQL's official documentation.

Tips

PostgreSQL: The import tool writes data to PostgreSQL but does not read from it. For excessive runtimes, optimize configurations that prioritize writing while disabling unnecessary validations. The following settings have proven effective:

max_connections = 100
shared_buffers = 1024MB
synchronous_commit = off
wal_writer_delay = 1000ms
wal_writer_flush_after = 10MB
max_wal_size = 1GB
min_wal_size = 80MB

Use this only for excessive runtimes, and be sure to restore the original configuration after the migration is complete.

Tips

Start the migration on lower environments (development or test servers) and gradually move to higher environments as each migration succeeds. By the time you migrate the production instance, you'll have a clear understanding of the process, minimizing surprises.