Upgrade script from sqllite to postgres?

Drone Support
1

Hello,
I would like to migrate my setup from using the sqllite backend to postgres.

Is there an upgrade process/script I can use?

2

There are open source tools that specialize in migrate database across database vendors. one example is pgloader. See https://pgloader.readthedocs.io/en/latest/ref/sqlite.html

1 Like
3

I ended up using sequel but I got the following:

Error: Sequel::ForeignKeyConstraintViolation: PG::ForeignKeyViolation: ERROR:  insert or update on table "stages" violates foreign key constraint "stages_stage_build_id_fkey"
DETAIL:  Key (stage_build_id)=(1) is not present in table "builds".
/usr/lib/ruby/gems/2.6.0/gems/sequel-5.36.0/lib/sequel/adapters/postgres.rb:156:in `exec'

I imagine this having to do with me running some time ago:

curl -X DELETE "https://ci.drone.x/api/repos/company/repo/builds?before=1000" -H "Authorization: Bearer ${DRONE_TOKEN}"

and perhaps the api not deleting stages as well as builds?

5

@bradrydzewski would you have any advice on how to clean the data on sqlite to make sure the migration works?

6

I’ve run this SQL on a test/copy of drone and checking if this fixes the issue:

DELETE FROM stages WHERE stage_id IN (
  SELECT stage_id from stages LEFT JOIN builds on (
    stages.stage_build_id == builds.build_id AND
    stages.stage_repo_id == builds.build_repo_id) 
  WHERE build_id is NULL
)
7

For reference, I also had to do the same on the steps and on the logs tables.

Also I had to re-add secrets

Working now. thanks