Legacy Data Migration — What to Check Before You Move Anything

In projects that build a new system, data migration is the task that most often wrecks the schedule.

The reason is simple: before the project starts, nobody knows the actual state of the data. The client's own staff do not know either. Nobody remembers every exception that has piled up over ten years of using that system.

That is why an effort estimate based only on "200,000 member records and 1.5 million order records" is almost always wrong. Row counts have little to do with how hard a migration is. What determines the difficulty is how many kinds of exceptions are mixed in.

This article sets out the order in which to approach a data migration when you are preparing for one.

---

1. Look at the real data first

Reading a design document or a table definition is not the same thing as opening the real data.

Documents and data differ

These are the things that turn up again and again in old systems.

Repurposed columns are especially tricky. There are cases where, if a "remarks" column contains a particular phrase, the business treats the record as being in a separate status. The rule is not in any document; only the people doing the work know it. Things like this are found only by scanning the data, spotting odd patterns and asking about them.

What to check in the survey

This is the list to check before signing the contract, or right after kickoff.

• Actual row count and growth trend for each table
• Null ratio and list of distinct values for each column
• Minimum and maximum values and format distribution of date columns
• Actual distribution of code values (including values not in the definition document)
• The proportion of foreign key relationships that actually hold
• The criteria for judging duplicates and the number of duplicates
• Character set and whether there are corrupted characters
• Total size of attached files and whether they actually exist

Attached files are often overlooked. It is common for the database to hold a file path while the actual file is missing. The volume to be migrated and the proportion of lost files must be checked in advance.

Get the numbers with profiling queries

The items above cannot be filled in by scanning with the eye. You have to run the same queries against every table and record the results as numbers; only then do they serve as the basis for the estimate, and as the baseline to compare the migration result against later.

The examples below assume they are run in a staging schema (legacy) into which the source has been copied, and were verified by running them on PostgreSQL 17. Table and column names are illustrative. Syntax such as FILTER, btrim and ~ differs between DBMSs, so when you run the queries directly on the source DBMS you must rewrite them in that DBMS's syntax.

-- Null and blank ratio, number of distinct values, and maximum length (characters and bytes) of one column
SELECT count(*) AS total_rows,
       count(*) FILTER (WHERE phone IS NULL) AS null_rows,
       count(*) FILTER (WHERE btrim(phone) = '') AS blank_rows,
       count(DISTINCT phone) AS distinct_values,
       round(100.0 * count(*) FILTER (WHERE phone IS NULL OR btrim(phone) = '')
             / nullif(count(*), 0), 2) AS empty_pct,
       max(char_length(phone)) AS max_chars,
       max(octet_length(phone)) AS max_bytes
FROM legacy.member;

-- Actual distribution of code values. Rows where is_undefined is true are values not in the definition document
SELECT m.status, count(*) AS row_count, (c.code IS NULL) AS is_undefined
FROM legacy.member m
LEFT JOIN legacy.code_status c ON c.code = m.status
GROUP BY m.status, c.code
ORDER BY row_count DESC;

-- Format distribution of a date column stored as a string
SELECT CASE
         WHEN reg_date IS NULL OR btrim(reg_date) = '' THEN '(empty)'
         WHEN reg_date ~ '^\d{8}$' THEN 'YYYYMMDD'
         WHEN reg_date ~ '^\d{4}-\d{2}-\d{2}$' THEN 'YYYY-MM-DD'
         ELSE 'other'
       END AS date_format,
       count(*) AS row_count, min(reg_date) AS min_value, max(reg_date) AS max_value
FROM legacy.member
GROUP BY 1
ORDER BY row_count DESC;

Nulls and blank strings are counted separately. Both look like an empty field on screen, but the conversion rules have to treat them differently. If you extract the maximum length in both characters and bytes, you can check in advance for the length problems covered in Section 3.

For duplicates and orphan records, the judgment criteria are spelled out in the query itself.

-- Duplicate candidates: members with the same name + date of birth + phone number reduced to digits
SELECT name, birth_date, phone_digits, count(*) AS duplicate_count,
       min(member_id) AS first_id, max(member_id) AS last_id
FROM (SELECT member_id, name, birth_date,
             regexp_replace(phone, '[^0-9]', '', 'g') AS phone_digits
      FROM legacy.member) m
GROUP BY name, birth_date, phone_digits
HAVING count(*) > 1;

-- Orphan records: number of orders whose referenced member does not exist
SELECT count(*) AS orphan_rows
FROM legacy.orders o
WHERE o.member_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM legacy.member m WHERE m.member_id = o.member_id);

