Skip to main content

What to Watch When DB Types Differ

When the DB that produced the source dump and the target DB you apply it to are different products (for example, applying a PostgreSQL dump to a MySQL DB), the SQL syntax is incompatible and the apply can fail. This guide explains how KIOPS handles engine compatibility, and what is and is not possible in the current UI.

Before You Start: Terms to Know​

  • Source DB: The DB that produced the original dump. The file itself contains the source DB's syntax verbatim.
  • Target DB: The destination DB where the dump will be applied.
  • Engine compatibility group: A set of DBs whose SQL syntax is mutually compatible. KIOPS treats MySQL and MariaDB as one group, and PostgreSQL as a separate group.
  • Cross-engine: Two DBs in different compatibility groups (for example, MySQL ↔ PostgreSQL).
  • Schema conversion: Translating the column types, defaults, and options in DDL statements such as CREATE TABLE into another engine's syntax.

Key Point: What Is Possible in KIOPS Today​

Cross-engine conversion is not exposed in the current UI

KIOPS's backend has logic to convert MySQL/MariaDB ↔ PostgreSQL dumps, but the current UI has no entry point for enabling a conversion option. Neither the data package apply screen (PackageApplyModal) nor the package-apply path in the Migration tab offers a conversion option.

The combinations you can apply a SQL dump with in KIOPS today are as follows.

Source dump engineTarget DB engineCan apply
MySQL / MariaDBMySQL / MariaDBYes (same compatibility group)
PostgreSQLPostgreSQLYes (same engine)
MySQL / MariaDBPostgreSQLNot applicable
PostgreSQLMySQL / MariaDBNot applicable

MySQL and MariaDB are in the same compatibility group, so they can be applied to each other without conversion.

For cross-engine combinations, KIOPS's behavior depends on the apply path. The package-apply in the Migration tab checks engine consistency and disables the apply button, preventing it up front. The data package apply screen (PackageApplyModal) has no pre-check guard, so attempting a cross-engine apply there will fail due to SQL syntax mismatch. Either way, a cross-engine DB cannot be applied without conversion.

Prerequisites​

Check before using
  • DB connection registered: The target DB to apply to must be registered on the DB Connections tab.
  • Engine match check: Confirm in advance that the engine of the dump you have and the engine of the target DB are in the same compatibility group.

The Migration Tab Blocks Cross-Engine Applies​

In the Migration tab, when you set "Source type" to "Stored data package (one-time apply)", KIOPS automatically checks the engine consistency between the selected package's DB type and the target DB.

  • Same compatibility group (MySQL↔MariaDB included): An "engine consistency OK" notice appears and the apply button is enabled.
  • Cross-engine: A "Cross-engine - migration not possible" warning appears and the apply button is disabled. Different engines have incompatible SQL syntax, so the apply would fail.

So an attempt to apply a PostgreSQL dump to a MySQL DB, or vice versa, without conversion is blocked by KIOPS up front.

If You Must Move Data Between Different DB Types​

If you need to move data between environments of different DB products, you cannot do it with automatic conversion inside the current KIOPS UI. Consider these approaches.

  1. Prepare a dump matching the target engine externally: Ask the source DB owner for a dump produced in the target engine's syntax, or use an external conversion tool to produce a converted .sql file in advance and upload it to KIOPS.
  2. Use Sync Jobs for recurring data replication: If you need to keep data in sync between two DBs periodically, check the scope of the [Database Management] > Sync Jobs tab.
  3. Ask your operations team about exposing the conversion feature: The backend conversion logic itself exists, so if you need to use cross-engine conversion from the UI, ask the KIOPS operations team whether the feature can be enabled.

Reference: Scope of the Backend Conversion Logic​

The following describes the scope of the conversion logic implemented in KIOPS's backend. It is not called directly from the current UI, so it only matters in environments where the operations team has separately enabled the conversion feature. General users may skip this section.

MySQL/MariaDB → PostgreSQL (schema only)​

This direction converts the schema only - data rows (INSERT) are not converted. The main mappings are:

Source (MySQL/MariaDB)Converted result (PostgreSQL)
Backtick identifier `col`backticks removed
int + AUTO_INCREMENTSERIAL
bigint + AUTO_INCREMENTBIGSERIAL
smallint + AUTO_INCREMENTSMALLSERIAL
tinyint(1)BOOLEAN
other tinyint(n)SMALLINT
int(N) / bigint(N) / smallint(N)display width removed, then INTEGER / BIGINT / SMALLINT
datetimeTIMESTAMP
blob / longblob / mediumblob / tinyblobBYTEA
longtext / mediumtext / tinytextTEXT
longtext + CHECK(json_valid(...))JSONB
ENGINE=..., DEFAULT CHARSET=..., COLLATE=... table optionsremoved
current_timestamp()CURRENT_TIMESTAMP
KEY / UNIQUE KEY definitionssplit into separate CREATE INDEX statements

INSERT data rows are not converted, so for this direction you must load data separately on top of the converted schema.

PostgreSQL → MySQL/MariaDB (schema + optional data)​

This direction converts the schema and, when the option is on, can also convert the COPY data block. The main mappings are:

Source (PostgreSQL)Converted result (MySQL/MariaDB)
public. schema prefixremoved
SERIALINT AUTO_INCREMENT
BIGSERIALBIGINT AUTO_INCREMENT
SMALLSERIALSMALLINT AUTO_INCREMENT
BOOLEANTINYINT(1)
TIMESTAMP (with/without time zone)DATETIME
BYTEALONGBLOB
JSONBJSON
CHARACTER VARYING(n)VARCHAR(n)
casts such as 'foo'::textcast removed
DEFAULT nextval(...)removed
table optionsENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci added
COPY data blockconverted to INSERT statements when the option is on

User-defined functions, triggers, sequences, extensions, VIEWs, and so on are not converted.

Always Verify After Applying a Converted Result​

Engine conversion is automated but not 100% perfect. If you applied a converted dump, verify the following right after the apply.

  1. Table count and column count: Compare against the source DB to confirm no tables are missing.
  2. Column types: Confirm key business columns (amounts, dates, JSON, etc.) were created with the intended types.
  3. Indexes and constraints: Confirm PK / UK / FK / NOT NULL carried over.
  4. Defaults: Confirm CURRENT_TIMESTAMP and sequence-based defaults landed correctly.
  5. Sample queries: Check that frequently used SELECT/INSERT statements run normally.
  6. Data rows: For the MySQL→PG direction, empty data is normal. For the PG→MySQL direction, if you moved data too, compare row counts.
Do not apply straight to production

Rehearse the converted result on staging first, run the checklist above once, and only then apply to production. In particular, user-defined functions, triggers, sequences, and extensions are not subject to automatic conversion.

What to Do Next​