Migrate from SQLite to POstgreSQL


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


“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)

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:

     	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

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”.