What the duplicate query returns is candidates. They may be different people with the same name and date of birth, so it is the business owner who decides what counts as the same entity. For values whose notation varies, such as phone numbers, duplicates will be undercounted unless you normalize before comparing.

Survey queries put load on the production database. They read entire tables, so it is safer to run them on a replica or on a copy restored from a backup; if they have to be run directly on the production database, agree on the time window with the client.

Use the survey results as the basis for the estimate

We recommend making this survey a separate phase and deciding the migration scope and effort from its results.

Two contract structures are possible.

• Separate the preliminary survey into its own contract, and sign the main contract based on the results
• Include it in the main contract, with a clause stating that the scope will be adjusted according to the survey results

Either way, putting in writing that "an accurate estimate is not possible before the survey" is the safe course for both sides. The procedure for handling a scope that changes as a result of the survey is covered separately in How Scope Grows in SI Projects and How to Design Change Management to Contain It.

---

2. Decide what to move and how far

Moving everything seems the obvious choice, but in many cases it is not.

Decide first what will not be migrated

• Old history — whether logs from five years ago really need to be moved
• Withdrawn members — data whose mandatory retention period has passed
• Test and duplicate data
• Data for features that are no longer used
• Data that has no corresponding concept in the new system

If some data is excluded, you also have to decide what to do with it: destroy it, keep it separately as read-only, or export it to files for retention.

In practice, a common approach is to keep the old system running in read-only mode for a set period. When past records are needed, they are looked up there. In that case the retention period and access rights must be decided.

For data that contains personal information, the question to ask before "should we move it" is "are we allowed to be holding it at all". Article 21 of the Personal Information Protection Act (개인정보 보호법) requires that personal information which has become unnecessary, because its retention period has expired or the purpose of processing has been achieved, be destroyed without delay, and provides that where it must be preserved under other statutes, that personal information must be stored and managed separately from other personal information. (Personal Information Protection Act) The point at which the migration scope is decided is the best time to sort this out.

• Data on withdrawn members whose retention period has passed is not a migration target but a destruction target. Moving it to the new system amounts to making one more copy of data that should have been destroyed.
• Data that must be preserved because of other statutes should be reflected in the design so that, in the new system as well, it is kept in a location separate from operational data.
• The same standard applies to the old system kept in read-only mode and to files exported for retention. Decide in advance who will destroy them, and by what method, when the retention period ends.

Which data is subject to retention, under which statute and for how long, differs by industry. This judgment must be confirmed by the client's privacy officer or legal staff and recorded in writing; it is not something for the contractor to decide by guesswork.

Decide what to do with structures that do not correspond

If the structure of the new system differs from the existing one, the data cannot be moved as it is.

These situations occur frequently.

• What used to be a single field is split into several items in the new system
• The existing code scheme does not map one-to-one to the new scheme
• A new mandatory item has been added, but past data has no value for it
• The existing system has status values that do not exist in the new system

For each of these, conversion rules must be decided and documented: what to fill a mandatory item with when there is no value, where to send codes that have no counterpart, and so on.

This decision is not a technical one but a business judgment. The contractor must not decide it arbitrarily; confirmation from the business owner is required.

Aligning code schemes takes longer than expected. In the MES / BOM build we delivered, a considerable part of the early period went into cleaning up master data, consolidating materials that each department called by a different name under a single code, and that process required confirmation from the business users several times. This time should not be buried inside the development schedule; it should be scheduled separately.

Decide the cutoff point

Migration moves the data as of a specific point in time. But the existing system keeps running.

These are the things to decide.

• The migration cutoff point
• How to catch up on data created after that point
• Whether to stop the existing system at cutover, and if so, for how long

The length of time the system can be stopped determines the migration method. If it can be stopped for a few hours, moving everything at once is simple. If it cannot be stopped, you need a structure that moves data in stages and catches up on the delta, and the difficulty rises sharply.

Attached files need a separate plan

Moving database rows and moving files are different tasks.

These are the things to check.

• Total size and number of files
• Storage location — server disk or external storage
• Whether the path convention differs by period
• The proportion of paths recorded in the database that match actual files
• Whether the same file is referenced from several records
• Whether file names contain Korean characters or special characters

When the volume is large, the transfer time itself becomes a schedule factor. Moving hundreds of gigabytes within a few hours on cutover day is difficult. In that case the files are moved in advance and only the delta is synchronized at cutover.

How to handle cases where the actual file is missing must also be decided: whether to exclude the record, or to move it without the file and keep a list.

Choose the migration method

Phased cutover reduces risk, but during the cutover period the data exists split across two systems. You must first check whether any work needs a consolidated view during that period.

