Postgres to TiDB Migration Guide for Global Scalability

Migrating from PostgreSQL to TiDB is less about swapping one SQL engine for another and more about changing the operational model behind your application. TiDB gives you horizontal scale, distributed transactions, and MySQL-wire compatibility, but the move works best when you treat it as a controlled schema translation plus bulk import exercise. As of May 05, 2026, PingCAP's stable self-managed docs are on v8.5, and the most reliable path from Postgres is still dump, transform, load, then cut over.

• TiDB Lightning physical mode is documented at up to 500 GiB/hour for initial loads.
• TiDB DM supports MySQL-compatible sources, not PostgreSQL, so final cutover needs a write freeze.
• Use pg_dump with insert output for portability, then split files so Lightning can parallelize work.
• Review PostgreSQL-only features first: sequences, triggers, functions, extensions, and type differences.

Why This Migration Works

This approach works because TiDB ingests MySQL-compatible SQL well, while PostgreSQL's logical dump can emit plain SQL scripts. The catch is compatibility: TiDB is highly compatible with MySQL 5.7 and 8.0, not PostgreSQL syntax. That means your job is to convert the DDL once, then feed TiDB portable insert statements. If you need to clean sample data for rehearsal environments, use TechBytes' Data Masking Tool before exporting production-like records.

When to Choose Postgres vs TiDB

Choose Postgres when:

• Your application depends heavily on PostgreSQL extensions, stored procedures, or trigger-driven workflows.
• You do not need multi-node write scale and your current bottleneck is still operational, not architectural.
• Your team wants to preserve PostgreSQL-native tooling end to end.

Choose TiDB when:

• You need one logical SQL layer across regions or fast-growing workloads.
• You are already comfortable standardizing on MySQL-compatible application behavior.
• You want a high-throughput initial import path with TiDB Lightning and distributed scale-out later.

Step-by-Step Migration

Step 1. Audit the PostgreSQL schema before you export

Do not start with the data. Start by identifying what cannot move as-is. In practice, the most common blockers are PostgreSQL-specific extensions, sequence semantics, trigger logic, and data types that need MySQL-compatible replacements.

psql "$PGURL" -c "\dn"
psql "$PGURL" -c "\dx"
psql "$PGURL" -c "SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog','information_schema');"

For each table, decide whether the TiDB version should keep the same shape or simplify it. A typical translation looks like this:

-- PostgreSQL
CREATE TABLE accounts (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- TiDB-compatible rewrite
CREATE TABLE accounts (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Step 2. Install the TiDB import tooling

PingCAP's documented path is to install TiUP first, then install the components you need.

curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh
source ~/.bashrc

tiup install dumpling
tiup install tidb-lightning
tiup --version

If you want to normalize or clean up generated SQL before review, run your translated DDL through the TechBytes Code Formatter. That is especially useful when you are diffing multiple schema revisions under time pressure.

Step 3. Export PostgreSQL as portable SQL inserts

For TiDB, plain SQL is the most straightforward PostgreSQL export format because it avoids PostgreSQL's native COPY syntax. The tradeoff is speed: --inserts and --column-inserts are slower to generate, but far safer for a non-Postgres target.

mkdir -p /data/pg-export

pg_dump "$PGURL" \
  --schema-only \
  --quote-all-identifiers \
  --file=/data/pg-export/00-schema-postgres.sql

pg_dump "$PGURL" \
  --data-only \
  --inserts \
  --column-inserts \
  --quote-all-identifiers \
  --file=/data/pg-export/10-data.sql

Do not import the PostgreSQL schema file directly. Use it only as reference. Create a separate TiDB-ready schema file after translation:

mysql -h ${TIDB_HOST} -P 4000 -u ${TIDB_USER} -p${TIDB_PASSWORD} < /data/pg-export/01-schema-tidb.sql

For large databases, avoid one monolithic data file. TiDB Lightning's docs note that a single SQL file is hard to split efficiently, and file sizes around 256 MiB are a better target. Dump by table or logical group so Lightning can parallelize the load.

Step 4. Prepare TiDB Lightning

For initial imports into empty tables, TiDB recommends the local backend for large volumes. The stable docs compare that mode at 100~500 GiB/hour versus 10~50 GiB/hour for the tidb backend. Use tidb if the dataset is under 1 TiB or if the cluster must stay fully available during import.

[lightning]
level = "info"
file = "tidb-lightning.log"

[tikv-importer]
backend = "local"
sorted-kv-dir = "/data/sorted-kv"

[mydumper]
data-source-dir = "/data/pg-export"

[tidb]
host = "${TIDB_HOST}"
port = 4000
user = "${TIDB_USER}"
password = "${TIDB_PASSWORD}"
status-port = 10080
pd-addr = "${PD_ADDR}"

mkdir -p /data/sorted-kv
tiup tidb-lightning --config tidb-lightning.toml

Step 5. Run the cutover with a short write freeze

This is the operational difference many teams miss. TiDB Data Migration supports MySQL-compatible upstreams, not PostgreSQL. So there is no official PingCAP direct PostgreSQL-to-TiDB incremental replication path to keep tail changes flowing while users continue writing. The practical answer is a controlled freeze window:

1. Rehearse the migration on a recent snapshot.
2. Stop application writes to PostgreSQL.
3. Take the final PostgreSQL data export for changed tables or the full small-delta set.
4. Import the delta into TiDB.
5. Run validation queries and smoke tests.
6. Switch the application connection string to TiDB.

Verify and Cut Over

Expected output

The fastest signal of import success is the TiDB Lightning log. PingCAP's docs call out the phrase below as the success indicator.

grep -n "the whole procedure completed" tidb-lightning.log

You also want application-level checks, not just transport-level checks:

-- PostgreSQL
SELECT COUNT(*) FROM accounts;
SELECT MAX(id) FROM accounts;

-- TiDB
SELECT COUNT(*) FROM accounts;
SELECT MAX(id) FROM accounts;
SHOW TABLES;

At minimum, verify these items before DNS or app cutover:

• Row counts match for every critical table.
• Primary and unique key behavior matches your expectations.
• Boolean, timestamp, and text columns round-trip correctly through the app.
• Read-after-write paths succeed against TiDB from the real application runtime.

Troubleshooting

Top 3 issues

• Import is slow or appears stuck: A single giant SQL file limits TiDB Lightning parallelism. Re-export by table or split files toward 256 MiB chunks, then rerun the import.
• DDL fails on TiDB: Your translated schema still includes PostgreSQL-native constructs such as sequences, extension types, or trigger-dependent behavior. Rewrite those objects into MySQL-compatible DDL before retrying.
• Data drift appears at cutover: Writes continued on PostgreSQL after the final export. Because TiDB DM does not consume PostgreSQL directly, the fix is procedural: enforce a clean write freeze and rerun the delta import.

What's Next

• Benchmark the migrated workload under real concurrency, not only synthetic import speed.
• Review primary key and hotspot behavior after cutover, especially on append-heavy tables.
• Automate schema translation rules so future PostgreSQL services follow the same pattern.
• Turn the migration runbook into an internal standard for rollback, validation, and smoke tests.