Migrating DB ordertable (< 0.9.5 to ≥ 0.9.5)

This migration has to be done on every server, which was originally created using stack version < 0.9.5

  less than a minute  

Instructions

  1. Open Lens

  2. Open a Shell inside the timescale pod

Untitled

  1. 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

Untitled

  1. Execute
psql

Untitled

  1. Select the factoryinsight db
\c factoryinsight

Untitled

  1. 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;

Untitled

  1. 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 !

Untitled

  1. Retrieve the name of the old unique constraint
SELECT conname FROM pg_constraint WHERE conrelid = 'ordertable'::regclass AND contype = 'u';

Untitled

  1. Delete the old constraint
ALTER TABLE ordertable DROP CONSTRAINT ordertable_asset_id_order_id_key;

Untitled

  1. Create the new constraint
ALTER TABLE ordertable ADD CONSTRAINT ordertable_asset_id_order_name_key UNIQUE (asset_id, order_name);

Untitled

  1. Exit
exit

Untitled

  1. Close the pod shell, the effect is immediat, you don’t need to restart the container
Last modified August 8, 2022: feat:knowledgeexport of 03.08.2022 (#60) (bfe1130)