Parallel operation is the most complex. You have to decide which side is the system of record, judge whether two-way synchronization is needed, and have rules for what to do when the two diverge. The cost rises considerably, so it is better to start by checking whether downtime is truly impossible. Synchronization between two systems is in effect a system integration, so the principles of retries, idempotency and reconciliation covered in Where External System Integrations Fail — Principles of Interface Design apply as they are.

Which of the three methods to choose is decided by checking the criteria below in order.

The first row is the criterion that filters first. If the allowed downtime is 8 hours and loading and validation took 7 hours in rehearsal (illustrative figures), there is no margin for rolling back, so big-bang cutover drops out of the options. In that case, consider moving past history in advance to reduce the load volume on the day, or switching to a phased cutover.

---

3. Write the conversion rules as a document first, not as code

If you start by writing scripts straight away, later nobody will be able to explain why the data was converted that way.

Organize by item

Decide the following for each item to be migrated.

The last item, Confirmed by, matters. It is the evidence when someone later asks "why did it end up like this".

The reason for writing the validation method on the same line as the rule is that when a conversion rule changes, the reconciliation query has to change with it. If they are managed separately, you end up fixing only the rule and running validation against the old rule.

Keep the code mapping table separately

Things that need a correspondence, such as status values and classification codes, are managed in a table.

Old code          →  New code    Remarks
01 (Received)     →  RECEIVED
02 (In progress)  →  PROCESSING
03 (Completed)    →  COMPLETED
04 (On hold)      →  ON_HOLD
99 (Other)        →  ?           Needs business confirmation

Leaving values with no counterpart as question marks and filling them in one by one is a good way to see progress.

Check character sets and lengths

In a migration where the DBMS changes, the same value is stored differently. The behavior described below was confirmed in each product's official documentation; if your version differs, check the documentation for that version again.

Korean encodings. Old systems are often stored in EUC-KR or CP949 (MS949), and new systems are usually UTF-8. The two are not the same thing. CP949 contains Hangul syllables that EUC-KR does not have, so if you declare CP949 data as EUC-KR and convert it, the conversion fails on some characters. Checking on PostgreSQL 17, the single character 똠 converts with UHC (CP949) but with EUC_KR raises an error saying there is no equivalent character. Such characters are mixed into personal names and business names, so even if you have been told the source is EUC-KR, it is safer to try reading it as CP949.

PostgreSQL can use EUC_KR as a server encoding, but supports UHC only as a client encoding, and raises an error when it meets a character that cannot be converted. (PostgreSQL Documentation, Character Set Support) So a CP949 file is loaded into a UTF-8 database with the encoding stated explicitly.

-- Run in psql. Loads a CSV saved as CP949 into a staging table in a UTF-8 database
\copy legacy.member FROM 'member.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UHC')

If ENCODING is omitted, the file is interpreted in the current client encoding. (PostgreSQL Documentation, COPY) Rows that raise a conversion error are cases where the source is already corrupted or the actual bytes differ from the declared character set, so do not skip them; extract them as a list and find the cause.

MySQL's utf8. In MySQL, utf8 is an alias for utf8mb3, and its use is not recommended. utf8mb3 uses at most 3 bytes per character and supports only Basic Multilingual Plane (BMP) characters, so it cannot store supplementary characters such as emoji. If the target is MySQL, create it with utf8mb4. (MySQL 8.4 Documentation, utf8mb3, utf8mb4) If the source is a MySQL database declared as utf8, it contains no 4-byte characters in the first place, so the risk in this case lies not in the past data but in the input that will come into the new system.

Oracle's empty string. Oracle Database treats a character value with a length of zero as null. (Oracle Database 19c SQL Language Reference, Nulls) Data that comes out of Oracle therefore contains no empty strings, only nulls. PostgreSQL and MySQL distinguish the two, so once the new application starts storing empty strings after the migration, "no value" exists in two forms in a single column. This becomes the cause of screens and statistics that queried with IS NULL quietly going wrong. In the opposite direction, that is, a migration into Oracle, empty strings become null and violate NOT NULL constraints. Write down in the mapping specification, for each column, what "no value" is to be unified as.

Unit of length. A column declared without a qualifier, such as Oracle's VARCHAR2(20), follows the session's NLS_LENGTH_SEMANTICS value, whose default is BYTE. (Oracle Database 19c SQL Language Reference, Data Types, Globalization Support Guide) PostgreSQL's varchar(n), on the other hand, counts characters, not bytes. (PostgreSQL Documentation, Character Types) One Hangul character is 2 bytes in CP949 and 3 bytes in UTF-8, so problems arise in both directions.

