Correct Procedure to copy production data to test location : Portal

Start a new topic

Correct Procedure to copy production data to test location

Alistair Middlemiss

started a topic 1 day ago

We want to test new data sources in our test environment with our current production data that has been fully data stewarded to assess the impacts on existing data stewarded data.

We attempted to do a full copy over of the data location from production to test environment, and that moved the data over effectively, it caused a range of issues - the execution engine errored out due to duplicate key violations and it caused the entire test data base to be flagged as a production instance. There would probably have been other issues found but reset the instance to stop issues.

What is the best / correct procedure to copy over production data to a test environment in a consistent, and ideally automatable way?

1 Comment

Haashim Jhungeer

said about 3 hours ago

Hi Alistair,

kindly find below the correct procedure to avoid these errors. Please try this in a lower environment.

Correct procedure

Never copy the repository schema between environments - repository (models, jobs, security, data location definitions) and data location schemas are separate and must stay environment-specific. Only the data location schema (the hub data schema) should move.
Confirm the model edition deployed in test is compatible with (ideally the same as) the one in production before restoring data.
Dump/restore only the data location schema (via your database's native backup/restore or pg_dump/Oracle Data Pump equivalent) into the test schema.
Before creating the data location in the test workbench against that restored schema, run the cleanup procedure so the schema is not seen as already belonging to a registered data location:
- Back up the schema first.
- Drop DL_DATA_LOC.
- Copy the other DL_* tables to temporary USR_DL_* backup tables.
- Drop all remaining DL_* tables and the three sequences (SEQ_MATCHGRP, DL_SEQ_FILTER, DL_SEQ_DUPS_OPERATION), noting their current values first.
- In the test workbench, create a new data location referencing this now-"empty" schema (this regenerates fresh DL_* tables/sequences).
- Stop the application, restore data into the new DL_* tables from the USR_DL_* backups (excluding DL_DATA_LOC), realign the three sequences to their prior values, restart, verify, then drop the USR_DL_* backup tables.
Separately check SEQ_LOADID and SEQ_BATCHID in the repository schema - if these aren't advanced past production's values, replayed loads/batches in test can collide with or reprocess production load/batch IDs.
Recreate continuous loads and data/job notification policies in test manually - these are data-location-level configs tied to the environment and shouldn't be carried over via schema copy.

Automation note: because this involves manual SQL against internal system tables, Semarchy doesn't provide a supported one-click automation for it. If you want this repeatable, script steps 3–4 (dump/restore + the DL_ cleanup SQL) with your DB tooling (e.g. a shell/SQL wrapper or CI job) - this is safe to automate since it only touches the data location schema, not the repository.

Thanks,
Haashim