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

New Issue

GiteaMirror · 2025-11-02T04:45:21-06:00

GiteaMirror commented

2025-11-02 04:45:21 -06:00

Originally created by @sphrak on GitHub (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

gitea dump -d postgres -c /path/to/app.ini
Reconfigure app.ini for postgresql
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

~~Configured the app.ini to have a postgres instance accessible.~~
~~Run the app once, to initialise the migration process~~
~~Pray there is no DELETE statements in sql file, since it does miss a few CREATE IF NOT EXIST statements.~~
~~Run the app again~~
~~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

Originally created by @sphrak on GitHub (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: ```sql 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](https://github.com/go-gitea/gitea/pull/2917) 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~~ ```sh 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]`): - [x] PostgreSQL - [ ] MySQL - [ ] MSSQL - [x] SQLite - Can you reproduce the bug at https://try.gitea.io: - [ ] Yes (provide example URL) - [ ] No - [x] Not relevant

GiteaMirror closed this issue

2025-11-02 04:45:21 -06:00

GiteaMirror commented

2025-11-02 04:45:21 -06:00

@techknowlogick commented on GitHub (Jan 10, 2019):

Closed with #5680

@techknowlogick commented on GitHub (Jan 10, 2019): Closed with #5680

GiteaMirror commented

2025-11-02 04:45:21 -06:00

@adrianschneider94 commented on GitHub (Mar 25, 2020):

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

@adrianschneider94 commented on GitHub (Mar 25, 2020): 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

GiteaMirror commented

2025-11-02 04:45:21 -06:00

@robindegen commented on GitHub (Jun 19, 2020):

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 https://github.com/go-gitea/gitea/issues/4407

@robindegen commented on GitHub (Jun 19, 2020): 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 https://github.com/go-gitea/gitea/issues/4407

GiteaMirror commented

2025-11-02 04:45:22 -06:00

@dev-dsp commented on GitHub (Jul 21, 2020):

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

@dev-dsp commented on GitHub (Jul 21, 2020): > 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'; ```

GiteaMirror referenced this issue

2025-11-02 12:13:18 -06:00

[PR #2714] [MERGED] Fix PR, milestone and label functionality if issue unit is disabled (#2710) #16553

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/gitea#2714