• If only the character set of a byte-based column is changed to UTF-8, a column that held 10 Hangul characters in 20 bytes now holds only 6. Values that exceed the declared length appear.
• If you bring the source's number over as is and create a character-based column, there is conversely room to spare, but values that grow longer in the new system may exceed the field widths of screens, printed output and fixed-length messages sent to other systems.

Decide the target length based on the maximum character count and maximum byte count obtained from the profiling in Section 1. Avoid truncating values that exceed the length when you meet them. PostgreSQL raises an error when a value that exceeds the length is inserted, but an explicit cast to varchar(n) truncates it without an error. (Character Types documentation above) If a conversion script contains such a cast, names and addresses pass the count check while truncated.

According to the same documentation, PostgreSQL cannot store the character with code zero (NUL) in a string. If this character is found in an old column into which fixed-length messages were stored as they were, decide on a removal rule before loading.

Check dates and time zones

It is easy to think that dates only need their format aligned, but the meaning of the data type differs between products.

(Oracle Database 19c SQL Language Reference, Data Types, MySQL 8.4 Documentation, DATE, DATETIME, TIMESTAMP, Server SQL Modes, PostgreSQL Documentation, Date/Time Types)

Date-times in legacy data are mostly stored in Korean time with no time zone notation. If these are put into a timestamp with time zone column from a session set to UTC, they are off by 9 hours, and for records around midnight the date shifts by a day. This is the result confirmed on PostgreSQL 17.

SET TIME ZONE 'UTC';
SELECT '2024-03-01 00:30:00'::timestamptz;  -- 2024-03-01 00:30:00+00 (off by 9 hours)

SET TIME ZONE 'Asia/Seoul';
SELECT '2024-03-01 00:30:00'::timestamptz;  -- 2024-03-01 00:30:00+09 (the intended value; February 29 15:30 in UTC)

State the session time zone explicitly in the first line of the load script, and do not leave it to the default of the server or the client tool. PostgreSQL does not accept 0000-00-00 as a date (a range error on 17), so zero dates also need a conversion rule before loading.

Numbers are examined in the same way. Oracle NUMBER(p, s) raises an error for values that exceed the precision, but rounds values that exceed the scale without an error. (Data Types documentation above) If the scale of the target column is smaller than the source, amounts and quantities change silently, so numeric items are not left out of the sum reconciliation.

Make it reversible

Migration scripts get run many times. They must be built on that assumption from the start.

• Running them several times must give the same result
• It must be possible to return to the state before execution
• It must be possible to re-run only a part
• The source data is never modified

For protecting the source, it is better to allow no exceptions. If cleansing is needed, do it on a copy, and access the source read-only.

Keep the source identifier

If each row in the new system also stores its identifier in the existing system, later reconciliation and re-migration become easier. Situations also arise in operation where you need to check "which record in the old system is this one".

If you put a unique constraint on the source identifier, the condition that "running it several times gives the same result" is guaranteed by the database rather than by the script. Below is an example for PostgreSQL (verified by running on 17). It assumes there is a unique constraint on app.orders.legacy_id.

INSERT INTO app.orders (legacy_id, member_id, ordered_on, amount, status)
SELECT o.order_id, m.id, o.order_date, o.amount,
       CASE o.status WHEN '01' THEN 'RECEIVED' WHEN '02' THEN 'PROCESSING' END
FROM legacy.orders o
JOIN app.member m ON m.legacy_id = o.member_id
ON CONFLICT (legacy_id) DO UPDATE
SET member_id  = EXCLUDED.member_id,
    ordered_on = EXCLUDED.ordered_on,
    amount     = EXCLUDED.amount,
    status     = EXCLUDED.status;

Running the same statement twice does not add rows, and if you run it again after correcting a conversion rule, the existing rows are updated under the new rule. There are two points to watch. In this example CASE returns null for status values that are not in the mapping table, so the target column must have a NOT NULL constraint for them to surface as an error instead of going in silently. And orders dropped by the JOIN (orphan records with no member) disappear without a trace, so they must be recorded separately in the reject list covered in Section 4.

---

4. Validation is half of the migration

Checking that the data was moved correctly is harder than moving it.

Check in stages

You must not stop once the counts match. Cases where the counts match but a particular column is entirely empty do actually occur.

Prepare the reconciliation queries in advance

Counts, sums and hashes: the further down the list, the wider the range of problems caught and the higher the cost. Write all three as queries and run the same ones at every rehearsal. Below is an example that assumes the copy of the source (legacy) and the target (app) are in the same PostgreSQL database; it was verified by running on 17.

