Restoring a db dump from sqlite to postgres is incomplete

[mjwwit]

• Gitea version (or commit ref): 521d429
• Git version: n.a.
• Operating system: Docker image
• Database (use [x]):
  • PostgreSQL
  • MySQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
  • 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.