How to demote a package via the DB

If we are in a situation where we need to demote a package (for example, reverting a release during an incident management) without the availability of the builder UI/API, we can do so with direct access to the datastore / DB.

Here are the steps:

1. Ssh into the datastore node, and log in the builder_originsrv database.

2. Set the search path to the shard you're interested in (eg, shard 30 for core origin packages), eg:

set search_path to shard_30;

3. Find the origin id of the origin you want to demote FROM, eg (for core):

builder_originsrv=# select * from origins where name = 'core';

4. Find the channel id of the channel you want to demote FROM, eg (for stable):

select * from origin_channels where origin_id = '721096872374083614' and name = 'stable';

5. Find the package id of the package you want to demote, eg:

select id,name,ident from origin_packages where ident like 'core/hab-sup/0.60.0/%';

6. Finally, issue the demote command (in a transaction), eg:

begin;
delete from origin_channel_packages where channel_id = '721096872390901790' and package_id = '1037867140318117918';
commit;

The demote command above should only delete 1 row - make sure that is the case before you commit the transaction.