-- Count: source_rows = loaded_rows + rejected_rows must hold
SELECT (SELECT count(*) FROM legacy.orders) AS source_rows,
       (SELECT count(*) FROM app.orders)    AS loaded_rows,
       (SELECT count(*) FROM app.migration_reject
         WHERE run_id = 'rehearsal-2' AND source_table = 'orders') AS rejected_rows;

-- Sum: outputs only the months whose monthly count or amount total differs. Zero rows means they match
SELECT coalesce(s.month, t.month) AS month,
       s.row_count AS source_rows, t.row_count AS target_rows,
       s.amount_sum AS source_sum, t.amount_sum AS target_sum
FROM (SELECT to_char(order_date, 'YYYY-MM') AS month,
             count(*) AS row_count, sum(amount) AS amount_sum
      FROM legacy.orders GROUP BY 1) s
FULL OUTER JOIN
     (SELECT to_char(ordered_on, 'YYYY-MM') AS month,
             count(*) AS row_count, sum(amount) AS amount_sum
      FROM app.orders GROUP BY 1) t ON t.month = s.month
WHERE s.row_count IS DISTINCT FROM t.row_count
   OR s.amount_sum IS DISTINCT FROM t.amount_sum
ORDER BY 1;

If you compare the sum only as a single overall line, you cannot tell where it went wrong. You have to break it down by the units the business uses, such as month, branch and status, to narrow down immediately the range where the difference occurred. If there are excluded or failed records, it is normal for that month's count and sum to differ, so go on to check that the difference agrees exactly with the count and amount in the reject list. IS DISTINCT FROM is used so that months present on only one side (null) are also caught as mismatches.

Hash reconciliation concatenates the item values row by row to produce a hash and compares the two sides.

-- Hash: outputs the source identifier of orders where even one item value differs
SELECT s.order_id
FROM (SELECT order_id,
             md5(concat_ws('|', to_char(order_date, 'YYYY-MM-DD'), amount::text,
                           coalesce(status, ''))) AS row_hash
      FROM legacy.orders) s
JOIN (SELECT legacy_id,
             md5(concat_ws('|', to_char(ordered_on, 'YYYY-MM-DD'), amount::text,
                           CASE status WHEN 'RECEIVED' THEN '01'
                                       WHEN 'PROCESSING' THEN '02' ELSE '' END)) AS row_hash
      FROM app.orders) t ON t.legacy_id = s.order_id
WHERE s.row_hash <> t.row_hash;

These are the rules to keep in hash reconciliation.

• Bring both sides to the same representation before hashing. For items whose codes changed, convert one side back, and turn dates and numbers into strings with a fixed format. Left as they are, the values are equal but the notation differs, and everything comes out as a mismatch.
• Replace nulls explicitly. concat_ws skips null arguments, so unless you fill the place of nullable items with coalesce, different rows can produce the same hash.
• A hash does not catch errors in the conversion rules. That is because the reconciliation query uses the same mapping as the migration script. Whether the mapping itself is wrong comes to light only through confirmation by the business owner.
• If the source and target DBMSs differ, not only the function names but also the rules for converting numbers and dates to strings and the handling of trailing spaces differ, so the same value produces a different hash. In this case, exporting from both sides as strings in a fixed format and calculating the hash in one place is less error-prone.

When the row count is large, instead of comparing every row, first compare hashes grouped by ranges of the source identifier, and go down to row level only for the ranges that disagree.

Business owner confirmation is required

Some things are not caught by technical validation: cases where the value was moved but its meaning changed.

• A wrong code mapping causes the status to be displayed differently
• A date is off by a day because of a time zone difference
• The unit of an amount has changed
• A name or address has been truncated but looks plausible

These are found only when someone who knows the business looks directly at data they are familiar with. The business owners' time must be secured in the validation schedule. Validation often ends as a formality because this time was not reserved.

Collect errors instead of discarding them

When data that does not fit the rules is encountered during conversion, what to do with that record must already be decided.

• Stop the whole run, or skip only that record
• Where to record the skipped records
• Whether to keep the source data and the reason for failure together
• Whether only that record can be reprocessed after correction

Skipping silently is the most dangerous. The migration is finished, and months later it is discovered that several thousand records have disappeared. Failed records must always be kept as a list, and either processed until the number reaches zero or given an explicit reason for exclusion.

The reject list is easier to handle as a table than as a log file, because it can be aggregated by reason and used directly in the count reconciliation query. This is an example for PostgreSQL.

CREATE TABLE app.migration_reject (
  reject_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  run_id       text        NOT NULL,  -- which rehearsal run
  source_table text        NOT NULL,
  source_key   text        NOT NULL,  -- source identifier
  reason       text        NOT NULL,  -- reason category
  source_row   jsonb       NOT NULL,  -- the entire source row
  created_at   timestamptz NOT NULL DEFAULT now()
);

