Beyond pg_dump: How YugabyteDB Voyager Turns Familiar Tools into a Migration Engine

YugabyteDB Voyager is an open-source migration engine that moves databases from PostgreSQL (and MySQL, Oracle) to YugabyteDB — a distributed SQL database designed for high availability and horizontal scaling.

Before diving into this article, it’s recommended to go through this prerequisite guide, which will help you better understand the concepts discussed here: article

What Is YugabyteDB Voyager?

YugabyteDB Voyager is an open-source migration engine that moves databases from PostgreSQL (and MySQL, Oracle) to YugabyteDB — a distributed SQL database designed for high availability and horizontal scaling.

The core problem it solves: migrating a production database is much harder than it sounds. You need to extract the entire schema — every table, index, function, trigger, constraint — in the right dependency order.

You need to export potentially billions of rows without slowing down your production system.

If it’s a live migration, you need to keep the source and target in sync while the source is still being written to.

And at the end, you need to load all of that into a different database system that has its own quirks and optimizations.

Voyager handles this end-to-end through a single CLI (yb-voyager). But rather than building everything from scratch, it leans heavily on PostgreSQL's own client tools — pg_dump, pg_restore, and the wire protocol — for the parts those tools already do well. The prerequisite article explains what those tools are and how they work under the hood. This article is about how Voyager puts them to work.

Why Voyager Doesn’t Run on Your Database Server

One of Voyager’s key design decisions is that it runs on its own separate machine — not on the source database server, and not on the target cluster. This matters for a few practical reasons:

No risk to production. Running a heavy export process directly on your database server competes for CPU, memory, and I/O with your production workload. A separate machine keeps the migration isolated.
Flexibility. The Voyager machine can be a dedicated host, a CI/CD runner, a VM in a different region, or even a developer’s laptop. It just needs network access to both the source and target.
Nothing to clean up. Voyager doesn’t install anything on your source or target servers. When the migration is done, you can shut down the Voyager machine and nothing changes on either database.

This is possible because of PostgreSQL’s client-server separation, which the prerequisite article covers in detail.

The client tools (pg_dump, psql, etc.) don't need to be on the same machine as the database server — they connect over the network using the wire protocol. Voyager simply installs those client tools on its own machine and points them at the source.

The Three-Machine Topology

In a typical migration, three distinct machines (or groups of machines) are involved:

┌──────────────────────┐     ┌──────────────────────┐     ┌──────────────────────┐
│   Source Machine     │     │   Voyager Machine    │     │   Target Machine     │
│                      │     │                      │     │                      │
│   PostgreSQL Server  │     │   yb-voyager CLI     │     │   YugabyteDB Cluster │
│   (port 5432)        │     │   pg_dump binary     │     │   (YSQL on port 5433)│
│                      │     │   pg_restore binary  │     │                      │
│   Has the data you   │◄TCP─┤   psql binary        ├─TCP►│   Receives the       │
│   want to migrate    │     │   Debezium (Java)    │     │   migrated data      │
│                      │     │   Go pgx driver      │     │                      │
│   Runs the database  │     │   SQLite (metadata)  │     │   Speaks PG wire     │
│   engine             │     │                      │     │   protocol via YSQL  │
└──────────────────────┘     └──────────────────────┘     └──────────────────────┘

Notice the middle box: the Voyager machine has the CLI, the PostgreSQL client tools, Debezium for change streaming, and a local SQLite database for tracking migration state. It doesn’t run a database engine — it’s purely an orchestrator that talks to both sides over the network.

It only needs the PostgreSQL client tools — because those tools connect to remote servers over the network using the wire protocol. This is the client-server separation in action.

Voyager’s Two Communication Channels

Voyager uses two fundamentally different mechanisms to interact with databases. Understanding when it uses each is key to understanding its design.

Channel 1: Direct SQL via the Go pgx Driver

For most database interactions, it opens a connection pool to PostgreSQL (or YugabyteDB) using the pgx driver — Go's most capable PostgreSQL driver. This is the programmatic equivalent of opening a psql session.

The connection is established using Go’s database/sql package with pgx as the backend:

// From postgres.go — how Voyager connects to the source
db, err := sql.Open("pgx", pg.getConnectionUri())
db.SetMaxOpenConns(pg.source.NumConnections)
db.SetConnMaxIdleTime(5 * time.Minute)

Through this connection pool, Voyager runs hundreds of targeted SQL queries. Here’s a sampling of what direct SQL handles:

Source discovery — understanding what’s in the database:

-- List schemas, tables, columns, indexes, partitions
-- Count rows (exact and approximate via reltuples)
-- Read sequence values and ownership
-- Check database size, encoding, server settings

