Manual Migration from MySQL to Postgres

Migration from MySQL to Postgres for Liferay using Dbeaver

Before going forward we list out bottlenecks while migrating from MySQL to Postgres

  • Data types are different in Postgres than what we have in MySQL

  • If we have few tables to migrate we can convert MySQL dump(.sql) file to Postgres query using any tool such as pgloader but if the data is huge let’s say 4-5 GB, We won’t be able to do so.

  • Using pgloader we can migrate from MySQL to Postgres installed remotely but there we have lot of limitations such as

    • It will only work in Linux

    • Cross network connectivity

    • Permission issues

    • Compatibility Issues, MySQL 5.7 is not supported on linux 22.04.

  • Using paid tools is an option but it depends how much time and cost you want to spend buying these licenses.

 

Keeping these in mind we will be following these steps to migrate from MySQL to Postgres or any other DB. We will use Dbeaver for this, however it is little time consuming but we can afford that much time. Instead of spending time in licensing and spending overhead costs for this.

 

  1. Create postgres db (Target server)

  2. Point Liferay to newly created db

This will create a default schema as per postgres for Liferay, all default tables will be created with default data.

  1. Stop Liferay server, Truncate all the tables(Delete all default data)

Fresh schema will be left out. Use the below query to truncate all tables in a single go.

Create a procedure and then call it for once.

CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$

DECLARE

    statements CURSOR FOR

        SELECT tablename FROM pg_tables

        WHERE tableowner = username AND schemaname = 'public';

BEGIN

    FOR stmt IN statements LOOP

        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';

    END LOOP;

END;

$$ LANGUAGE plpgsql;

 

SELECT truncate_tables(<username>);

 

  1. Migrate all tables data  from MySQL to postgres using dbeaver client

    1. We can check which all tables are empty before exporting to postgres just to save time. We will be exporting only those tables which have some data in MySQL.

    2. Columns type we have to change from MySQL to postgres, if there is some doubt about columns type first check column type in postgres and map to that only.

Keep in mind to map columns type(tinyint→ bool)

(double→float8)

(blob→oid)

Etc….

You can compare the table's columns with the default created in postgres. you can map from MySQL to postgres.



 

  1. Change the below configuration as per environment from where we are migrating.

  • Portal-ext.properties

  • Portal-setup-wizard.properties

  • Elasticsearch configuration (If we are using remote no need)

 

  1. Restart Liferay server after migrating data.

  2. Sanity testing

0