TiDB Lightning can import up to 500 GiB/hour in physical mode. Learn the safest Postgres dump, transform, import, and validation path. Read now.
Plan the Migration Path Before You Dump
Moving from Postgres to TiDB for global scale is not a lift-and-shift of the binary. TiDB speaks MySQL-compatible SQL and stores data in a distributed key-value layer, so schema shape, index design, and dump format all affect safety and speed. Start by inventorying tables, sequences, views, stored procedures, extensions, and any Postgres-only types or constraints. Decide which objects map cleanly, which need rewrite, and which stay outside TiDB (for example, heavy procedural logic often moves to the application). Set a clear cutover model: one-time bulk load with downtime, or bulk load plus change capture if you need a longer dual-write window.
Agree on success criteria up front: row counts, checksum strategies, latency targets under multi-region traffic, and how you will roll back if validation fails. A documented dump → transform → import → validate path keeps the team aligned and reduces ad-hoc fixes mid-cutover.
Dump Postgres in a Form TiDB Can Consume
Prefer a logical dump that produces portable SQL or delimited data rather than a binary-only backup you cannot reshape. Export schema and data separately when possible so you can fix types, defaults, and indexes before bulk load. Watch for Postgres-specific constructs: serial and identity columns, arrays, JSONB nuances, partial indexes, exclusion constraints, and extension-backed types. Convert sequences to auto-increment or application-assigned IDs as your design requires. Normalize timestamps and time zones explicitly so global readers do not inherit silent offset bugs.
For large tables, dump in parallel by partition or primary-key ranges and keep files sized for stable network transfer. Record exact dump timestamps and source WAL or LSN position if you plan a follow-on sync. Never assume a dump is complete until row counts and a sample of critical tables match production.
Transform Schema, Then Import With TiDB Lightning
Transform the schema into TiDB-compatible DDL before import. Choose primary keys that distribute well (avoid hot single-value keys), drop or defer secondary indexes that are not required for first cutover, and size tables for regional access patterns. Convert unsupported types to closest equivalents and document every mapping so validation teams know what “equal” means. Keep foreign keys and complex constraints optional during load if they slow bulk import; re-enable or enforce them after data is in place.
TiDB Lightning is built for high-volume load. In physical mode it can import up to 500 GiB/hour by writing data files that the storage layer can ingest efficiently, which is usually far faster than row-by-row inserts for multi-hundred-gigabyte or multi-terabyte Postgres estates. Use physical mode when you control the target cluster, can pause competing writers on the destination, and have validated that the transformed files match Lightning’s expected layout. Stage files close to the cluster, pre-check disk and CPU headroom, and run a small pilot table end-to-end before the full dataset. If you need online writers during load or cannot take the destination offline, evaluate logical import modes instead—slower, but safer under concurrent traffic.
- Schema-only dry run: apply DDL on a staging cluster and fix failures before touching data.
- Pilot import: one representative large table plus one high-churn table; measure throughput and errors.
- Full import: load in dependency order (parents before children if constraints are active).
- Index rebuild: add secondary indexes after bulk data lands when that shortens total window.
Validate Thoroughly Before Global Traffic
Validation is the safety net for the dump–transform–import path. Compare source and target row counts per table, then sample or full-table checksums on critical columns. Re-run application read paths against TiDB with the same queries you use in production: pagination, multi-column filters, and transactions that span related rows. Check auto-increment / sequence continuity, nullability, and uniqueness on keys that must stay unique after type conversion. For global scalability, exercise latency from more than one region and confirm that read replicas or placement rules match your locality goals.
Only after automated checks and a controlled canary of real traffic should you switch writers. Keep the Postgres dump and Lightning input files until the cutover is stable, and document the exact transform rules so the next migration or re-import is repeatable. A careful path—clean dump, explicit schema transform, Lightning physical import when volume demands it, and hard validation—gets you to TiDB without trading reliability for speed.