New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
sphrak opened this Issue Jan 6, 2019 · 1 comment

Comments

2 participants
@sphrak
Copy link
Contributor

sphrak commented Jan 6, 2019

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

This comment has been minimized.

Copy link
Member

techknowlogick commented Jan 10, 2019

Closed with #5680

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment