Migrating DB ordertable (< 0.9.5 to ≥ 0.9.5)

Instructions

1. Open UMHLens

2. Open a Shell inside the timescale pod

   

3. Create a backup of the order table !

   pg_dump --host localhost --port 5432 --username postgres --verbose --file "/var/lib/postgresql/ordertable.sql" --table public.ordertable factoryinsight
   

   

4. Execute

   psql
   

   

5. Select the factoryinsight db

   \c factoryinsight
   

   

6. Check if any old orders would fail under the new constraints

   SELECT order_name, asset_id, count(*) FROM ordertable GROUP BY order_name, asset_id HAVING count(*) > 1;
   

   

7. If there are any conflicts, remove the conflicting data

   DELETE FROM ordertable ox USING (
        SELECT MIN(CTID) as ctid, order_name, asset_id
        FROM ordertable
        GROUP BY order_name, asset_id HAVING count(*) > 1
        ) b
   WHERE ox.order_name = b.order_name AND ox.asset_id = b.asset_id
   AND ox.CTID <> b.ctid;
   

   If the data can not be removed (e.g. is still required), please make sure to rename the order_names to prevent duplicates !

   

8. Retrieve the name of the old unique constraint

   SELECT conname FROM pg_constraint WHERE conrelid = 'ordertable'::regclass AND contype = 'u';
   

   

9. Delete the old constraint

   ALTER TABLE ordertable DROP CONSTRAINT ordertable_asset_id_order_id_key;
   

   

10. Create the new constraint

    ALTER TABLE ordertable ADD CONSTRAINT ordertable_asset_id_order_name_key UNIQUE (asset_id, order_name);
    

    

11. Exit

    exit
    

    

12. Close the pod shell. The effect is immediate, you don’t need to restart the container