Technos & Psyche

Migrate Gitea from SQLite to Postgres

July 12, 2020

Since SQLite uses different types, we need to change those. We also need to fix the id-sequences after the import.

Create Gitea Backup

gitea dump -d postgres -c /path/to/app.ini

This will dump the database in PostgreSQL Format as well as repositories, logs and config.

Fix SQL Types

Open the `gitea_sql.db` file in an editor and all instances of `BOOL DEFAULT 0` with `BOOL DEFAULT false` and `BOOL DEFAULT 1` with `BOOL DEFAULT true` respectively.

Import into PostgreSQL

psql -d gitea -f backup.sql

Change Gitea dbconfig to point to PostgreSQL

Check config cheatsheet: https://docs.gitea.io/en-us/config-cheat-sheet/#database-database

Fix PostgreSQL Sequences

This was reported to work in 1.7.3 but also works in 1.12.1.

 SELECT SETVAL('public.access_id_seq', COALESCE(MAX(id), 1) ) FROM public.access;
 SELECT SETVAL('public.access_token_id_seq', COALESCE(MAX(id), 1) ) FROM public.access_token;
 SELECT SETVAL('public.action_id_seq', COALESCE(MAX(id), 1) ) FROM public.action;
 SELECT SETVAL('public.attachment_id_seq', COALESCE(MAX(id), 1) ) FROM public.attachment;
 SELECT SETVAL('public.collaboration_id_seq', COALESCE(MAX(id), 1) ) FROM public.collaboration;
 SELECT SETVAL('public.comment_id_seq', COALESCE(MAX(id), 1) ) FROM public.comment;
 SELECT SETVAL('public.commit_status_id_seq', COALESCE(MAX(id), 1) ) FROM public.commit_status;
 SELECT SETVAL('public.deleted_branch_id_seq', COALESCE(MAX(id), 1) ) FROM public.deleted_branch;
 SELECT SETVAL('public.deploy_key_id_seq', COALESCE(MAX(id), 1) ) FROM public.deploy_key;
 SELECT SETVAL('public.email_address_id_seq', COALESCE(MAX(id), 1) ) FROM public.email_address;
 SELECT SETVAL('public.follow_id_seq', COALESCE(MAX(id), 1) ) FROM public.follow;
 SELECT SETVAL('public.gpg_key_id_seq', COALESCE(MAX(id), 1) ) FROM public.gpg_key;
 SELECT SETVAL('public.hook_task_id_seq', COALESCE(MAX(id), 1) ) FROM public.hook_task;
 SELECT SETVAL('public.issue_assignees_id_seq', COALESCE(MAX(id), 1) ) FROM public.issue_assignees;
 SELECT SETVAL('public.issue_dependency_id_seq', COALESCE(MAX(id), 1) ) FROM public.issue_dependency;
 SELECT SETVAL('public.issue_id_seq', COALESCE(MAX(id), 1) ) FROM public.issue;
 SELECT SETVAL('public.issue_label_id_seq', COALESCE(MAX(id), 1) ) FROM public.issue_label;
 SELECT SETVAL('public.issue_user_id_seq', COALESCE(MAX(id), 1) ) FROM public.issue_user;
 SELECT SETVAL('public.issue_watch_id_seq', COALESCE(MAX(id), 1) ) FROM public.issue_watch;
 SELECT SETVAL('public.label_id_seq', COALESCE(MAX(id), 1) ) FROM public.label;
 SELECT SETVAL('public.lfs_lock_id_seq', COALESCE(MAX(id), 1) ) FROM public.lfs_lock;
 SELECT SETVAL('public.lfs_meta_object_id_seq', COALESCE(MAX(id), 1) ) FROM public.lfs_meta_object;
 SELECT SETVAL('public.login_source_id_seq', COALESCE(MAX(id), 1) ) FROM public.login_source;
 SELECT SETVAL('public.milestone_id_seq', COALESCE(MAX(id), 1) ) FROM public.milestone;
 SELECT SETVAL('public.mirror_id_seq', COALESCE(MAX(id), 1) ) FROM public.mirror;
 SELECT SETVAL('public.notice_id_seq', COALESCE(MAX(id), 1) ) FROM public.notice;
 SELECT SETVAL('public.notification_id_seq', COALESCE(MAX(id), 1) ) FROM public.notification;
 SELECT SETVAL('public.org_user_id_seq', COALESCE(MAX(id), 1) ) FROM public.org_user;
 SELECT SETVAL('public.protected_branch_id_seq', COALESCE(MAX(id), 1) ) FROM public.protected_branch;
 SELECT SETVAL('public.public_key_id_seq', COALESCE(MAX(id), 1) ) FROM public.public_key;
 SELECT SETVAL('public.pull_request_id_seq', COALESCE(MAX(id), 1) ) FROM public.pull_request;
 SELECT SETVAL('public.reaction_id_seq', COALESCE(MAX(id), 1) ) FROM public.reaction;
 SELECT SETVAL('public.release_id_seq', COALESCE(MAX(id), 1) ) FROM public.release;
 SELECT SETVAL('public.repo_indexer_status_id_seq', COALESCE(MAX(id), 1) ) FROM public.repo_indexer_status;
 SELECT SETVAL('public.repo_redirect_id_seq', COALESCE(MAX(id), 1) ) FROM public.repo_redirect;
 SELECT SETVAL('public.repo_unit_id_seq', COALESCE(MAX(id), 1) ) FROM public.repo_unit;
 SELECT SETVAL('public.repository_id_seq', COALESCE(MAX(id), 1) ) FROM public.repository;
 SELECT SETVAL('public.review_id_seq', COALESCE(MAX(id), 1) ) FROM public.review;
 SELECT SETVAL('public.star_id_seq', COALESCE(MAX(id), 1) ) FROM public.star;
 SELECT SETVAL('public.stopwatch_id_seq', COALESCE(MAX(id), 1) ) FROM public.stopwatch;
 SELECT SETVAL('public.team_id_seq', COALESCE(MAX(id), 1) ) FROM public.team;
 SELECT SETVAL('public.team_repo_id_seq', COALESCE(MAX(id), 1) ) FROM public.team_repo;
 SELECT SETVAL('public.team_unit_id_seq', COALESCE(MAX(id), 1) ) FROM public.team_unit;
 SELECT SETVAL('public.team_user_id_seq', COALESCE(MAX(id), 1) ) FROM public.team_user;
 SELECT SETVAL('public.topic_id_seq', COALESCE(MAX(id), 1) ) FROM public.topic;
 SELECT SETVAL('public.tracked_time_id_seq', COALESCE(MAX(id), 1) ) FROM public.tracked_time;
 SELECT SETVAL('public.two_factor_id_seq', COALESCE(MAX(id), 1) ) FROM public.two_factor;
 SELECT SETVAL('public.u2f_registration_id_seq', COALESCE(MAX(id), 1) ) FROM public.u2f_registration;
 SELECT SETVAL('public.upload_id_seq', COALESCE(MAX(id), 1) ) FROM public.upload;
 SELECT SETVAL('public.user_id_seq', COALESCE(MAX(id), 1) ) FROM public."user";
 SELECT SETVAL('public.user_open_id_id_seq', COALESCE(MAX(id), 1) ) FROM public.user_open_id;
 SELECT SETVAL('public.version_id_seq', COALESCE(MAX(id), 1) ) FROM public.version;
 SELECT SETVAL('public.watch_id_seq', COALESCE(MAX(id), 1) ) FROM public.watch;
 SELECT SETVAL('public.webhook_id_seq', COALESCE(MAX(id), 1) ) FROM public.webhook;

Sources

Tags: gitea sqlite postgres