Path A — Safer dump/restore migration
- Back up roles/globals to capture users and permissions.
- Back up each database to portable files. Keep them safe.
- Install the new version side-by-side; initialize a fresh data directory.
- Restore roles and databases into the new cluster.
- Validate with application smoke tests and integrity checks.
- Switch clients to the new instance; keep the old offline for rollback until confident.
This path is simple and predictable, at the cost of longer downtime on large datasets.
Path B — In‑place upgrade with pg_upgrade (overview)
- Stop the old service. Ensure no processes hold locks on the data directory.
- Install the new version (binaries and tools) without removing the old.
- Run pg_upgrade checks to validate compatibility and directory paths.
- Perform the upgrade following the tool’s prompts and guidance.
- Analyze and reindex where recommended to rebuild statistics and indexes.
- Start the new service and test with your applications before decommissioning the old cluster.
pg_upgrade is much faster but requires strict adherence to its checklist. Always keep backups and a tested rollback plan.
Post‑migration checks
Connectivity
Connect locally and remotely, run critical queries, and verify performance against benchmarks.
Extensions
Upgrade or reinstall extensions as needed; confirm versions and availability.
Maintenance
Rebuild statistics and consider reindexing for large or critical tables when advised.
Troubleshooting — quick answers
Service still running
Stop the PostgreSQL Windows service and ensure no leftover processes are locking files before upgrading.
Permission denied
Run tools with sufficient privileges and ensure the service account can access both old and new data directories.
Incompatible jump
Some version jumps need intermediate steps. Review compatibility notes and consider dump/restore if pg_upgrade flags issues.
Related guides
Disclaimer: This is an unofficial resource. PostgreSQL is a registered trademark of the PostgreSQL Community Association of Canada. Links may include affiliate tracking.