Live migration plumbing — setting up Change Data Capture:

-- Create a publication for the tables being migrated
CREATE PUBLICATION voyager_pub FOR TABLE public.users, public.orders, ...;

Schema import to target — executing DDL against YugabyteDB:

-- Voyager reads the exported .sql files and executes each statement
CREATE TABLE public.users (id integer NOT NULL, name text, ...);
CREATE INDEX idx_users_status ON public.users USING btree (status);

Data import to target — bulk loading via the COPY protocol:

COPY public.users (id, name, status, created_at) FROM STDIN WITH (FORMAT text, DELIMITER E'\t');

Sequence restoration — after data migration, resetting sequences to their correct values:

SELECT pg_catalog.setval('public.users_id_seq', 2847201, true);
SELECT pg_catalog.setval('public.orders_id_seq', 12038470, true);

Direct SQL gives Voyager precise control over every operation: error handling, retry logic, batching, progress tracking, and YugabyteDB-specific optimizations.

Channel 2: pg_dump as a Subprocess

For two specific heavy-lifting jobs, Voyager doesn’t use direct SQL. Instead, it shells out to the pg_dump binary as a child process, because pg_dump encapsulates irreplaceable logic that would be impractical to reimplement.

Job 1: Schema Export

When you run yb-voyager export schema, Voyager asks pg_dump to extract the database's structure — just the blueprints (table definitions, indexes, functions, etc.), not the actual data.

/usr/lib/postgresql/17/bin/pg_dump \
  'postgresql://user:pass@source-host:5432/mydb' \
  --schema-only \
  --schema='public|inventory' \
  --no-owner \
  --no-privileges \
  --no-tablespaces \
  --no-comments \
  --load-via-partition-root \
  --file=/export/temp/schema.sql

Most of the flags are about stripping out things that won’t be relevant on the target database — ownership info, permissions, tablespace assignments, and inline comments.

The goal is a clean DDL dump that can be adapted for YugabyteDB.

Voyager stores these flags in a template file so the exact pg_dump invocation is generated automatically based on the migration configuration (which schemas to export, where to write the output, etc.).

The result is one big schema.sql file containing every CREATE TABLE, CREATE INDEX, CREATE FUNCTION, and so on — all in the right dependency order (types before tables, tables before indexes, etc.).

Splitting the schema into categories

A single giant SQL file isn’t very useful on its own. Different object types need different handling during import — for example, you might want to create indexes after loading data (for speed), or you might need to tweak function syntax for YugabyteDB compatibility. So Voyager takes that big file and sorts its contents into separate folders:

schema/
  tables/
    table.sql          ← CREATE TABLE, ALTER TABLE, constraints
  indexes/
    index.sql          ← CREATE INDEX
  sequences/
    sequence.sql       ← CREATE SEQUENCE, ALTER SEQUENCE
  functions/
    function.sql       ← CREATE FUNCTION
  views/
    view.sql           ← CREATE VIEW
  triggers/
    trigger.sql        ← CREATE TRIGGER
  mviews/
    mview.sql          ← CREATE MATERIALIZED VIEW
  ...

How does Voyager know which SQL belongs where? Conveniently, pg_dump labels every block of DDL with a comment header:

-- Name: users; Type: TABLE; Schema: public; Owner: -
CREATE TABLE public.users ( ... );

-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: -
CREATE SEQUENCE public.users_id_seq ...;

-- Name: idx_users_email; Type: INDEX; Schema: public; Owner: -
CREATE INDEX idx_users_email ON public.users ...;

Voyager reads the Type: label from each comment and routes the SQL that follows it to the matching folder. It recognizes 20+ object types — tables, indexes, sequences, functions, views, triggers, and many more.

Job 2: Data Export

When you run yb-voyager export data, Voyager uses pg_dump again — but this time for the actual row data, not the structure.

The key challenge here is speed and consistency: you want to export millions of rows as fast as possible, and you need all the data to represent a single point in time (no half-updated rows from concurrent transactions).

/usr/lib/postgresql/17/bin/pg_dump \
  'postgresql://user:pass@source-host:5432/mydb' \
  --data-only \
  --no-blobs \
  --compress=0 \
  --format=directory \
  --jobs=4 \
  --table='public.users' --table='public.orders' --table='public.events' \
  --snapshot=00000003-00000001-1 \
  --file=/export/data/

Here’s what the important flags do:

