pg_dbmigrator

A Rust library and CLI for migrating PostgreSQL databases between two endpoints, a one-shot dump/restore for cold moves, and an online path that keeps PostgreSQL's built-in logical replication apply worker pulling from the source so the operator can cut over with near-zero downtime.

The online path issues CREATE SUBSCRIPTION on the target attached to a slot we created with EXPORT_SNAPSHOT before pg_dump ran.

Modes

Online migration phases

Validate → SourceVacuum → PrepareSnapshot → Dump → Restore → Analyze → StreamApply → (Lag heartbeat …) → CaughtUp → Cutover → SourceCleanup → Complete

• Validate pre-flights the source (wal_level = 'logical', max_replication_slots > 0, max_wal_senders > 0) and ensures the required publication exists — auto-creating it if missing (see Publication lifecycle below).
• SourceVacuum runs VACUUM ANALYZE on the source to reclaim dead tuples and refresh planner statistics before the dump. Skip with --skip-source-vacuum.
• PrepareSnapshot creates the replication slot first; START_REPLICATION is deferred until after the dump completes, so the exported snapshot remains valid for the dump.
• Analyze runs ANALYZE on the target after restore so the query planner has fresh statistics for the first application queries. Skip with --skip-analyze.
• During StreamApply the library polls pg_current_wal_flush_lsn() on the source every --cutover-poll-secs and emits a Lag progress event with lag_bytes / source_lsn / received_lsn / applied_lsn. This is the signal the customer watches to decide when to cut over.
• When the lag drops at or below --lag-threshold-bytes a one-shot CaughtUp event is emitted (“ready for cutover”).
• SourceCleanup (after cutover) drops auto-created publications and replication slots on the source — see the next section.

Online mode does not run an automatic row-count verification. Cutover stops the apply worker but does not freeze source writes, so an automatic count(*) compare would show spurious mismatches (readiness is signalled by the lag heartbeat, not row counts). Verify manually once the source is quiesced — see Verify.

Install

pg_dbmigrator shells out to pg_dump and pg_restore, so it needs a PostgreSQL client at least as new as your source server. The install script sets both up in one command.

One-liner

curl -fsSL https://raw.githubusercontent.com/isdaniel/pg_dbmigrator/main/install.sh | sh

Installs a static binary to ~/.local/bin (no root), then installs postgresql-client for your distro (needs root, via passwordless sudo/doas).

If you pass the connection strings, the installer asks the servers what they run — over the wire protocol, so this works before any client exists — and installs the client matching the newer of source and target rather than simply the newest available. That is the floor pg_dump needs, and stopping there avoids a newer pg_restore emitting settings an older target does not recognise. An existing pg_dump is left alone unless it is older than what the servers require.

curl -fsSL https://raw.githubusercontent.com/isdaniel/pg_dbmigrator/main/install.sh \
  | PG_DBMIGRATOR_SOURCE='postgres://…/src' \
    PG_DBMIGRATOR_TARGET='postgres://…/dst' sh

Supports Ubuntu, Debian, RHEL/Rocky/AlmaLinux, Amazon Linux 2023, Alpine, openSUSE/SLES and Fedora, on x86_64 and aarch64. On Debian/Ubuntu and RHEL-likes it pulls the client from apt.postgresql.org / yum.postgresql.org rather than the distro repos, whose clients are often too old to dump a modern server (Ubuntu 22.04 ships PG 14, RHEL 9 ships PG 13 — both refuse a PG 17 source).

Tunable with environment variables:

With cargo-binstall (prebuilt binary, no compile)

cargo binstall pg_dbmigrator

From source

cargo install pg_dbmigrator

Manual download

Grab a tarball and checksums.txt from the releases page, verify, and drop the binary anywhere on $PATH:

sha256sum -c checksums.txt
tar -xzf pg_dbmigrator-v0.3.0-x86_64-unknown-linux-musl.tar.gz

Binaries are statically linked against musl, so one build runs on any glibc or musl distro.

pg_dbmigrator --help
pg_dbmigrator --mode offline --source '…' --target '…' --jobs 4

CLI

Offline

pg_dbmigrator \
    --mode offline \
    --source 'postgres://user:pw@src.example/db' \
    --target 'postgres://user:pw@dst.example/db' \
    --jobs 4 \
    --drop-target-first

By default, VACUUM ANALYZE runs on the source before pg_dump and ANALYZE runs on the target after pg_restore. Disable with --skip-source-vacuum / --skip-analyze if you manage maintenance externally.

Before the dump runs, offline migrations also pre-flight the target role's privileges and extension availability (source extensions must be installable on the target), so a misconfigured target fails fast with a clear error instead of stalling inside pg_restore.

Online

On the source, before starting:

ALTER SYSTEM SET wal_level = 'logical';   -- requires restart

The publication is auto-created by the migrator if it does not already exist (default FOR ALL TABLES). If you prefer to create it manually — e.g. to publish only specific tables — run:

CREATE PUBLICATION pg_dbmigrator_pub FOR TABLE my_schema.t1, my_schema.t2;

pass --no-auto-create-publication so the migrator uses the existing one and does not attempt to create or drop it.

