DBAtlas · Database operations atlas

Version upgrade wizard

Patch or major, in place or by migration? Plan the upgrade step by step; each branch covers prechecks, method and post-upgrade work. Rehearse it here before touching production.

What kind of upgrade are you doing?

A patch/minor version (within the same major version, e.g., 16.2 -> 16.4, or a SQL Server CU) is low risk and is usually done in place. A major version (e.g., 15 -> 16) may involve data format changes and requires more careful planning.

SELECT * FROM v$version;
SELECT patch_id, action, status FROM dba_registry_sqlpatch ORDER BY action_time DESC;
  • Minor version / patch (same major version, CU)
  • Major version (the major version changes)
  • I don't know the difference; explain

Patch or major?

Patch: the major version stays the same (16.2 -> 16.4); the datafile format does not change, usually swap binaries + restart. Major: the major version changes (15 -> 16); the catalog/format may change, pg_upgrade / AutoUpgrade / dump-restore is required, and application compatibility must be tested.

  • Mine is a patch (same major version)
  • Mine is a major (the major version changes)

Are your backup and rollback plan ready?

Even though a patch is low risk, the golden rule of every upgrade is: full backup first (and a snapshot if possible). You need a point to return to if something goes wrong.

  • Yes, backup + rollback are ready
  • No, not yet

Backup + rollback plan first

Set up the safety net before starting the upgrade.

Apply the patch

A patch within the same major version. Applying it rolling across a cluster (starting with replicas) reduces downtime.

Major upgrade method

Two main paths: (1) In place: convert on the existing server (pg_upgrade, Oracle AutoUpgrade/DBUA, MySQL in-place). Fast but hard to roll back. (2) By migration: build a fresh server on the new version, move the data (with dump/restore or replication), then cut over. Safer, with minimal downtime.

  • Let me run a preparation/compatibility check first
  • In place
  • By migrating to a new server (minimal downtime)

Compatibility / readiness check

Before upgrading, collect the target version's warnings: removed (deprecated) features, invalid objects, compatibility level.

-- AutoUpgrade analyze mode: java -jar autoupgrade.jar -config cfg -mode analyze
  • Clean output, continue
  • There are warnings/incompatibilities

Fix the warnings first

Resolve every warning in the report BEFORE upgrading; otherwise the upgrade may fail midway or the application may break.

In-place major upgrade

Convert on the existing server. Fast, but rollback usually means restoring from backup, so a snapshot is a must.

Upgrade by migration (minimal downtime)

Build a fresh server on the new version, move the data, then switch over with a short cutover. The safest method; the old system stays intact.

Open in DBAtlas →