Gitea: Restoring a db dump from sqlite to postgres is incomplete

[mjwwit]

• Gitea version (or commit ref): 521d429b58c78864c1809b67005ce0bc80d2e484
• Git version: n.a.
• Operating system: Docker image
• Database (use [x]):
  
  
  
  • [x] PostgreSQL
  
  
  • [ ] MySQL
  
  
  • [x] SQLite
  
  
• Can you reproduce the bug at https://try.gitea.io:
  
  
  
  • [ ] Yes (provide example URL)
  
  
  • [ ] No
  
  
  • [x] Not relevant
  
  
• Log gist: https://gist.github.com/mjwwit/08966dde2b08b4ecd941112c3c754d8a

Description

The error in the gist linked above takes place when trying to create a repository after migrating from a sqlite3 database to a postgres database using gitea dump.
The issue is that the sequences aren't set to correct values in the dump. This means that they all start at 0. Any insertions get the next sequence number, 1, which is most likely already taken, causing the conflict.

[mjwwit]

I'm not sure if this will help, but a sequence in Postgres can be set to the last value present in the table by executing:

SELECT setval('table_id_seq', COALESCE((SELECT MAX(id) FROM table), 1), false);

[lunny]

I will send a PR to fix this tomorrow.

[lunny]

@mjwwit Please help to confirm #886 resolved this issue.

[mjwwit]

This is still an issue, please re-open.

[mjwwit]

I've come to realize that my previous fix suggestion doesn't work. In order to fix this issue the following (fixed) statement needs to be run for all sequences:

SELECT setval('table_id_seq', COALESCE((SELECT MAX(id) FROM "table"), 0) + 1, false);

Where table needs to be replaced with the table name.

This will set the sequence value to MAX(id) + 1 for tables with rows and 1 for tables without any rows.

[lunny]

The dump command will be deprecated and see #1637

[bkcsoft]

Migrations between database providers is not supported and never has been (until #1637 is done). Closing

[nodiscc]

@mjwwit 's solution seems to have worked for me so far (migration from MySQL to PostgreSQL), gitea 12.4

# get a list of tables
$ sudo -u gitea psql -c '\dt' | cut -d " " -f 4

$ echo $tables
access access_token action attachment collaboration comment commit_status deleted_branch deploy_key email_address email_hash external_login_user follow gpg_key gpg_key_import hook_task issue issue_assignees issue_dependency issue_label issue_user issue_watch label language_stat lfs_lock lfs_meta_object login_source milestone mirror notice notification oauth2_application oauth2_authorization_code oauth2_grant oauth2_session org_user protected_branch public_key pull_request reaction release repo_indexer_status repo_redirect repository repo_topic repo_unit review star stopwatch task team team_repo team_unit team_user topic tracked_time two_factor u2f_registration upload user user_open_id version watch webhook

# update all sequences
$ for table in $tables; do sudo -u gitea psql --echo-all -c "SELECT setval('${table}_id_seq', COALESCE((SELECT MAX(id) FROM \"$table\"), 0) + 1, false);"; done