Migrating Gitea's Database from MariaDB to PostgreSQL

The gitea dump command can supposedly create an SQL dump for a different target engine than it’s running (ex: from a MariaDB Gitea instance to a PostgreSQL dump). However, this is broken: “Postgres expects ’t’ or ‘f’ for boolean values or requires an explicit cast”. The issue is closed as “stale”, but that’s the error I got.

The comments on another issue mention some success using pgloader instead.

The idea is:

1. Install a new Gitea instance backed by PostgreSQL so it creates the necessary tables
2. Use pgloader to copy only the data (no tables, etc.) from MariaDB to PostgreSQL

Simple, right? Well, kind of but not really.

My “load” file looked like this:

load database from
    mysql://gitea:$PASSWORD@mariadb.lxc.chromic.org:3306/gitea into
    postgresql://gitea@postgres.lxc.chromic.org:5432/gitea
 with data only

For me, pgloader failed with “pgloader 10 fell through ECASE expression”. A workaround someone mentions in the thread is to edit PostgreSQL’s pg_hba.conf file so that the PostgreSQL user pgloader is temporarily using “trusted” login method and thus doesn’t need a password to connect (don’t do this if your database is, for whatever reason, accessible from the public internet…):

# Original:
# host    gitea           gitea           10.0.3.234/32           md5
# Temporary:
host    gitea           gitea           10.0.3.234/32           trusted 

This seemed to fix the issue, however I ran into another error: “failed to find schema ‘gitea’ in target catalog”

I found a workaround/solution to this in a random pgloader Github issue. Append the following ALTER SCHEMA ‘gitea’ RENAME TO ‘public’;

load database from
    mysql://gitea:$PASSWORD@mariadb.lxc.chromic.org:3306/gitea into
    postgresql://gitea@postgres.lxc.chromic.org:5432/gitea
 with data only
 ALTER SCHEMA 'gitea' RENAME TO 'public';

pgloader then finally started creating tables and inserting things.

Now because my Gitea instance is a few years old and went through a several upgrades, there was a column present in one of my tables (don’t remember which) that didn’t exist in the new Gitea install (something about “old_id” or some such).

What I ended up doing was:

1. Dump a copy of the current MariaDB Gitea database: mysqldump gitea > gitea_tmp.sql
2. Create a temporary database: CREATE DATABASE gitea_tmp CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
3. Grant permissions to the current gitea user: GRANT ALL PRIVILEGES ON gitea_tmp.* TO 'gitea';
4. Load data in the temporary MariaDB databse: mysql -u gitea -p gitea_tmp < gitea_tmp.sql
5. Drop the offending column from the temporary database: ALTER TABLE table_name DROP COLUMN column_name;

Try pgloader with the temporary database:

load database from
    mysql://gitea:$PASSWORD@mariadb.lxc.chromic.org:3306/gitea_tmp into
    postgresql://gitea@postgres.lxc.chromic.org:5432/gitea
 with data only
 ALTER SCHEMA 'gitea_tmp' RENAME TO 'public';

Another thing that was wrong with my running database is the fact that I somehow had NULL values in a “NOT NULL” column (“user.keep_activity_private”) so filled those in:

UPDATE user SET keep_activity_private=1;

And… Success!