ScaleArc DB Migration utilizes AWS Database Migration Service (DMS). It is responsible for the migration of tables and secondary schemas.
Major Disparities Possible
Some major limitations due to which partial data loss or structural disparity can happen are listed below.
Postgres Server
- Only Postgres version 9.4 or higher is supported.
- Fields with the following data types are not supported:
- PATH
- POLYGON
- OID
- For tables having the below mentioned data types and no primary key, only staging migration can be done, cdc (live-migration) is not possible.
- TEXT
- BYTEA
- TSVECTOR
- TSQUERY
- XML
- JSON
- JSONB
- ARRAY
- COMPOSITE
- Precision or scale is lost for the following data types. We provide the fix for these types in the pre-migration validation report:
- NUMERIC
- DECIMAL
- VARCHAR
- CHARACTER VARYING
- TIME [(p)] [without time zone]
- TIME [(p)] [with time zone]
- For Numeric and Decimal types, pre-migration validation reports check whether the value would be the same post-migration or not. But pre-migration validation will fail to detect the change in scale.
- For example: pre-migration validation report will issue that 1.1234567 will lose its scale post-migration as after actual migration value will be 1.123456 != 1.1234567.
- But pre-migration validation will fail to capture any issue for this data 1 or 1.1234560 as after actual migration 1.000000=1 and 1.123456=1.1234560.
- No fix is available currently to avoid the microseconds value loss for the time data type.
- The following row values are not migrated properly:
- NaN value changes to 0 for a column with Numeric/Decimal data type
- infinity - infinity value changes to current_date for a column with Date data type
On top of DMS, our service takes care of migrating these secondary schemas (listed below):
- Check Constraint
- Unique Constraint
- Primary Keys
- Foreign Keys
- Generated Column
- Identity Columns
- Default Values
- Custom Data Types and Domains
- Auto Increment
- Stored Procedures
- Indexes
- Views and Materialized views
- Rules
- Triggers
- Schemas
- Sequences
- Triggers
- Migration of Lob columns without staging