Version: 2.3.0

V2.2 to V2.3

Steps to migrate REGARDS from version 2.2 to 2.3

Database modifications

In version 2.3, several changes were made to the tables ingest.t_aip and fem.t_feature (see the release note for more information).

Although these changes are automatically applied when launching the new version of the server, they may take several minutes or even hours, depending on the number of projects and the volume of products. The migration may involve several restarts of the microservice rs-ingest and rs-fem.

Therefore, it is highly recommended for projects with a large set of OAIS or GeoGson products (over 100k products) to run the following migration SQL script separately after having shut down the server and before deploying the new version.

ALTER TABLE ingest.t_aip
    ADD COLUMN IF NOT EXISTS category varchar(128);

ALTER TABLE ingest.t_sip
    ADD COLUMN IF NOT EXISTS category varchar(128);

DROP INDEX IF EXISTS ingest.idx_aip_categories;

CREATE OR REPLACE PROCEDURE update_categories(tablename TEXT)
LANGUAGE plpgsql
AS $$
DECLARE
    start_time TIMESTAMP := clock_timestamp();
    total_duration INTERVAL;
    row_count INTEGER := 0;
    total_row_count INTEGER := 0;
BEGIN
    RAISE NOTICE 'Migrating % table...', tablename;
    start_time := clock_timestamp();
    LOOP
        EXECUTE format('
            WITH cte AS (
                SELECT id
                FROM ingest.%1$I
                WHERE category IS NULL AND jsonb_typeof(categories) = ''array'' AND jsonb_array_length(categories) > 0
                LIMIT 2000
                FOR UPDATE SKIP LOCKED
            )
            UPDATE ingest.%1$I product
            SET category = categories ->> 0
            FROM cte
            WHERE product.id = cte.id;', tablename);
        GET DIAGNOSTICS row_count = ROW_COUNT;
        total_row_count := total_row_count + row_count;
        EXIT WHEN NOT FOUND;
        COMMIT;
        total_duration := clock_timestamp() - start_time;
        RAISE NOTICE '% rows updated (average: % rows/s)', total_row_count, (total_row_count /
              (SELECT extract ('epoch' from total_duration)));
    END LOOP;
    RAISE NOTICE 'Migration of % table complete.', tablename;
END
$$;

CALL update_categories('t_aip');
CALL update_categories('t_sip');

DROP PROCEDURE update_categories;

CREATE INDEX IF NOT EXISTS idx_aip_category ON ingest.t_aip USING btree (category);

CREATE INDEX IF NOT EXISTS idx_aip_last_update_id ON ingest.t_aip USING btree (last_update, id);

DROP INDEX IF EXISTS idx_feature_model_last_update;
CREATE INDEX IF NOT EXISTS idx_feature_model_last_update_id ON fem.t_feature USING btree (model, last_update, id);

DO $$ BEGIN RAISE NOTICE 'Migration for REGARDS 2.3.0 complete.'; END $$;

This script can be run with a psql client, on each database used by a REGARDS tenant:

psql -h db_host -d db_name -U db_user < migration2.3.sql

If this script is interrupted for any reason (a network issue for instance), just relaunch it and it will continue the migration. Its full completion is confirmed by the message:

NOTICE:  Migration for REGARDS 2.3.0 complete.

Database modifications​

Database modifications