sqlite3 migration to MySQL or Postgress

[preussal]

Do you have instructions on how to migrate the SQLight Database to MySQL or Postgress?

I have now the problem with ~ 60 mirrors that I keep getting
“sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked”

only a restart of all 3 service help to resolve this for a short time

[Salamek]

You installed this project from source?

[preussal]

with apt.
and then i do the installation procedure
no docker contrainer

[Salamek]

ah i see, this project uses old setup script... crap...

Well i have written guide how to migrate here https://github.com/Salamek/gitlab-tools/blob/master/doc/PostgreSQL.md
untested...

[preussal]

Thank you,
Installation - Check
Create DB - Check

Migration need also install sqlite3
apt-get install sqlite3

Migration Dump export

root@gitlab:/home/gitlab-tools # sqlite3 gitlab-tools.db .schema > schema.sql
root@gitlab:/home/gitlab-tools # sqlite3 gitlab-tools.db .dump > dump.sql
root@gitlab:/home/gitlab-tools # grep -vx -f schema.sql dump.sql > data.sql

Check

But the import Fails

root@gitlab.:/home/gitlab-tools # su postgres
postgres@gitlab:/home/gitlab-tools$ psql gitlab_tools < /home/gitlab-tools/data.sql
ERROR:  syntax error at or near "PRAGMA"
LINE 1: PRAGMA foreign_keys=OFF;
        ^
BEGIN
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
ERROR:  syntax error at or near "user"
LINE 1: INSERT INTO user VALUES('2024-02-05 12:42:41','2024-02-05 12...
                    ^
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block

I think i need to Cleanup the Dump
This Lines to a lot of truble
INSERT INTO celery_taskmeta VALUES(4207,'e777290c-d716-4492-93ae-f99bf8d0a3a0','FAILURE',X'80059581.........

[preussal]

I found the mistake, both in the SQLITE3 DB and in the Postresql.
My name contains a "ß" (German Special ä ö ü ß) and in the db dump from gitlab-tools it is something like the "a/o = n" and that somehow breaks.

ERROR:  syntax error at or near "user"
LINE 1: INSERT INTO user VALUES('2024-02-05 12:42:41','2024-02-05 12...

I changed my name and now the SQLITE3 DB is going again :)

[Salamek]

Yes dump will most likely need some manual cleanup (some commands may not be supported etc)