Omarchy

How-To: Dry-Run a PostgreSQL Major Upgrade Before You Commit

Leave a comment

PostgreSQL 19 is out, and the safest way to see if your cluster will upgrade cleanly is to ask pg_upgrade first, on a copy.

1. Make a copy you can break

Restore last night’s backup or snapshot to a spare VM. Never test against production data in place.

2. Install the new version next to the old one

Install the new server packages, then initdb a fresh, empty data directory for it with the same locale and encoding as the old one.

3. Run the check

pg_upgrade --check \
  -b /usr/lib/postgresql/OLD/bin -B /usr/lib/postgresql/NEW/bin \
  -d /var/lib/postgresql/OLD/main -D /var/lib/postgresql/NEW/main

Run it as the postgres user with both servers stopped. --check only looks; it changes nothing. Fix whatever it flags (extensions, data types, settings) and run it again until it’s clean.

4. Do the real run on the copy, then test your app

Afterward, run vacuumdb --all --analyze-in-stages to rebuild planner stats, then point a test copy of your app at it. If you relied on JIT, compare query timings, since 19 turns it off by default.

God Bless, and Happy hacking!

References & Further Reading

Subscribe (RSS) Follow How-Tos via RSS

Leave a note

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.