Possible bug when migrating from sqlite to postgres [with workaround]

[sphrak]

>> BE ADVISED<<

If you find this issue in the future do not try to reproduce this ended up rendering
the gitea my instance read only and I had to revert back to sqlite.

Hello everyone,
I tried to migrate from sqlite to postgresql manually today.
I did run into some problems doing it the conventional way so
I figured I might as well document the experience and potentially
narrow down the problem so it can be fixed.

Short description

The problem seems to be the generated sql file (gitea dump -d postgres [..])
contains both INSERT and CREATE statements -- which does not fly well with
psql because it errors out if its not in order.

This puts the database in an incomplete state and you cannot run the app afterwards.

I did get it to work with the help of @zeripath by first running the postgres migrations, and then "blindly" importing the psql file when the relations already exist.

What I tried initially

1. gitea dump -d postgres -c /path/to/app.ini
2. Reconfigure app.ini for postgresql
3. Load the sql file via psql -d gitea -f backup.sql

But that generates errors like:

ERROR:  relation "repository" does not exist
LINE 1: INSERT INTO "repository" ("id", "owner_id", "lower_name", "n...
                    ^
ERROR:  relation "repository" does not exist
LINE 1: INSERT INTO "repository" ("id", "owner_id", "lower_name", "n...

Now, I know the dump command has been reimplemented in @lunny's PR over here so after that has been merged this
might no longer be a problem.

But long story short its impossible to restore the sql file

What I ended up having to do:

I dumped the sqlite3 schema+data via

gitea dump -d postgres -c /path/to/app.ini

1. Configured the app.ini to have a postgres instance accessible.
2. Run the app once, to initialise the migration process
3. Pray there is no DELETE statements in sql file, since it does miss a few CREATE IF NOT EXIST statements.
4. Run the app again
5. Profit

---

• Gitea version (or commit ref): 1.7.0-rc2
• Git version:
• Operating system:
• Database (use [x]):
  • PostgreSQL
  • MySQL
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
  • Not relevant

[techknowlogick]

Closed with #5680

[adrianschneider94]

For anybody getting here with google:
I succesfully migrated from sqlite to postgres with the following steps:

• Create a new Gitea instance with postgres
• copy the repositories
• Import the data from the sqlite database with pgloader. It has an option 'data only' which doesn't meddle with the schema but just transfers the data.
• Edit the app.ini of the new instance according to your needs.

Also applies to #6090

[robindegen]

I did the conversion to postgres 11 but there was an error with table creation saying that the value was a bool but the default value was of type integer. I replaced all instances of BOOL DEFAULT 0 with BOOL DEFAULT false and BOOL DEFAULT 1 with BOOL DEFAULT true respectively. This worked.

On top of that I had to apply the workaround provided by belminf in #4407

[dev-dsp]

For anybody getting here with google:
I succesfully migrated from sqlite to postgres with the following steps:

• Create a new Gitea instance with postgres
• copy the repositories
• Import the data from the sqlite database with pgloader. It has an option 'data only' which doesn't meddle with the schema but just transfers the data.
• Edit the app.ini of the new instance according to your needs.

Also applies to #6090

This one actually does work.

I used following pgloader configuration for migrating data from sqlite:

load database
     from sqlite:///srv/gitea-bak/gitea.db
     into postgresql:///gitea_production
 with data only
  set work_mem to '16MB', maintenance_work_mem to '512 MB';