-- Records orders that are left out of the load because the member does not exist
INSERT INTO app.migration_reject (run_id, source_table, source_key, reason, source_row)
SELECT 'rehearsal-2', 'orders', o.order_id::text, 'member not found', to_jsonb(o)
FROM legacy.orders o
WHERE NOT EXISTS (SELECT 1 FROM legacy.member m WHERE m.member_id = o.member_id);

Because the entire source row is kept, this table also contains personal information. Manage its access rights at the same level as the operational tables, and decide the retention period and the time of destruction for after the migration is finished.

From PostgreSQL 17, COPY has an ON_ERROR ignore option that discards rows with errors and continues loading, and from 18 an upper limit on the permitted number can be set with REJECT_LIMIT. (PostgreSQL 17 Release Notes, PostgreSQL Documentation, COPY) However, this option deals only with errors raised while converting input values to their data types, and the discarded rows are merely reported in a notice as a count (with line numbers and column names if LOG_VERBOSITY verbose is given) and are not stored anywhere. This behavior is close to the "silent skipping" mentioned above, so we do not recommend it for the main migration load. Putting everything into a staging table where every column is a string, then judging in SQL whether each value can be converted and recording failures in the reject list, is easier to trace.

Large volumes change the processing approach

As the row count grows, simple methods no longer finish in time.

• Process in batches instead of one record at a time
• Consider removing indexes and constraints temporarily during the load and putting them back afterwards
• Split the work into ranges and record progress so that a failure midway does not mean starting again from the beginning
• Process as a stream instead of loading everything into memory

A structure that can resume from a midpoint is especially important. If a six-hour job fails in its fifth hour and has to be started again from the beginning, that day's cutover fails.

When the target is PostgreSQL, the bulk loading methods recommended in the official documentation, and the caveats that come with each, are as follows. (PostgreSQL Documentation, Populating a Database)

If COPY is given a file path on the server, the database server reads that file directly, so file access permission on the server side is required. Files on the operator's PC or a batch server are uploaded with psql's \copy. (COPY documentation above)

If you loaded with constraints removed, the step of putting them back is itself validation. If creating a unique index or foreign key fails, it means duplicates or orphan records got in, so the time this step takes and the procedure for handling a failure are also checked in rehearsal.

There is one more thing when identifiers are inserted with their source values as they are. In PostgreSQL, inserting values directly into an identifier column does not advance the sequence. If the sequence is not aligned after loading, the first registration after go-live raises a duplicate primary key error (reproduced and confirmed on 17).

-- Run for each table after loading is finished
SELECT setval(pg_get_serial_sequence('app.member', 'id'),
              (SELECT max(id) FROM app.member));

If the table is empty, max(id) is null, so the sequence is not changed and stays as it is.

Rehearse several times

Doing it for the first time on cutover day must be avoided.

• Rehearse at least two or three times with the full data
• Measure the elapsed time every time
• Sort out the problems found and correct the rules
• Run the last rehearsal under the same conditions as the real thing

Measuring the elapsed time is especially important. Testing with part of the data and scaling proportionally generally misses the mark, because there are stretches that slow down as the data grows. You need a time measured with the full data to be able to draw up a cutover plan.

---

5. Turn cutover day into a procedure

Cutover is hard to undo. The number of things that have to be judged on the day must be reduced to a minimum.

Make a timetable

① Announce the shutdown of the existing system and block access
② Final backup
③ Data extraction
④ Conversion and loading
⑤ Validation (count, sum, sample)
⑥ Business owner confirmation
⑦ Go-live decision
⑧ Open the new system

Write the expected duration and the person responsible for each step. Use the values measured in rehearsal.

Write completion criteria for each step

A timetable alone cannot tell you that "this step is finished". There has to be a document (a runbook) that states, for each step, what to execute and what to confirm before moving on, so that work proceeds by the same criteria even if the person in charge changes in the early hours.

In ①, batch jobs and interfaces are often missed. If screen access has been blocked but a nightly batch or an inbound interface from an external system runs and the source changes after extraction, those changes are caught by no reconciliation and disappear. Make the list of things to block before rehearsal, and notify the counterpart organizations of the interfaces in advance of the date and time of the suspension.

Record the actual start and end time of each step on the spot. You need to know early that things are running later than expected in order to make a judgment before the final decision time.

Decide the abort criteria in advance

If a problem is found during cutover, you have to decide whether to continue or roll back. Trying to decide at that moment clouds judgment.

