Migration Pipeline

The default pgferry flow is built to load data first and add expensive constraints later.

Stage order

Stage	What happens	Full	schema_only	data_only
1	Load and validate config	Yes	Yes	Yes
2	Introspect the source schema	Yes	Yes	Yes
3	Create target schema and tables	Yes	Yes	No
4	Run before_data hooks	Yes	No	Yes
5	Optional target-table truncate (truncate_before_copy)	Yes	No	Yes
6	Stream data with COPY	Yes	No	Yes
7	Run after_data hooks	Yes	No	Yes
8	Optional validation	Yes	No	Yes
9	SET LOGGED for full migrations using UNLOGGED tables	Yes	No	No
10	Add primary keys	Yes	Yes	No
11	Add indexes with bounded parallelism	Yes	Yes	No
12	Run before_fk hooks	Yes	Yes	No
13	Optional orphan cleanup	Yes	No	No
14	Add foreign keys	Yes	Yes	No
15	Reset sequences	Yes	Yes	Yes
16	Optional unsigned checks	Yes	Yes	No
17	Optional trigger emulation	Yes	Yes	No
18	Run after_all hooks	Yes	Yes	Yes

Modes

Full migration

This is the default. pgferry creates tables, loads data, then adds constraints and post-load objects.

schema_only = false
data_only = false

Use this unless you have a specific operational reason to split schema creation and data load.

schema_only

schema_only = true

Use this when you want to inspect or adjust the target schema before any data is loaded. It skips:

• before_data
• data COPY
• after_data
• row-count validation
• SET LOGGED
• orphan cleanup

data_only

data_only = true

Use this when the target schema already exists from a prior schema_only run or from your own DDL. It skips:

• table creation
• primary keys and indexes
• before_fk
• orphan cleanup
• foreign keys
• unsigned checks
• trigger creation

Operational note: before COPY starts, pgferry verifies that the target role can run ALTER TABLE ... DISABLE TRIGGER ALL and later re-enable those triggers on the selected target tables. If that preflight fails, pgferry aborts before copying any data.

This matters because data_only loads into a schema where foreign keys and other triggers may already exist. pgferry temporarily disables those triggers during the load so parallel COPY can proceed without immediate FK enforcement.

When an external schema migrator also inserts seed or reference rows, set truncate_before_copy = true in the data-only config. pgferry will run one TRUNCATE TABLE ... statement for the selected target tables after before_data hooks and before COPY, so duplicate source rows do not collide with pre-seeded target rows. pgferry does not add CASCADE; if non-selected tables still reference the selected tables, PostgreSQL will reject the truncate instead of deleting out-of-scope rows.

For multi-schema data-only batches with cross-schema foreign keys, set truncate_before_copy = "once" only on the first schema config and set truncate_before_copy_schemas to every target schema in the batch. pgferry discovers tables in those schemas and runs one TRUNCATE TABLE ... CASCADE before COPY, avoiding later per-schema cascades into already-loaded schemas. If a listed schema is missing or has no ordinary tables to truncate, pgferry fails before issuing TRUNCATE.

After COPY, pgferry advances existing auto-increment sequences with setval, resolving each sequence from the target catalog: first via pg_get_serial_sequence (identity columns and OWNED BY sequences, under whatever name the external migration tool gave them), then from the column’s DEFAULT nextval(...) expression (custom-named sequences without OWNED BY, as Entity Framework creates them), then by pgferry’s own {table}_{col}_seq naming from a schema_only run. If no sequence is found for an auto-increment column, the run fails with an error naming that column. pgferry does not create sequences or change column defaults in data_only; the pre-existing schema is expected to own that DDL.

Two-phase workflow

pgferry migrate schema-migration.toml  # schema_only = true
pgferry migrate data-migration.toml    # data_only = true

Tradeoff: this is slower than the default full pipeline because data is loaded with indexes and foreign keys already present.

Snapshot strategy

source_snapshot_mode = "none"

• Fastest mode.
• Tables or chunks can run in parallel.
• Best when source writes are already paused or consistency across tables does not matter.

source_snapshot_mode = "single_tx"

• Supported on MySQL, MariaDB, and MSSQL.
• Reads the source inside one read-only transaction for a stable point-in-time view.
• Gives up parallel source reads for consistency.

Use single_tx when the source stays live during migration and the tables must agree with each other at one point in time.

Chunking

pgferry chunks a table only when it has a single-column numeric primary key.

Chunkable tables

• MySQL integer PKs such as tinyint, smallint, mediumint, int, bigint
• MariaDB integer PKs such as tinyint, smallint, mediumint, int, bigint
• SQLite integer primary keys
• MSSQL numeric integer primary keys

Not chunkable

• composite primary keys
• non-numeric primary keys such as UUID or text
• tables with no primary key

What chunking changes

1. pgferry finds MIN(pk) and MAX(pk).
2. It splits the range into chunks of roughly chunk_size.
3. Each chunk becomes a bounded SELECT ... WHERE pk >= lower AND pk < upper.
4. Completed chunks can be checkpointed individually when resume = true.

Resume behavior

When resume = true, pgferry stores progress in pgferry_checkpoint.json next to the config file.

Use pgferry checkpoint status migration.toml when you want to inspect that file as human-readable progress instead of opening the JSON directly.

What the checkpoint protects

• completed table copies
• completed chunk copies
• migration shape compatibility, including hooks, identifier rules, type mapping, and table layout

What you need for safe resume

resume = true
unlogged_tables = false

Do not combine resume = true with on_schema_exists = "recreate" or "use", or with schema_only = true.

Validation timing

validation = "row_count" runs after after_data hooks and before post-load DDL like indexes and foreign keys.

This is intentional:

• table data is already present
• hook-driven cleanup or transforms can run first
• expensive post-load objects are not built yet if validation finds a mismatch

Standalone validate

pgferry validate migration.toml

This command is outside the migrate pipeline. It loads the same TOML config, reconnects to the source and target, introspects the selected tables, and reruns the configured validation mode only.

It skips:

• schema creation
• data COPY
• hooks
• checkpoints
• post-load DDL like indexes, foreign keys, and sequence reset

Use it when you want to compare the current source state against an already-loaded target after cutover or on a later check.

Practical operating advice

• Use the full pipeline for most rehearsals and first production attempts.
• Split into schema_only and data_only only when you need manual schema review or a more controlled cutover sequence.
• Treat data_only as privilege-sensitive on PostgreSQL. Managed services and restricted roles often block trigger control on pre-existing tables.
• Prefer resume = true for large runs where restarts are expensive.
• Prefer validation = "row_count" for the final rehearsals before cutover.