--data-only — the opposite of schema export: rows only, no structure
--format=directory + --jobs=4 — instead of writing one huge file, pg_dump creates a folder and uses four parallel connections to dump different tables at the same time. This is what makes large exports fast.
--snapshot=... — this is the interesting one. It tells all four parallel connections to read from the exact same point-in-time view of the database. Even though the connections are dumping different tables at different moments, they all see the same consistent state — as if the database was frozen at one instant.
--table=... — Voyager explicitly lists which tables to export, rather than dumping everything

Like the schema export, Voyager generates this command automatically from a template based on your migration configuration.

Why the snapshot matters for live migrations

The --snapshot flag is especially critical when migrating a database that's still in active use.

Here's the sequence: before starting pg_dump, Voyager sets up a logical replication slot on the source database. Creating that slot captures a snapshot — a "bookmark" in the database's change history. Voyager passes that snapshot to pg_dump, so the exported data is consistent with that exact bookmark.

Later, Debezium (the change-streaming tool) picks up from that same bookmark and streams every change that happened after it.

This handoff ensures no data is lost or duplicated between the initial bulk export and the ongoing change stream.

pg_restore: The Surprising Non-Restorer

Here’s a fact that surprises most people: Voyager uses pg_restore, but never to restore data. It only calls it in list mode:

pg_restore -l /export/data/

This reads the toc.dat manifest from the directory dump and outputs a human-readable table of contents, which Voyager parses to build a mapping of table names to their corresponding .dat data files. That's it.

The actual data import into YugabyteDB is done entirely through the Go pgx driver using COPY FROM STDIN.

This is a deliberate choice — by handling the import itself, Voyager can do things that a generic restore tool can't:

Faster bulk loading: For tables with a primary key, Voyager can skip the usual transaction overhead and use YugabyteDB’s optimized ingest path, which is significantly faster
Graceful error handling: If a batch of rows fails (say, due to a duplicate key), Voyager doesn’t abort the whole import. It retries that batch row-by-row to find and skip just the problem rows, then continues.
Tunable batch sizes: Voyager lets you control how many rows are committed at a time — smaller batches use less memory, larger batches are faster
Resumable imports: Voyager keeps track of which batches have already been loaded. If the migration is interrupted, it picks up where it left off instead of starting over

The psql Binary: Checked but Never Called

Here’s another surprising fact: Voyager never actually shells out to the psql binary at runtime. It verifies that psql exists and meets version requirements during installation and pre-flight checks, but all "psql-like" work (running SQL against databases) is done programmatically through the Go pgx driver.

The driver is strictly superior for Voyager’s needs: it provides connection pooling, prepared statements, binary protocol support, and programmatic error handling — none of which are easily available when shelling out to a subprocess.

The psql Binary: Checked but Never Called

The driver is strictly superior for Voyager’s needs: it provides connection pooling, prepared statements, binary protocol support, and programmatic error handling — none of which are easily available when shelling out to a subprocess.

Part 4: The Complete Migration Flow

Let’s trace through what happens during each Voyager command to see how all these tools work together.

yb-voyager assess-migration — Understanding the Source

This command analyzes the source database to produce a migration assessment report.

Direct SQL: Voyager connects to the source PostgreSQL via pgx and runs a comprehensive metadata-gathering script — querying catalog tables, pg_stat_statements (if available), table sizes, index statistics, data types in use, and potential compatibility issues with YugabyteDB.
pg_dump: Voyager runs pg_dump --schema-only to export the complete schema DDL, then parses it to identify object types and analyze SQL syntax for YugabyteDB compatibility (unsupported features, syntax differences, etc.).

Both mechanisms are used because they provide complementary information:

direct SQL gives live statistics and runtime metadata, while pg_dump gives the authoritative DDL representation.

yb-voyager export schema — Extracting the Blueprint

pg_dump (subprocess): Runs pg_dump --schema-only to produce schema.sql
Go code (in-process): Parses schema.sql and splits it into per-object-type files

yb-voyager export data — Moving the Data

For offline migration:

Direct SQL: Queries the source to get the table list, row counts, and column metadata
pg_dump (subprocess): Runs pg_dump --data-only --format=directory --jobs=N to export all table data in parallel

For live migration (PostgreSQL source):

Direct SQL: Creates a publication and logical replication slot on the source. The slot creation captures a transaction snapshot.
pg_dump (subprocess): Runs pg_dump --data-only --snapshot=<name> to export the initial data, pinned to the replication slot's snapshot
Debezium (Java subprocess): Starts reading from the logical replication slot, streaming all changes that occurred after the snapshot point

This handoff — from pg_dump snapshot to Debezium streaming — is the heart of live migration.

The snapshot name is the bridge that ensures no data is lost or duplicated between the initial load and ongoing replication.

