Dump database and import to other supported databases

[crocket]

Since SQLite 3 looks ok, I want to remove PostgreSQL which is another piece of software that adds complexity to my system administration.

http://gogs.io/docs/intro/ doesn't tell me how to migrate gogs database.

Can anyone document how to do it on the website?

[unknwon]

Hmm... I think it's kind of database migration thing than a Gogs feature.

Maybe dump SQL from PostgreSQL and import to SQLite3?

Did some googling: http://manuel.manuelles.nl/blog/2012/01/18/convert-postgresql-to-sqlite/

[crocket]

I read the article, and it doesn't apply well to gogs.
It seems like a non-intuitive administration task.

[unknwon]

The article link I posted just an example that how migrate between databases.

Right now Gogs does not have somewhat in the middle format to support this kind of operation.

However, we can change purpose of issue to be: dump database and import to other supported databases.

[crocket]

I spent some time on fiddling with PostgreSQL and SQLite 3 and migrated the database.
But, I am not sure whether the migration caused any glitches.
Perhaps, it should become a feature request.

[unknwon]

I've changed title, please check and see if you're OK with it.

[crocket]

I'm ok with it.

[slaskis]

Could x-orm tools be used perhaps?

[lunny]

This is an interesting question. The dump will automatically generate create table, create index and insert into SQLs. For all kind of databases, the differences between them are create table and create index.

[jdroot]

Would there be a downside to gogs dump dumping serialized structs instead of SQL? An import/restore feature could then be created that was database agnostic.

[lunny]

I will try to implement this on xorm via engine.DumpAll(w, dbtype). @unknwon

[unknwon]

Would there be a downside to gogs dump dumping serialized structs instead of SQL? An import/restore feature could then be created that was database agnostic.

@jdroot SQL isn't 100% compatible across all types of databases, but Gogs understands its convetion.

@lunny Is it possible to dump SQLite3 and import to MySQL?

[lunny]

@unknwon I think Yes. The new DumpAll method could dump SQL to any xorm supported databases.

[unknwon]

@lunny that sounds cool!

[lunny]

It's difficult to convert sqlite's datatypes to mysql's datatypes. There are some work to do.

[unknwon]

It's difficult to convert sqlite's datatypes to mysql's datatypes. There are some work to do.

That's why I suggest this issue to dump a in-middle format specifically for Gogs purpose.

[lunny]

Another idea, we can generate table creation SQL according Go's struct not from database. And generate Insert SQL from database. I will try this way later.

[unknwon]

Another idea, we can generate table creation SQL according Go's struct not from database.

I think it is a good idea.

[lunny]

I just pushed a commit to xorm go-xorm/xorm@4bcbb95, now I think Gogs could implement this feature via DumpTables, the usage is below:

var tbs = []interface{}{
        new(AccessToken),
        //new(DeployKey),
        new(Collaboration),
        new(Notice),
        new(Access),
        new(Label),
        new(EmailAddress),
        new(TeamRepo),
        new(TeamUser),
        new(OrgUser),
        new(HookTask),
        new(UpdateTask),
        new(Webhook),
    }

    var tables []*core.Table
    for _, tb := range tbs {
        tables = append(tables, engine.TableInfo(tb))
    }

    err = engine.DumpTables(tables, os.Stdout, core.MYSQL)
    if err != nil {
        panic(err)
    }

@unknwon

[unknwon]

Thanks, how to import back?

[lunny]

engine.Import or engine.ImportFile.

[unknwon]

Got it, I'll try that.

[jdroot]

@unknwon What I meant by my comment earlier was something like the following. Basically, don't deal with SQL at all. (Code might be bad, I am very new to Go)

func DumpAllAsGob(filePath string) error {
    f, err := os.Create(filePath)
    if (err != nil) {
        return err
    }
    defer f.Close()

    enc := gob.NewEncoder(f)

    for _, table := range tables {
        slice := reflect.MakeSlice(reflect.SliceOf(reflect.TypeOf(table)), 0, 0)
        slicePointer := reflect.New(slice.Type())
        slicePointer.Elem().Set(slice)
        sliceInterface := slicePointer.Interface()
        err = x.Find(sliceInterface)
        if (err == nil) {
                enc.Encode(slicePointer.Elem().Interface())
        } else {
            return err
        }
    }
    return nil
}

[unknwon]

@jdroot Yes, that is my original thought. But if XORM already works with export and import, I don't think gob thing is worth investigating.

[FyinInc]

+1

[bkcsoft]

This thingie here kinda does that (if you edit the config-file or pass a new one as a flag) develop...bkcsoft:feature/restore-dump coughing

[aventrax]

hello. I'd want to convert ftom mysql to postgresql. Is there a way to do so? In case there is not, and because my gogs is pretty empty (just addes 20 commits to 2 repos), what would I lost to start from scratch? Would be the commit's comments visible to a new gogs deploy with the same git repos?

[unknwon]

Hi @aventrax , forgot to update this issue. This feature has been supported quite a while, you can read more about how to do that: https://discuss.gogs.io/t/how-to-backup-restore-and-migrate/991

[aventrax]

@unknwon many thanks, i migrated successfully!