Cannot Import Dump From Sqlite To Postgres

[ccaspers]

• Gitea version (or commit ref): 3b612ce
• Git version: 2.18
• Operating system: Linux (using the official Gitea Docker image)
• Database (use [x]):
  • PostgreSQL
  • MySQL
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
  • Not relevant
• Log gist:

Description

I tried migrating a Gitea instance from Sqlite to Postgres. [1] and [2] lead me to believe that this is possible now. What I did:

1. gitea dump -c /path/to/app.ini -d postgres
2. Copy the generated gitea-db.sql to my Postgres container
3. psql -U gitea gitea -f gitea-db.sql inside my container

During the import, a lot of errors were logged to stdout. Here is the collected output: stderr.log

I tried with Postgres 10 and 11. The issue seems to be rooted in the generated sql as CREATE TABLE IF NOT EXISTS "user" ( ... "must_change_password" BOOL DEFAULT 0 NOT NULL ...); causes an error in Postgres. Postgres expects 't' or 'f' for boolean values or requires an explicit cast [3].

Related

[1] #5651
[2] #5680
[3] https://stackoverflow.com/questions/7947814/pgerror-error-column-is-required-is-of-type-boolean-but-expression-is-of-typ

[lunny]

SQLITE store BOOL as INTEGER, so when dump to postgres, it cannot be converted to BOOL because this is only a database to database convert.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs during the next 2 weeks. Thank you for your contributions.

[stale]

This issue has been automatically closed because of inactivity. You can re-open it if needed.