forgejo sqlite to postgres

2025-11-16 #forgejo #sqlite #postgres #sysadmin

I long ago set up a forgejo instance, but didn't really put any care in to it, so used the default sqlite DB backend. I've finally got around to moving it to a new machine and running as a quadlet, so thought I should have a crack at moving it to Postgres, too.

While there is no supported method to do this, this fine chap had already had lit the path, so I just did that. I had less trouble than they did, perhaps because I was on an even more ancient version of Forgejo.

So, I set up a new instance providing it with nothing but the Postgres creds, and then halted the app, and got these errors when loading the DB:

2025-11-16T20:32:47.512014Z ERROR Database error 23505: duplicate key value violates unique constraint "forgejo_version_pkey"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY forgejo_version, line 1
2025-11-16T20:32:47.512014Z ERROR Database error 23505: duplicate key value violates unique constraint "version_pkey"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY version, line 1
2025-11-16T20:32:47.556015Z ERROR Database error 23505: duplicate key value violates unique constraint "oauth2_application_pkey"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY oauth2_application, line 1
2025-11-16T20:32:47.944024Z ERROR Database error 23505: duplicate key value violates unique constraint "app_state_pkey"
DETAIL: Key (id)=(runtime-state) already exists.
CONTEXT: COPY app_state, line 1
2025-11-16T20:32:48.028026Z ERROR Database error 23505: duplicate key value violates unique constraint "system_setting_pkey"
DETAIL: Key (id)=(1) already exists.

They all sounded like they were just low level application state, so I just ignored it and hoped for the best, and all seems well now.

I guess the lesson is that my rootless podman Ansible gunk makes stuff so easy that I should just do things right the first time - I would have taken only five more minutes a year ago to use Postgres to start with. Or maybe the lesson is that it's good to have home infra no one else uses so you can just be lazy with setting it up and then sloppy when moving data.