[DB conversion] MySQL to PostrgreSQL

Support
1

I wanted to migrate from mariadb to postgres, but I found no tutorials. So after a bunch of trial and error I finally figured it out and decided to write a tutorial so you don’t have to.
I’m running docker-compose so the example will be based on that, but you can use this with any method.

First of all create a backup of your database to avoid losing any precious data!!!

Shutdown the docker stack.

docker-compose down

Create postgres DB

In your docker compose create a second database (postgres) and make sure to change the database type and host to the new database.
Make sure to map a port to each database so you can access them from outside the docker network.

Start both databases

docker-compose up -d db db2

Migrate with pgloader

pgloader -v mysql://<user>:<password>@<ip>:<mariadb_port>/vikunja pgsql://<user>:<password>@<ip>:<psql_port>/vikunja

Rename vikunja schema to public
You can do this with pgloader, psql, or pg admin. I chose the latter.

BEGIN TRANSACTION;
  ALTER SCHEMA public RENAME TO public_old;
  ALTER SCHEMA vikunja RENAME TO public;
  DROP SCHEMA public_old CASCADE;
COMMIT;

Start vikunja

docker-compose up -d vikunja
2 Likes
2

pgloader is available as a docker container, in your favourite package manager, or on github.