Migrate Gitea DB from MariaDB to PostgreSQL

I currently run Gitea in Docker, with a separate MariaDB database on a different server.

I’d like to migrate my database from MariaDB to PostgreSQL. I don’t really need/want to spin up a new Gitea instance, since the only difference will be the database backend.

Planning this out, am I able to do the following?

  1. Do a dump
    a. Use gitea dump (using Postgres as the output format)
    gitea dump -d postgres -c /path/to/app.ini
    or
    b. Use mysqldump (to dump the database directly)
  2. Import the resulting sql file into my Postgres server
  3. Update my app.ini to point to the new server?
1 Like

Using gitea dump with Postgres as the syntax should work to import.

1 Like

Thanks! All seems to be good so far. If anyone finds this, here is what I did to migrate from MariaDB to PostgreSQL.

Create a gitea dump (as shown here)
sudo docker exec -u git -it -w /tmp $(docker ps -qf "name=gitea") bash -c '/app/gitea/gitea dump -d postgres -c /data/gitea/conf/app.ini'

In the command above:

  • My user was git
  • My temp location (inside the container) was /tmp
  • My container name was gitea
  • I specified the database output type as postgres

Move the zip file

  1. Move the .zip file to your desired database server and directory
  2. Unzip it
  3. Delete everything except gitea-db.sql

Create user/database

  1. Login as the postgres user and enter the postgres prompt
    sudo -u postgres
    psql
  2. Create the user and database (I had already setup SCRAM, as described here)
    CREATE USER "gitea" WITH PASSWORD 'password';
    CREATE DATABASE dbgitea WITH OWNER gitea TEMPLATE template0 ENCODING UTF8 LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';
  3. Check if the user and database were created
    \du
    \list
  4. Go back to an OS prompt (as the postgres user)
    exit
  5. Restore the database
    psql --username gitea -h localhost --set ON_ERROR_STOP=on dbgitea < /path/to/your/gitea-db.sql
  6. Verify the “Owner” column of all the tables in your database (mine was gitea)
    psql
    \connect dbgitea
    \dt
    \q
    exit

Update Gitea settings (either in the .ini file or your environment settings in docker

  1. Update the database type (mine changed from mysql to postgres)
  2. Change the database host/port
  3. Change the database name/username/password

Start Gitea

  1. Restart the Docker container
  2. Make sure you can login/push/pull/create/delete/etc…

Cleanup
I had to re-generate new oAuth tokens for Drone, but after I did that, it worked as well

1 Like

I tried to migrate from mysql 5.7 to postgresql-10 db using the dump command generated sql-file. I can import the db file just fine but gitea won’t start and shows errors about indexes in logs.

2020/04/14 07:19:45 routers/init.go:51:initDBEngine() [I] Beginning ORM engine initialization.
2020/04/14 07:19:45 routers/init.go:53:initDBEngine() [I] ORM engine initialization attempt #1/10...
2020/04/14 07:19:45 ...-xorm/xorm/engine.go:330:Ping() [I] PING DATABASE postgres
2020/04/14 07:19:45 .../xorm/session_raw.go:76:queryRows() [I] [SQL] SELECT tablename FROM pg_tables WHERE schemaname = $1 AND tablename = $2 []interface {}{"public", "version"} - took: 2.052187ms
2020/04/14 07:19:45 .../dialect_postgres.go:942:IsColumnExist() [I] [SQL] SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = $1 AND table_name = $2 AND column_name = $3 [public version id]
2020/04/14 07:19:45 .../dialect_postgres.go:942:IsColumnExist() [I] [SQL] SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = $1 AND table_name = $2 AND column_name = $3 [public version version]
2020/04/14 07:19:45 .../xorm/session_raw.go:76:queryRows() [I] [SQL] SELECT "id", "version" FROM "version" WHERE "id"=$1 LIMIT 1 []interface {}{1} - took: 614.438µs
2020/04/14 07:19:45 .../dialect_postgres.go:1076:GetTables() [I] [SQL] SELECT tablename FROM pg_tables WHERE schemaname = $1 [public]
2020/04/14 07:19:45 .../dialect_postgres.go:974:GetColumns() [I] [SQL] SELECT column_name, column_default, is_nullable, data_type, character_maximum_length,
            CASE WHEN p.contype = 'p' THEN true ELSE false END AS primarykey,
            CASE WHEN p.contype = 'u' THEN true ELSE false END AS uniquekey
        FROM pg_attribute f
            JOIN pg_class c ON c.oid = f.attrelid JOIN pg_type t ON t.oid = f.atttypid
            LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
            LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
            LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
            LEFT JOIN pg_class AS g ON p.confrelid = g.oid
            LEFT JOIN INFORMATION_SCHEMA.COLUMNS s ON s.column_name=f.attname AND c.relname=s.table_name
        WHERE c.relkind = 'r'::char AND c.relname = $1 AND s.table_schema = $2 AND f.attnum > 0 ORDER BY f.attnum; [user public]
2020/04/14 07:19:45 .../dialect_postgres.go:1116:GetIndexes() [I] [SQL] SELECT indexname, indexdef FROM pg_indexes WHERE tablename=$1 AND schemaname=$2 [user public]
2020/04/14 07:19:45 routers/init.go:59:initDBEngine() [E] ORM engine initialization attempt #1/10 failed. Error: sync database struct error: Unknown col created_unix in index created_unix of table user, columns []
2020/04/14 07:19:45 routers/init.go:60:initDBEngine() [I] Backing off for 3 seconds

You did the export using the -d postgres flag?

Also, I’m assuming you updated the application’s ini file to point to the new database host/port/type?

yes on both questions.

Just add a small note:
If you met error ERROR: invalid byte sequence for encoding "UTF8" on import the SQL file (For example, it will failed if there is any Chinese character in any values), try add this line at the begin of the gitea-db.sql:

\encoding UTF8

This error may be related to what @zeripath worked on in xorm. @lunny merged this pull request two days ago and the title reads “Escape string and blob results from dump more correctly”, reason why I made the link. But I don’t actually know if there is a relationship :slight_smile:

Thanks, that’s great that it will be fixed in future version of Gitea.

And in my case I met another 2 problems:

  1. The column of owner_name in table repository contains null value, this caused the link for all repositories are incorrect, I have to fix it manually in database.
  2. I have to reset all sequences for the postgresql database (Refer to PostgreSql wiki) to make Gitea works on creating any new item.

I’m migrating from an old version of Gitea (version 1.11.4) with MySQL to docker image gitea/gitea:latest(version 1.15.9) with PostgreSQL.