Including the 181.sql migration script which creates indices.
primary db impacted is 'softwareheritage' on host belvedere
Description
Description
Event Timeline
Comment Actions
- staging db already migrated during the deployment of T3861.
- production db migration ongoing
Comment Actions
staging, prod: storage deployed and service restarted (it's not dependent on the sql migration to be complete).
belvedere: migration still ongoing
next step: once the first migration is done, migration to run on somerset.
Comment Actions
belvedere migration status: first index on directory created, ongoing index creation for revision, and then release.
Actually there is no need to migrate the somerset replica whose flavor is read-replica.
[1]
softwareheritage=# \conninfo
You are connected to database "softwareheritage" as user "postgres" via socket in "/var/run/postgresql" at port "5433".
softwareheritage=# select now(), * from dbflavor ;
now | flavor | single_row
-------------------------------+--------------+------------
2022-01-20 13:59:43.880917+00 | read_replica | x
(1 row)Comment Actions
migration done.
softwareheritage=# \pset pager off
Pager usage is off.
softwareheritage=# \i /srv/softwareheritage/postgres/swh-storage/sql/upgrades/181.sql
insert into dbversion(version, release, description)
values(181, now(), 'Work In Progress');
INSERT 0 1
select swh_get_dbflavor() = 'default' as dbflavor_default
create index concurrently directory_raw_manifest_not_null on directory(id) where raw_manifest is not null;
CREATE INDEX
create index concurrently revision_raw_manifest_not_null on revision(id) where raw_manifest is not null;
CREATE INDEX
create index concurrently release_raw_manifest_not_null on release(id) where raw_manifest is not null;
CREATE INDEX
softwareheritage=# \d directory
Table "public.directory"
...
Indexes:
...
"directory_raw_manifest_not_null" btree (id) WHERE raw_manifest IS NOT NULL
...
softwareheritage=# \d revision
Table "public.revision"
...
Indexes:
...
"revision_raw_manifest_not_null" btree (id) WHERE raw_manifest IS NOT NULL
...
softwareheritage=# \d release
Table "public.release"
...
Indexes:
"release_raw_manifest_not_null" btree (id) WHERE raw_manifest IS NOT NULL
...