Gitea database migration

My Gitea instance was originally spun up using SQLite — as this was easiest at the time. Over the years I’ve tried several times to switch to PostgreSQL and been unsuccessful until now.

Most of the steps are taken from this issue on Github and the Database Preparation page of the Gitea docs. Before you proceed please read both and take onboard this nugget of wisdom.

“Switching DBs is always hard, no matter what application you are running.” — delvh

With that said make full backups and prepare to fail.

Instructions Link to heading

Mount a volume shared between the Gitea container and Postgresql container. For ease I mounted it at /opt/import in both containers.

 1version: '3.8'
 2services:
 3    git:
 4        image: gitea/gitea
 5        volumes:
 6            - "git_import:/opt/import"
 7        …
 8    db:
 9        image: postgres:16.4-alpine
10        environment:
11            POSTGRES_DB: gitea
12            POSTGRES_USER: gitea
13            POSTGRES_PASSWORD: itsasecret
14        volumes:
15            - "git_import:/opt/import"
16        …
17volumes:
18    git_import:
19        driver: local

Docker exec into the running Gitea container and execute gitea doctor check --all --fix and gitea doctor recreate-table to make sure the SQLite database is in a good starting condition. If you run into errors here do not proceed until you have fixed them.

Stop the Gitea container and copy the database file to /opt/import¹. I did this on the docker host

1$ cp /var/lib/docker/volumes/gitea_gitea_data/_data/gitea.db \
2>    /var/lib/docker/volumes/gitea_git_import/_data/gitea.db

This step avoids accidentally working on a database that is in use.

Restart the Gitea container and run the following in the container to dump the current database to a file that Postgresql should be able to import.

1$ cd /opt/import
2$ cp /etc/gitea/app.ini /opt/import/app.ini

Edit the path option of the database section in /opt/import/app.ini to point to /opt/import/gitea.db

1$ gitea --config /opt/import/app.ini dump \
2>    --database postgres --skip-repository --skip-custom-dir \
3>    --skip-attachment-data --skip-package-data --skip-index \
4>    --file gitea-dump.zip
5$ unzip gitea-dump.zip

Switch to the Postgres container and follow all the steps on Database Preparation for PostgreSQL

1. Edit /var/lib/postgresql/data/postgresql.conf make sure password_encryption = scram-sha-256 in the file and not commented out.

2. Restart Postgres container.

3. Make sure the database role (user) exists

   1CREATE ROLE gitea WITH LOGIN PASSWORD 'itsasecret';
   

4. Create the database

   1CREATE DATABASE gitea WITH OWNER gitea TEMPLATE template0 ENCODING UTF8 LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';
   

5. Make sure /var/lib/postgresql/data/pg_hba.conf has the following line host gitea gitea 192.0.2.10/32 scram-sha-256 which has the format CONNECTION_TYPE DATABASE_NAME USER ADDRESS METHOD all of which should be documented in the file itself.

6. Restart Postgres container

7. Import the exported database.

   1$ cd /opt/import
   2$ psql -U gitea
   

   1\i gitea-db.sql
   

8. Wait for the import to complete.

9. Restart Postgres container

Switch back to the Gitea container Edit /opt/import/app.ini with your new database settings.

1[database]
2DB_TYPE  = postgres
3HOST     = db:5432
4NAME     = gitea
5USER     = gitea
6PASSWD   = itsasecret 
7SSL_MODE = disable

1$ gitea doctor --config /opt/import/app.ini check --all --fix
2$ gitea doctor --config /opt/import/app.ini recreate-table

Edit /etc/gitea/app.ini with the same database configuration as above and add the following

1[log]
2LEVEL = Debug

Restart both containers and watch the Gitea startup logs while praying. Finally as is Gitea tradition, login to the Site Administration interface and ‘Resynchronize pre-receive, update and post-receive hooks of all repositories.’