Migrating from sqlite to postgres (drone 0.7)


I’m currently moving our in-house drone-based build server to a new machine and decided to do a bit of housekeeping. First thing is to move the default sqlite database into postgres, preferably a standalone one. I kind of don’t really trust the sqlite not to break someday and I like having full history.

The second phase will be the upgrade from 0.7 to 0.8.x version of drone.

However, I’m stuck converting the database. I tried the pgloader, which looked good, though it did not transfer the ‘config’ table. Do I need to do that manually? By the contents it looks like it is only necessary to rerun existing builds.

Other issue is that the database seems a bit malformed. The UI kind of works, but for example, trying to enable drone for a project (bitbucket), I receive this error (in http 500 response from drone):

meddler.Insert: DB error in QueryRow: pq: null value in column “repo_id” violates not-null constraint

I guess this is due to different handling of nulls in sqlite/postgres. Now I’m at a point where I really don’t want to be going around fixing stuff in the database, only to find some relict a few months later. Is there any way (semi) official way to migrate the database? Did anyone do it? I have seen one thread regarding mysql, with no followup. If not, I would rather remove some of our build history than throw a lot of time into this issue.

Are there any hints you could give me regarding this migration? For example, is it worth to convert to 0.8 first and then do some kind of conversion? Any insight is appreciated.