• What problem, if found, means aborting
• Who decides to abort
• What the rollback procedure is
• How long rolling back takes
• By when the decision must be made (the final time)

Setting a final decision time is useful in practice. If it is set as, for example, "if validation is not finished by 6 a.m., we roll back", you can prevent pressing ahead recklessly under time pressure.

Make the criteria a table, not prose, and get the decision-maker's approval in advance. Below is an example of the structure; the actual criteria are agreed with the client to suit the nature of the business.

The point up to which you can roll back has an end. Before the new system is opened, the source has not been touched, so you can roll back by lifting the block on the old system. After opening, new data starts to accumulate in the new system, and rolling back from then on means moving that data back to the old system or re-entering it by hand. So the following two things are decided before cutover.

• How many hours (or days) after opening count as the window in which rollback is possible, and how data that came in during that window will be reflected in the old system
• The principle that problems found after that window are not rolled back but fixed in the new system

The final decision time cannot be later than the start of business minus the time it takes to roll back. This calculation holds only if the rollback procedure, too, has actually been carried out once in rehearsal and timed.

Notify users in advance

Apart from the technical work, a procedure for informing the people who use the system is needed.

• Advance notice of the date, time and duration of the shutdown
• Guidance on how to handle work during the shutdown
• Screens and procedures that change after cutover
• Where past records can be viewed
• Contact point if a problem occurs

How to handle work during the shutdown is often left out. Whether orders or applications that come in during that time will be taken on paper, or not taken at all, must be agreed with the business users. If it is not decided, the backlog ends up being entered by hand right after cutover.

When scheduling the cutover, peak business periods and closing dates must be avoided. It is safer to avoid cutting over during month-end settlement or at year-end.

Put the days after go-live into the plan

Things do not settle down immediately once cutover is finished.

• Staff to take the inquiries that come in heavily at the start
• A standby arrangement that can fix data-related errors immediately
• Guidance on how to look things up in the existing system
• Checking the state of the data for several days

If this period is not put into the contract and schedule, staff leave right after cutover and the response is delayed.

---

6. Frequently asked questions

How is migration effort estimated?

It is proportional to the number of target tables and the complexity of the conversion rules rather than to row counts. These are the factors used in the estimate.

• Number of tables to be migrated
• Proportion that is finished by simple copying
• Number of items that need code mapping
• Areas that need redesign because the structure differs
• Proportion of data that needs cleansing
• Number of validations and rehearsals

If the estimate has to be made without a preliminary survey, the right thing is to state explicitly that it is a rough amount premised on re-estimation after the survey.

Who does the data cleansing?

In principle it is the job of the data owner. The people who can judge which value is correct are the business owners.

In practice, however, it proceeds with the contractor proposing cleansing rules and the business owner confirming them. There are things to set out at that point.

• The types and rules that can be cleansed automatically
• The number of records that a person has to judge one by one
• The business owner time needed for those judgments

If the number of records a person has to judge is large, that in itself becomes a schedule factor. If several thousand records have to be checked, it takes several weeks.

What if the existing system cannot be stopped?

A zero-downtime cutover is possible, but the difficulty and cost go up. The approach is broadly as follows.

1. Move the past data in advance
2. Have a period in which the two systems run at the same time
3. Keep catching up on the changes that occur in the meantime
4. Pick a point in time and direct new input to the new system

With this approach there will inevitably be a stretch in which the data on the two sides diverges. A reconciliation procedure and rules for handling mismatches are needed along with it.

Whether it really cannot be stopped is worth checking again. A few hours in the early morning is allowed in quite a few cases, and then the difficulty drops considerably.

What if we cannot get cooperation from the people responsible for the existing system?

There are cases where the vendor that built the system being replaced is reluctant to cooperate. In reality this is not rare.

These are the things to check in that situation.

• How far the obligation to provide data is specified in the contract
• Whether database access rights can be obtained directly
• Whether the data can be received as a full backup file
• The extent of what can be obtained through the download functions of the system's screens

The client's right to access its own data is usually specified in the contract. We suggest checking the existing contract first.

If no documents or explanations are received, the structure has to be inferred from the data alone, and the survey period and the likelihood of errors grow accordingly. If this situation is expected, a longer preliminary survey period must be planned.

How is data containing personal information handled?

Protective measures are needed during the migration process as well.

• Storage location and access rights for extracted data files
• Destruction of intermediate files after the work is complete
• Whether real data will be used in development and test environments, and if so, what measures will be taken
• Access records of those performing the migration work

Using real data as it is in a test environment is sometimes done as a matter of custom, but the risk is high. It is safer to test with pseudonymized or anonymized data and to have a limited number of people carry out validation on real data in a controlled environment.