yb-voyager import schema — Creating the Target Structure

Direct SQL only: Voyager reads the exported .sql files and executes each DDL statement against YugabyteDB's YSQL layer using the pgx driver. No subprocess tools are used.

yb-voyager import data — Loading the Target

Direct SQL only: Voyager reads the exported data files and streams them into YugabyteDB using COPY FROM STDIN via the pgx driver. It manages batching, parallelism, error recovery, and progress tracking entirely in Go code.

For YugabyteDB targets, Voyager can use an optimized fast path:

Normal mode:    BEGIN → COPY ... FROM STDIN WITH (ROWS_PER_TRANSACTION N) → COMMIT
Fast path mode: COPY ... FROM STDIN  (no transaction wrapper — YB handles it internally)

Fast path is enabled when the table has a primary key and the conflict action is set to IGNORE.

This triggers YugabyteDB’s internal ingest optimization, which is significantly faster because it avoids the overhead of explicit transactions and conflict detection at the SQL layer.

Sequence Restoration

After data import, Voyager restores sequence values to match the source. For offline migrations, it reads the sequence values from pg_dump's output (stored in postdata.sql from the TOC). For live migrations, it reads them from Debezium's export status. Then it executes setval() calls on the target:

SELECT pg_catalog.setval('public.users_id_seq', 2847201, true);

Part 5: Why pg_dump Can't Be Replaced with Direct SQL

A natural question is: why depend on pg_dump at all? Why not do everything with direct SQL?

The answer is that pg_dump solves two problems that are extremely hard to solve independently:

Problem 1: Complete, Correct Schema Extraction

To extract a database’s schema via direct SQL, you would need to:

Query every catalog table (pg_class, pg_type, pg_attribute, pg_constraint, pg_index, pg_proc, pg_trigger, pg_policy, pg_rewrite, pg_collation, pg_operator, pg_opclass, pg_opfamily, pg_conversion, pg_attrdef, pg_depend, and more)
Reconstruct syntactically correct DDL from the catalog data. For example, turning rows from pg_attribute + pg_type + pg_attrdef into a CREATE TABLE statement with correct column types, defaults, NOT NULL constraints, and generated column expressions.
Handle every PostgreSQL version’s catalog differences. PostgreSQL 10 added identity columns. 12 added generated columns. 14 added multirange types. 15 added security invoker views. Each version changes what’s in the catalog and how to interpret it.
Topologically sort all objects using pg_depend to ensure correct creation order.
Handle edge cases: circular dependencies, extension-owned objects, inherited tables, partitioned tables, row-level security policies, custom operators, and more.

This is essentially reimplementing pg_dump — 15,000+ lines of battle-tested C code that the PostgreSQL community maintains. It's not that it can't be done; it's that maintaining a parallel implementation would be an enormous ongoing burden.

Problem 2: Parallel, Snapshot-Consistent Data Export

To replicate pg_dump --format=directory --jobs=4 --snapshot=<name>:

Export a transaction snapshot: SELECT pg_export_snapshot()
Open N parallel connections
On each connection: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION SNAPSHOT '<snapshot_id>';
On each connection: COPY <table> TO STDOUT WITH (FORMAT text)
Handle error recovery, progress tracking, connection failures, and cleanup

This is doable, but you’d be reimplementing pg_dump's parallel execution framework. And you'd lose pg_dump's intelligent scheduling (it orders tables by size to minimize idle time across parallel workers).

Part 6: The Design Philosophy

Why This Architecture Works

Looking at the full picture, Voyager’s design follows a clear philosophy:

Use PostgreSQL’s own tools where they’re best. pg_dump encapsulates 20+ years of PostgreSQL catalog knowledge. Rather than reimplementing it, Voyager delegates schema extraction and parallel data export to pg_dump and focuses its own engineering on the parts that are unique to the migration problem.

Use direct SQL for everything else. The Go pgx driver gives Voyager programmatic control over connections, transactions, error handling, and the COPY protocol. This enables YugabyteDB-specific optimizations (fast path COPY, batch recovery, configurable transaction sizes) that generic tools like pg_restore can't provide.

Keep the Voyager machine stateless. By relying on client tools that connect over the network, Voyager doesn’t need to be co-located with either the source or target database. This gives deployment flexibility — the Voyager machine can be a dedicated migration host, a CI/CD runner, or even a developer’s workstation.

Respect the OS boundary. The installation script draws a clean line between OS-level dependencies (managed by the system administrator) and Voyager’s own components (managed by the installer). This avoids package conflicts, keeps the installation predictable, and works within the constraints of airgapped environments.