Migrating Gitea db from MySQL to Postgresql

Install/Maintain/Configure
1

I have need to change to database used by Gitea from current MySQL to PostgreSQL.
I assumed this would be simple swapping of database configs and database migration but there seems to be some extra steps needed as after migration I can’t login to gitea anymore.

Steps taken:
I shutdown gitea server, used pgloader to migrate mysql to postgresql database, updated gitea configuration to point to pg database and started gitea.

I get gitea frontpage but when I try to login with my AD account I get “Username or password is incorrect.”. If I switch database to point to old mysql instance the login works again.

So far I’ve re-added the AD settings with “gitea admin add” command but seems the database migration with default options won’t work. Adding “preserve index names” seems to be required option and logs seem to point that bigint -> bigserial change should be done as well.

Has anyone completed such a migration and has notes to share?

2

Hi, I’ve never attempted this, but at the very least you need to create and update all PostgreSQL sequences. My suggestion is this:

  • Install a new instance of Gitea using PostgreSQL (use the same Gitea version as your MySQL’s).
  • Delete all records without dropping any tables or other objects.
  • Magically migrate all records from MySQL into PostgreSQL (I don’t know how you did this, but you already did, so you’ve got that figured out).
  • Run the following SQL in the PostgreSQL database:
SELECT 'SELECT SETVAL(' ||
		quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
		', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
		quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
	 FROM pg_class AS S,
	      pg_depend AS D,
	      pg_class AS T,
	      pg_attribute AS C,
	      pg_tables AS PGT
	 WHERE S.relkind = 'S'
	     AND S.oid = D.objid
	     AND D.refobjid = T.oid
	     AND D.refobjid = C.attrelid
	     AND D.refobjsubid = C.attnum
	     AND T.relname = PGT.tablename
	 ORDER BY S.relname;
  • Copy the query results and run them, in order to fix the sequence numbers.

I don’t know if any other steps are required, or what was your problem with the user passwords, but I’d start from this at least. Objects like indexes and sequences do not receive the same names across database types, so there’s that.

One more thing: in PostgreSQL, the keyword user is reserved, so the name of the table must be enclosed between back ticks. Make sure all your user records were properly migrated by doing:

SELECT * from `user`;

If your migration tool gives you trouble with this table, try renaming it momentarily.