Migrate Gitea DB from MariaDB to PostgreSQL

Install/Maintain/Configure
3

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