Migrate from SQLite to POstgreSQL

General
1

Hi,

is there a plan how migrate data from SQLite to PostgreSQL?

Regards,
Rafal

2

“Official” documentation is not written on the topic.

“Unofficial” documentation can thou easily be found from many users of Gitea starting out with SQLite and migrating their DB to PostgreSQL. duckduckgo.com/?q=gitea+SQLite+to+POstgreSQL

Found this one, I will try out later. (I’ve had this same task on my own to-do for a while)

3

Here is what I did to make it work.
It worked for me, with Gitea version 1.9.5 and PostgreSQL 11, I can’t garantee it will work for you.

  1. I configured gitea to access postgresql, then I had it initialize the database with gitea migrate

  2. I extracted the insert commands from the database backup produced by gitea from SQLite: grep -i insert gitea-db.sql > gitea-insert.sql

  3. I inserted it into Postgresql: psql --set ON_ERROR_STOP=on < gitea-insert.sql

  4. In psql I generated a script to update the sequences:

    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;

then played the generated script
5. I can’t remember why I had to do this: gitea admin regenerate hooks (but it didn’t work without it)

Then it worked like a charm

4

Thx for help.

But I can’t migrate from SQLite to PG. Cause my SQLite Gitea instance has problems with mirroring. No one have any idea why.

I ready installed on new server the 1.12.2 with PG and have to migrate all the stuff (~95% are mirrors from other servers) “manually”.