Rolling back a Postgres database

If a customer downgrades their instance to a previous version, they need to downgrade their database schema in two circumstances:

1. Developer created backwards-incompatible migration: if a migration added in the successor version that was not backwards-compatible, the code of the previous version may struggle operating with the new schema. This may be an emergency situation in which the previous schema must be restored to bring the instance back to a stable state.
2. The customer is downgrading multiple versions: if a customer has downgraded their instance once, they will need to downgrade their database schema before downgrading their instance a subsequent time. This is because an instance two (or more) versions ago has no guarantee to run properly against the current database schema. Multiple instance downgrades therefore need to be performed in an alternating fashion with database downgrades.

1. To roll back the database to a specific version, we need to determine the set of leaf migrations defined at that version. This can be done via the sg development tool. This should be ran by a supporting Sourcegraph engineer in an up-to-date clone of the sourcegraph/sourcegraph repository. For example, if we are downgrading from 3.37.0 to 3.36.0:

$ sg migration leaves v3.36.0
Leaf migrations for "frontend" defined at commit "v3.36.0"
 1528395968: (track fork namespace on spec)

Leaf migrations for "codeintel" defined at commit "v3.36.0"
 1000000030: (lsif data implementations)

Leaf migrations for "codeinsights" defined at commit "v3.36.0"
 1000000025: (captured values series points)

2. Once we have discovered our target leaf versions, we need to unappaly all migrations applied after those migrations. For the following, consult the Kubernetes or Docker-compose instructions on manually run database migrations. The specific migrator commands to run will be unique to each schema that needs to be downgraded. For the example above, we'll need to issue three downto commands:

• downto -db=frontend -target=1528395968
• downto -db=codeintel -target=1000000030
• downto -db=codeinsights -target=1000000025

The log output of the migrator should include INFO-level logs and successfully terminate with migrator exited with code 0. If you see an error message or any of the databases have been flagged as "dirty", please follow "How to troubleshoot a dirty database". A dirty database at this stage requires manual intervention. Please contact support at mailto:[email protected] or via your enterprise support channel for further assistance.