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?
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)
Import the resulting sql file into my Postgres server
Move the .zip file to your desired database server and directory
Unzip it
Delete everything except gitea-db.sql
Create user/database
Login as the postgres user and enter the postgres prompt sudo -u postgres psql
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';
Check if the user and database were created \du \list
Go back to an OS prompt (as the postgres user) exit
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
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:
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
Thanks, that’s great that it will be fixed in future version of Gitea.
And in my case I met another 2 problems:
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.
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.
Just some minor changes for me personally. I run everything through docker compose. And I’ve setup the postgres container with a default gitea username, a default gitea database and a password through environment variables. This allows me to already skip your create user and create database commands.
The dump command provided by gitea is unnecessarily complex in my opinion. I needed:
Then go into the postgres container and execute the import command.
docker compose exec -it -u postgres db /bin/bash
cd ~
psql -U gitea -h localhost --set ON_ERROR_STOP=on gitea < ./gitea-db.sql
I got a lot of INSERT 0 1 , CREATE TABLE, CREATE INDEX, etc. as expected, but no errors. After updating the gitea database related environment variables I recreated/started the container and it was all good!
