Migrate Umami from PostgreSQL to MySQL

I recently installed MySQL for another purpose so I've decided to use it as well for Umami. Here are the few steps I did.

Let's assume that:

• Umami is currently running with PostgreSQL database (docker.umami.dev/umami-software/umami:postgresql-latest)
• PostgreSQL container is named "umami-postgres" and user, password and database name are "umami"
• MySQL container is already running, is named "mysql-container" and with root password being "c35f6910-8979-4647-8ba9-928a044be28a"

Create a database for umami

You need to create a database for Umami in your existing MySQL container with the following commands:

docker exec -it mysql-container bash
mysql -uroot -pc35f6910-8979-4647-8ba9-928a044be28a
CREATE DATABASE umami;
SHOW DATABASES;

Create the database schema

You need to create a new Umami secondary container using MySQL (docker.umami.dev/umami-software/umami:mysql-latest) and running on a different port.
You need it to create the database schema in your MySQL database. Keep the primary one running in parallel.
You don't need to do anything except changing the admin account password. The rest of the data will be imported later.

Export the data

To export the data from your PostgreSQL database, you need to execute the following command. It will generate an export.sql file.

docker exec -t umami-postgres pg_dump -U umami --column-inserts --data-only --table=website --table=pageview --table=session umami > export.sql

Customize the export file

Few modifications need to be done on the export file to be MySQL compatible. You need to remove Milliseconds and Timezone from the timestamps and remove the public. prefix on insert statements which is not existing on MySQL. As you can have a huge amount of lines, the commands below will do it for you!

sed -E 's/(:[0-9]+).[0-9]+/\1/g' export.sql > export2.sql && mv export2.sql export.sql
sed 's/+00//g' export.sql > export2.sql && mv export2.sql export.sql
sed 's/INTO public./INTO /g' export.sql > export2.sql && mv export2.sql export.sql
sed '/^INSERT INTO/!d' export.sql > export2.sql && mv export2.sql export.sql

Import the data to MySQL

To import the data in your MySQL container, you need to run the command below, it can take few minutes depending on the volume of data.

docker exec -i mysql-container mysql -uroot -pc35f6910-8979-4647-8ba9-928a044be28a umami < export.sql

Finishing

You can now open your secondary Umami container and see all the data being imported. When the previous command is over and all data visible in the secondary Umami container, you just need to modify your docker compose file to shutdown the secondary Umami container and plug your primary one to the MySQL database.