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
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.
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
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
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.
Create postgres db (Target server)
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.
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 $$
statements CURSOR FOR
SELECT tablename FROM pg_tables
WHERE tableowner = username AND schemaname = 'public';
FOR stmt IN statements LOOP
EXECUTE 'TRUNCATE TABLE ' ||
quote_ident(stmt.tablename) || ' CASCADE;';
$$ LANGUAGE plpgsql;
Migrate all tables data from MySQL to
postgres using dbeaver client
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
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)
You can compare the table's columns with the default
created in postgres. you can map from MySQL to postgres.
Change the below configuration as per
environment from where we are migrating.
Elasticsearch configuration (If we are using remote
Restart Liferay server after migrating