PostgreSQL upgrade basics

Topic: Databases core

Upgrade PostgreSQL by using pg_dump and pg_restore (logical) or pg_upgrade (in-place). Logical is safer and works across major versions; pg_upgrade is faster but same major. Use this when moving to a new major version.

Intent: How-to

Quick answer

  • Logical upgrade: pg_dumpall or pg_dump from old cluster; install new PostgreSQL; init new cluster; restore. No catalog compatibility issues; can fix encoding or filter data.
  • pg_upgrade links or copies data files and upgrades catalogs. Same machine or copy data; run pg_upgrade; restart new server. Check extension and compatibility first.
  • Test restore or pg_upgrade on a copy. Plan downtime; back up before upgrade. Run ANALYZE after upgrade. Update connection strings and apps.

Prerequisites

Steps

  1. Backup and prepare

    Full backup of current cluster. Install new PostgreSQL version. Create new cluster (initdb). Document extensions and config to reapply.

  2. Logical upgrade

    pg_dumpall -f backup.sql from old. Stop old server. Start new; psql -f backup.sql. Or pg_dump/pg_restore per database. Run ANALYZE.

  3. Or pg_upgrade

    Stop both clusters. Run pg_upgrade -b oldbindir -B newbindir -d olddata -D newdata. Start new server; run generated script to update extensions. ANALYZE.

  4. Verify and cutover

    Smoke test apps; update config and connection strings. Decommission old cluster after validation.

Summary

Upgrade via logical dump/restore or pg_upgrade. Backup first; test on a copy; verify and cut over.

Prerequisites

Steps

Step 1: Backup and prepare

Back up the current cluster; install and init the new version; note extensions and config.

Step 2: Logical upgrade

Dump from old cluster; restore into new; run ANALYZE.

Step 3: Or pg_upgrade

Run pg_upgrade with old and new bin/data dirs; run any generated scripts; start new server; ANALYZE.

Step 4: Verify and cutover

Test applications; switch config to new cluster; retire old after validation.

Verification

  • New cluster serves traffic; version and extensions correct; no errors in logs.

Troubleshooting

Restore fails — Check encoding and extensions; fix and re-run. pg_upgrade fails — Resolve extension or catalog issues; consider logical upgrade.

Next steps

Continue to