concourse: Downgrade to 7.4.0 database (1625844436) fails

@AndoniLarz Thanks so much for the information, that’s actually very useful because that pending build was not fixed by my script. I’ll update the script and the migration with this, thanks!!

Finally got around to running this and it seems to have worked. Thanks again!

@clarafu thank you so much! Script worked as expected, successfully upgraded to 7.4.3 from 7.4.2.

I’ve created a script that contains

#!/bin/bash

set -ex

PGPASSWORD=$CONCOURSE_POSTGRES_PASSWORD psql -v ON_ERROR_STOP=1 --username=$CONCOURSE_POSTGRES_USER --host=$CONCOURSE_POSTGRES_HOST --dbname=$CONCOURSE_POSTGRES_DATABASE --port=$CONCOURSE_POSTGRES_PORT <<EOF
do \$$
declare
    b record;
    startValue int;
begin
    for b in
        select id, name, pipeline_id, team_id from builds where (status = 'started' OR status = 'pending') and completed = false
    loop
        raise notice 'dropping sequence build_event_id_seq_% ...', b.id;
        execute 'drop sequence if exists build_event_id_seq_' || b.id;

        if b.name = 'check' then
            execute 'select max(event_id) from check_build_events where build_id=' || b.id into startValue;
        elsif b.pipeline_id is null or b.pipeline_id = 0 then
            execute 'select max(event_id) from team_build_events_' || b.team_id || ' where build_id=' || b.id into startValue;
        else
            execute 'select max(event_id) from pipeline_build_events_' || b.pipeline_id || ' where build_id=' || b.id into startValue;
        end if;

        if startValue is null then
            startValue := 0;
        else
            startValue := startValue + 1;
        end if;

        raise notice 'creating sequence build_event_id_seq_% ...', b.id;
        execute 'create sequence build_event_id_seq_' || b.id || ' minvalue 0 start with ' || startValue;
    end loop;
end \$$;

DELETE FROM migrations_history WHERE version = 1627322257;
EOF

And done some manual testing and it seems to work. Basically what this script does is run the down migrations necessary for downgrading from 7.4.1 or 7.4.2 and also removes the history that the up migration that was included in 7.4.1 or 7.4.2. This is a little different from what a proper downgrade does to your database but I needed to do it this way because of a little unfortunate situation (if you want to know more I explain it in more detail here https://github.com/concourse/concourse/issues/7884)

So for users that are currently on 7.4.1 or 7.4.2, if you run the script that I pasted above you will be able to upgrade to 7.4.3, 7.5.0 or 7.6.0. Or if you want you can also downgrade back to 7.4.0 (you will NOT need to run any concourse migrate commands anymore to downgrade to 7.4.0 if you run the script).

The script requires you to have the psql, access to your postgres database and also the following environment variables that you should already have from starting concourse $CONCOURSE_POSTGRES_PASSWORD, $CONCOURSE_POSTGRES_USER, $CONCOURSE_POSTGRES_HOST, $CONCOURSE_POSTGRES_DATABASE, $CONCOURSE_POSTGRES_PORT.

I think I’m going to create a script that you can run against your database that will fix it and then you will be able to downgrade back to 7.4.0!