Migration methods: when to use each

Each method has a different risk profile, downtime and operational complexity.

  • pg_upgrade: the fastest method. Converts cluster files in-place. With --link it uses hard links and avoids copying data. Requires full maintenance window (minutes to a few hours). Most used for planned migrations.
  • Dump/restore (pg_dump + pg_restore): the simplest conceptually but with greater downtime proportional to database size. Useful for small databases or when there are significant schema changes.
  • Logical replication (zero-downtime): allows migrating with minimal downtime (seconds) by replicating data to the new PG16 server while PG14 remains in production. The most complex method and requires all tables to be compatible with logical replication.

Important changes between PostgreSQL 14 and 16

  • PG15: MERGE SQL added, changes to default permissions on public schema, pg_wal_summary
  • PG16: new pg_stat_io view, improvements in logical replication (subscription from standby, column and row filter), vacuum_buffer_usage_limit, improvements in VACUUM parallelism
  • Removed parameter: wal_keep_segments was replaced by wal_keep_size in PG13. If you have that parameter in your postgresql.conf, PG16 will not start
  • Permissions in public schema (PG15+): CREATE permissions are no longer granted by default to the PUBLIC role. Applications that create tables in the public schema may fail

Pre-migration checklist

  • 1. Verify installed extensions and their compatibility with PG16
  • 2. Run pg_upgrade in --check mode first This detects incompatibilities without modifying anything. It is mandatory before running the actual migration.
  • 3. List and review invalid objects Invalid objects can cause post-migration errors in dependent functions.
  • 4. Review deprecated or removed parameters in postgresql.conf
  • 5. Verify that the application driver supports PG16 Psycopg2 >= 2.9.3, JDBC PostgreSQL >= 42.5, libpq >= 16 are compatible. Very old drivers may fail.
  • 6. Have a complete and verified backup of PG14 The backup is the ultimate rollback plan. It must be done and tested before starting.
  • 7. Agree on a maintenance window and communicate to stakeholders Define: start, expected end, rollback criterion (if it doesn't work within X minutes, revert), and who has authority to approve the rollback.
  • 8. Prepare the documented step-by-step rollback plan The rollback plan with pg_upgrade is to start the original PG14 (which was not deleted). With dump/restore, restore the prior dump. With logical replication, redirect traffic to the PG14 primary.
  • 9. Validate the new PG16 server before migration Install the same extensions, verify the operating system has the necessary packages, and confirm sufficient disk space (at least 1.5x the size of the PG14 cluster).
  • 10. Run the migration with pg_upgrade (real mode)
  • 11. Post-migration validation: object count Compare the number of tables, functions, indexes and sequences before and after.
  • 12. Run ANALYZE on all databases after pg_upgrade pg_upgrade does not transfer planner statistics. Without ANALYZE, the planner will make suboptimal decisions.
TERMINAL 
# Checks compatibility before migrating (does not run the upgrade)
pg_upgrade --check   -b /usr/lib/postgresql/14/bin -B /usr/lib/postgresql/16/bin   -d /var/lib/postgresql/14/main -D /var/lib/postgresql/16/main

When to escalate or not run the migration without expert support

  • The database has extensions with unmaintained or internally developed C code
  • The system has no available maintenance window (24/7 operation with no downtime tolerance)
  • There is no valid and tested backup prior to the migration
  • The application has no test suite to validate post-migration behavior
  • pg_upgrade --check reports undocumented incompatibilities
  • There are streaming replicas or logical subscribers that must be migrated in a coordinated manner

Frequently asked questions

How long does a migration with pg_upgrade --link take?

With --link, the migration uses hard links instead of copying files, so the time is almost independent of database size: generally between 5 and 30 minutes. Most of the time is the object verification process and the subsequent ANALYZE run. Without --link, the time is proportional to database size.

Is pg_upgrade safe for production?

Yes, when run after a --check verification, with a complete prior backup and in an agreed maintenance window. It has years of production use and is the method recommended by the PostgreSQL community for major version migrations. The main risk is not having a documented rollback plan if a problem is discovered post-migration.

What extensions can cause problems when migrating to PG16?

The most frequently incompatible extensions are old versions of PostGIS (requires a specific version for each PG version), TimescaleDB (has its own migration process), pg_partman and internally developed extensions with C code that references internal PostgreSQL structures. Always check the compatibility matrix in each extension's official repository before starting.

Do I also need to migrate streaming replicas?

With pg_upgrade, yes: streaming replicas are incompatible between major versions. You must create new PG16 replicas with pg_basebackup from the new PG16 primary. With logical replication (zero-downtime), you can temporarily maintain PG14 read replicas during the transition, but they must eventually migrate. Coordinating replica migration is part of the overall plan and not an optional step.

Related resources