PostgreSQL Migration Strategies: pg_dump vs Logical Replication vs pg_upgrade

Moving a PostgreSQL database is never just a copy operation. The real question is what kind of interruption the product can tolerate while data moves, catches up, and switches to the new destination. A database that can accept a maintenance window has very different migration options from one that needs a near-live cutover.

The three migration patterns most teams compare are pg_dump and restore, logical replication, and pg_upgrade. All three are official PostgreSQL paths, but they solve different problems. One is best for portable exports and smaller moves, one is built for low-downtime data movement between running systems, and one is designed for major-version upgrades of the same cluster rather than cross-provider relocation.

If you are moving to managed PostgreSQL, the important decision is not which tool looks most sophisticated. It is which method matches your tolerance for downtime, your need to keep writes flowing, and the amount of operational control you have over both the source and target systems.

The core decision in one table

That table is the short version. The longer version is that each method preserves a different set of assumptions about the old environment.

When pg_dump is the right answer

pg_dump remains the most boring migration tool, and that is often a strength. PostgreSQL documents it as a utility for exporting a database consistently even while the database is being used concurrently, and it also notes that it does not block other readers or writers during the dump. That makes it useful when you need a standard export with predictable behavior and broad portability.

For migrations, pg_dump works best when the dataset is still manageable and the product can afford a clear maintenance window. The operational pattern is simple: dump the source database, restore into the new one, run checks, update connection strings, and reopen writes. For many startups, that simplicity beats a more advanced replication setup.

There are still important edges. PostgreSQL’s documentation says pg_dump only dumps a single database, so cluster-level objects such as roles and tablespaces need separate handling. The docs also show that parallel dump jobs require the directory format, which can reduce elapsed time but increases load on the source database. In other words, pg_dump scales farther than many teams assume, but it is still a copy-and-cutover workflow rather than a continuous sync mechanism.

If you are moving from another provider into ArmorDB and the database is modest in size, the built-in docs at /docs/migrate-from-neon and /docs/migrate-from-supabase reflect this same logic: use standard PostgreSQL tools first, avoid provider-specific lock-in assumptions, and keep the migration path easy to reason about.

When logical replication earns its complexity

Logical replication is the migration choice for teams that want the target database to catch up while the source stays live. PostgreSQL defines logical replication as replication of data objects and their changes based on replication identity, usually a primary key. That design matters because it operates at the row-change level instead of copying storage blocks byte for byte.

In practice, this means you can create a publication on the source, create a subscription on the target, let initial synchronization copy the published tables, and then allow ongoing changes to stream across until the lag is small enough for cutover. The cutover window becomes a final write pause, a last synchronization check, and a connection switch instead of a full reload from scratch.

The tradeoff is scope. Logical replication is excellent for keeping table data in sync, but teams should not mistake it for a perfect clone of the old environment. PostgreSQL’s publication docs explain that publications choose which DML operations are replicated, and the subscription path is fundamentally about changes flowing from a publisher to a subscriber. That is different from saying every cluster-level object, every administrative setting, and every operational habit comes across automatically.

This is why logical replication is strongest when the schema is already aligned, the tables have suitable replica identity, and the migration plan explicitly lists what must be recreated outside the row data path. It is the lowest-downtime option of the three, but it only stays low-risk when the team is deliberate about object coverage, lag monitoring, and cutover sequencing.

Why pg_upgrade is not a general migration tool

pg_upgrade is often mentioned in migration conversations because it is fast, but its speed applies to a narrower problem. PostgreSQL documents pg_upgrade as the utility for upgrading a server instance to a new major version using the existing data files. The docs also emphasize compatibility checks between old and new clusters and note that upgrade paths depend on supported source versions.

That makes pg_upgrade a strong fit when you control both the old and new PostgreSQL binaries and you are upgrading an existing deployment in place. It is not the normal answer for moving from one managed provider to another, or for changing environments where you do not control the host-level cluster layout. The tool is fast precisely because it assumes more continuity between the old and new environments than a cross-provider migration can usually guarantee.

The safest mental model is this: pg_upgrade is for major-version transitions of a cluster you already own operationally. If the project is instead moving data into a different managed PostgreSQL service, compare dump/restore against logical replication first.

A more practical comparison

For teams deciding under time pressure, the useful question is not which tool is “best.” It is which failure mode you would rather manage.

A safe migration workflow for managed PostgreSQL

Start by deciding whether the application can tolerate a maintenance window measured in restore time. If the answer is yes, a disciplined pg_dump migration is usually the shortest path to success. Take a fresh export, restore into the new database, verify extensions and roles, run application smoke tests, and then switch the app to the new connection string. On ArmorDB, that usually means validating the target credentials against /docs/connect before cutover day so connection details are not the last surprise.

If the answer is no, design the move around logical replication. Create the target schema first, establish publication and subscription cleanly, let the subscriber catch up, and rehearse the cutover as an operational event rather than a copy command. The winning pattern is to reduce the final pause to something the team can observe and reverse if needed.

If your real need is a PostgreSQL major-version upgrade inside infrastructure you manage directly, then pg_upgrade deserves attention. But that is a different decision from “how do I migrate this app into a managed PostgreSQL provider?” Teams get into trouble when they collapse those two questions into one.

Common mistakes that make migrations feel harder than they are

The first mistake is choosing logical replication only because it sounds more advanced. If a product can tolerate a short maintenance window and the dataset is still moderate, the extra moving parts may not buy much.

The second mistake is treating pg_dump like a magical full-cluster export. PostgreSQL is clear that it dumps one database unless you handle additional cluster-wide objects separately. Roles, privileges, extensions, and post-cutover validation still need attention.

The third mistake is reaching for pg_upgrade during a provider move. It is excellent at what it was built for, but its assumptions are too specific for many managed-database migrations.

The last mistake is skipping rehearsal. The first real migration should never be in production. Restore into staging, test the application, time the steps, and document the exact point where writes stop and restart. That rehearsal often matters more than the choice of tool.

Takeaway

Use pg_dump and restore when you want the simplest reliable move and can accept a clear cutover window. Use logical replication when the product needs low downtime and the team can handle a more careful setup. Use pg_upgrade when the task is actually an in-place PostgreSQL major-version upgrade, not a general relocation.

For most managed PostgreSQL migrations, the decision tree is smaller than it first appears: if downtime is acceptable, prefer dump and restore; if downtime is not acceptable, plan around logical replication; if the job is really a version upgrade on infrastructure you control, consider pg_upgrade.

Sources / further reading

• PostgreSQL pg_dump documentation: https://www.postgresql.org/docs/current/app-pgdump.html
• PostgreSQL logical replication overview: https://www.postgresql.org/docs/current/logical-replication.html
• PostgreSQL publication behavior: https://www.postgresql.org/docs/current/logical-replication-publication.html
• PostgreSQL CREATE SUBSCRIPTION reference: https://www.postgresql.org/docs/current/sql-createsubscription.html
• PostgreSQL pg_upgrade documentation: https://www.postgresql.org/docs/current/pgupgrade.html