Migrate MySQL to PostgreSQL

As part of Umami v3, we are removing MySQL as a supported database option and standardizing on PostgreSQL. This change ensures a more consistent and scalable data layer across all environments.

Data migration#

This document outlines the migration process from MySQL to PostgreSQL, including:

• Preparing a MySQL dump file or CSVs.
• Verifying data integrity and application compatibility.

The goal is to make the transition seamless, while preserving all data and ensuring minimal downtime. There are open-source tools like pgloader and pg_chameleon that you may explore. The process outlined below is a pure data migration into a new v2 PostgreSQL database, making this method as simple and agnostic as possible.

Requirements#

• MySQL database schema must be in sync with the latest v2 version (v2.19.0).
• Credentials to both databases:

MySQL: mysql://user:password@host:port/dbname

PostgreSQL: postgresql://user:password@host:port/dbname

Install Umami with a PostgreSQL database#

1. Install Umami (v2.19.0) on a PostgreSQL server. Documentation for the installation process can be found here.
2. Truncate the tables below. These tables will be populated from the MySQL import.

truncate table "_prisma_migrations";
truncate table "user";

Running migration#

MySQL dump file#

For smaller databases (less than 1 million records) you can use a MySQL dump file. Official docs can be found here.

1. Generate a MySQL dump file.

mysqldump --no-create-info --default-character-set=utf8mb4 --quick --skip-add-locks \
-u username -p mydb > mydbdump.sql

2. Replace backticks with double quotes to make it PostgreSQL compatible.

sed -i 's/`/"/g' mydbdump.sql

3. Run the dump file using psql.

psql -U username -d mydb < mydbdump.sql

Docker version:

docker exec -i my-container psql -U username -d mydb < mydbdump.sql

Now you can upgrade to Umami (v3.0).

Generating CSVs#

For larger databases, the dump file may be too large and the insert statement may become inefficient.

1. Generate MySQL CSVs for all the necessary tables with data.

Inside the MySQL shell:

SELECT *
INTO OUTFILE '/tmp/website_event.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM website_event;

2. Move the file from the MySQL server to a location accessible by psql using scp or cp.

3. Use psql \COPY command to import the CSVs into the PostgreSQL database. Repeat the command for each table.

psql -U postgres -d mydb -c "\COPY website_event FROM 'website_event.csv' CSV NULL '\N' ESCAPE '\'"