The table below maps provisions confirmed in the Personal Information Protection Act and the Enforcement Decree of the same Act (개인정보 보호법 시행령; the version in force as of September 11, 2026) to migration work. (Personal Information Protection Act, Enforcement Decree of the Personal Information Protection Act)

De-identifying data does not immediately make it something other than personal information. If only the name is changed and the phone number or address is left as it is, a specific individual can still be identified. Decide together with the client's privacy staff which items to change and how, and put the data into the test environment after the result of the processing has been confirmed.

Intermediate outputs remain in more places than you would think. Extract files, staging schemas, reject list tables, copies of the target database made at each rehearsal, and Excel files downloaded for validation all count. Unless each one is written on the list as it is created, it is difficult to find them all at the end.

The table above is a mapping of provisions to tasks, not a legal interpretation. Detailed standards such as the retention period for access records are set separately in the Enforcement Decree and public notices and are revised frequently, so the original text as of the time of the project and the client's internal management plan must be checked together.

---

7. Pre-kickoff checklist

Preliminary survey

• Have you opened and checked the real data?
• Have you checked the null ratio and distinct values for each column?
• Have you checked whether there are code values not in the definition document?
• Have you identified the number of records with broken foreign key relationships?
• Have you checked the volume of attached files and whether any are lost?
• Have you checked the actual character set of the source (EUC-KR or CP949) and the maximum length of each column?
• Have you re-estimated scope and effort based on the survey results?

Scope decision

• Is what will not be migrated defined?
• Is the handling of excluded data decided?
• Have you classified personal information past its retention period as a destruction target?
• Is the period for keeping the old system available for lookup agreed?
• Are the cutoff point and the handling of later data decided?
• Have you confirmed how long the existing system can be stopped?

Conversion rules

• Are there documented conversion rules for each item?
• Does each rule have business owner confirmation?
• Are there no blanks in the code mapping table?
• Does the specification contain the criteria for null versus empty string, unit of length and time zone handling?
• Is it safe to run several times?
• Is the source left unmodified?
• Is the source identifier stored along with the data?

Validation

• Are the methods for checking count, sum, sample and boundary decided?
• Are the reconciliation queries prepared as scripts, with the same ones run at every rehearsal?
• Are failed records kept as a list together with the source row and the reason?
• Is business owner confirmation time in the schedule?
• Have you rehearsed with the full data?
• Have you measured the elapsed time?

Cutover

• Is there a step-by-step timetable with persons responsible?
• Is the condition for moving to the next step written for each step?
• Is there a list of batch jobs and interfaces to block?
• Are the abort criteria and the decision-maker decided?
• Have you actually carried out the rollback procedure in rehearsal and timed it?
• Are the rollback window after opening and the principle beyond it decided?
• Are support staff for after go-live in the plan?
• Is the procedure for handling personal information decided?
• Is there a list of intermediate outputs such as extract files and staging, and a plan for destroying them?

---

Closing

The biggest risk in data migration is not technical difficulty but starting without knowing. Until you open the real data you cannot know what is in it, and what you cannot know you can neither estimate nor plan.

That is why the order matters: survey the data first, decide the scope and effort from the results, agree the conversion rules in writing, rehearse several times, and follow the defined procedure on cutover day.

If you skip the survey phase, its cost does not disappear; it comes back in a larger form late in the project.

We have carried out data migration many times in projects that replace existing systems. If you are considering replacing a system, we would be glad to start together from the stage of checking the state of the data you hold.

---

Related articles

• How Scope Grows in SI Projects and How to Design Change Management to Contain It
• Where External System Integrations Fail — Principles of Interface Design
• Designing a System That Meets Document Security Requirements: Access Control, Watermarks, and Audit Logs

References

• PostgreSQL Documentation, COPY
• PostgreSQL Documentation, Populating a Database
• PostgreSQL Documentation, Character Set Support
• PostgreSQL Documentation, Character Types
• PostgreSQL Documentation, Date/Time Types
• PostgreSQL 17 Release Notes
• MySQL 8.4 Documentation, The utf8mb3 Character Set
• MySQL 8.4 Documentation, The utf8mb4 Character Set
• MySQL 8.4 Documentation, The DATE, DATETIME, and TIMESTAMP Types
• MySQL 8.4 Documentation, Server SQL Modes
• Oracle Database 19c SQL Language Reference, Nulls
• Oracle Database 19c SQL Language Reference, Data Types
• Oracle Database 19c Globalization Support Guide, Choosing a Character Set
• Personal Information Protection Act (in Korean)
• Enforcement Decree of the Personal Information Protection Act (in Korean)