pg_dbmigrator \
    --mode online \
    --source 'postgres://user:pw@src/db' \
    --target 'postgres://user:pw@dst/db' \
    --slot-name pg_dbmigrator_slot \
    --publication pg_dbmigrator_pub \
    --subscription-name pg_dbmigrator_sub \
    --jobs 4 \
    --lag-threshold-bytes 8192 \
    --cutover-poll-secs 5

Before the dump runs, the migrator pre-flights the source: wal_level = 'logical', max_replication_slots > 0, max_wal_senders > 0, and extension availability (source extensions must be installable on the target). A misconfigured source fails fast with a clear error instead of stalling later inside CREATE_REPLICATION_SLOT.

At cutover, the migrator runs setval(...) on every sequence in the included schemas so the target picks up where the source left off — otherwise the first INSERT after cutover would collide with rows the subscription replicated. Disable with --no-sequence-sync if your target role lacks privileges for setval on those sequences.

Filtering

Use --exclude-schema and --exclude-table to omit large or transient objects from the dump. Both flags accept multiple values.

pg_dbmigrator --mode offline \
    --source ... --target ... \
    --exclude-schema audit \
    --exclude-table public.large_log

Verify

Compare per-table row counts between source and target.

In offline mode this runs automatically after restore; a mismatch is logged as a warning by default. Use --verify strict to make a mismatch a hard error (non-zero exit) for CI, or --verify off to skip the step.

In online mode verification is manual, not automatic. Online cutover stops the apply worker but does not freeze source writes, so an automatic row-count compare would almost always show a spurious mismatch — readiness is signalled by the lag heartbeat (CaughtUp), not by row counts. Once the operator has quiesced source writes and lag has drained, run verification explicitly with --mode verify (below).

Standalone (read-only, no dump/restore) — always exits non-zero on mismatch:

pg_dbmigrator --mode verify \
    --source 'postgres://user:pw@src/db' \
    --target 'postgres://user:pw@dst/db' \
    --schema app

Honours --schema / --table / --exclude-schema / --exclude-table so the verified object set matches what you migrated.

Other flags

Publication / replication resource lifecycle

The migrator fully manages the lifecycle of the replication resources it creates, so the operator does not need to run manual cleanup SQL after a successful cutover.

Auto-create publication: By default, the migrator checks whether the named publication (--publication, default pg_dbmigrator_pub) exists on the source. If it does not, the migrator creates it as FOR ALL TABLES (or scoped to --table / --schema if specified). Auto-created publications are tracked and dropped on the source after a successful cutover. Pre-existing publications are never dropped.

Slot cleanup: After cutover, the replication slot on the source is no longer needed. By default the migrator drops it. Pass --keep-slot if you need to inspect the slot post-migration or if another consumer shares it.

All cleanup steps are best-effort — failures are logged as warnings but do not abort the migration.

Cutover (online mode)

Cutover is driven by SIGINT (Ctrl+C). The CLI prints a periodic Lag heartbeat after the dump completes, so the operator has a continuous bytes-behind read-out:

INFO stage=Lag replication lag 4096 bytes (source LSN …, received LSN …, applied LSN …)
INFO stage=Lag replication lag 1024 bytes (…)
INFO stage=CaughtUp target caught up with source (lag 512 bytes) — ready for cutover

When the customer is satisfied with the lag, they press Ctrl+C once:

• The signal handler calls CutoverHandle::request().
• The streaming apply loop notices the request on its next poll, flushes the last LSN feedback to the source, emits a Cutover event, and returns.
• The migrator syncs sequences, cleans up replication resources (publication, slot, subscription), and returns with MigrationOutcome::cutover_triggered() == true. The process exits cleanly. Application traffic can now be switched to the target.
• A second Ctrl+C is treated as an abort (escape hatch — only use it if the graceful path is stuck).

Cutover is always operator-driven; --lag-threshold-bytes is purely advisory and only controls when the one-shot CaughtUp “ready for cutover” event fires.

For online migrations, hold on to migrator.cutover_handle() and call request() from your own signal handler / RPC endpoint when the operator is ready to cut over. See examples/online_migration for a complete program that wires Ctrl+C to the cutover handle.

Performance defaults

The CLI ships with sensible defaults tuned for migration speed. Override only when you have a specific reason.

Benchmark

See BENCHMARK.md for migration performance results across 10 GB -- 200 GB datasets (PG 16 -> PG 18, 8 parallel jobs, zstd compression).

Known limitations

• Apply runs through PostgreSQL's own logical replication apply worker (CREATE SUBSCRIPTION), not an in-process decoder, so type fidelity matches native logical replication. The flip side is that no per-column transform can be applied during replication.
• DDL changes are not migrated automatically — refresh the publication and restart the migration if the schema changes during the run.
• Extensions whose internal state cannot be re-created on the target (Azure-reserved extensions, pg_cron metadata, ...) may cause pg_restore to exit with code 1. Pass --allow-restore-errors to treat that as a non-fatal warning when user data was restored successfully.
• Sequence sync at cutover requires the target role to have permission to call setval() on the destination sequences. Per-sequence failures are logged but do not abort cutover — inspect the warnings and re-setval manually if needed, or pre-grant USAGE on the sequences before the migration.