Right, so you’ve decided to upgrade your PostgreSQL installation. Good for you. New features, security patches, all that good stuff. But you’re not about to dump your data and reload it from scratch like some kind of barbarian. You’re going to use pg_upgrade, the tool designed for exactly this job. It’s brilliant when it works, which is most of the time, but it has the personality of a meticulous archivist who will absolutely yell at you for using the wrong kind of paperclip. Let’s get this right.

The core concept of pg_upgrade is beautifully simple: it creates a new set of database files for the new version that point to the old data files. It’s essentially a massive, incredibly careful file system hard link operation (or copy, if you’re paranoid). This is why it’s fast. It’s not moving your actual data; it’s just building a new library catalog system for the same books.

Before You Even Think About Running It

This isn’t a “run and see what happens” command. This is a “measure twice, cut once” situation. Here’s your pre-flight checklist. Do not skip this. I will know.

  1. Check Version Compatibility: pg_upgrade isn’t magic. It only works between certain versions. You can’t jump from 9.6 to 16 directly; you’d need to hop through intermediate versions. Always check the PostgreSQL manual for your specific versions first. This is the one thing that is utterly unforgiving.

  2. Install the New Version: You need the new version of PostgreSQL installed alongside the old one. They must be installed with the same --prefix configuration or be aware of each other. On a package-managed system (apt, yum), this usually Just Works. Compile it yourself? You’d better have put them in, say, /usr/local/pgsql-14 and /usr/local/pgsql-15.

  3. Stop Both Servers: This should be obvious, but I’ve seen it happen. The old cluster and the new cluster must be completely shut down. No pg_ctl restart, a full pg_ctl stop.

    # Stop the old server (usually version-specific scripts exist)
sudo systemctl stop postgresql@14-main
# Or the old-fashioned way
/usr/lib/postgresql/14/bin/pg_ctl -D /var/lib/postgresql/14/main stop

# The new data directory should be empty, but ensure its service is stopped too.
sudo systemctl stop postgresql@15-main

  4. The Most Important Command: –check: The designers, in a rare moment of clarity, gave us a dry-run option. Use it. It will save you from yourself.

    sudo -u postgres /usr/lib/postgresql/15/bin/pg_upgrade \
  -b /usr/lib/postgresql/14/bin \   # Path to OLD binaries
  -B /usr/lib/postgresql/15/bin \   # Path to NEW binaries
  -d /var/lib/postgresql/14/main \  # OLD data directory
  -D /var/lib/postgresql/15/main \  # NEW data directory
  --check

    If this spits out any errors (not warnings, errors), stop and fix them. Common issues include mismatched byte ordering (if you’re moving between architectures, which is a whole other nightmare), extension versions, or leftover pg_stat files.

The Main Event (And The Flags That Matter)

Assuming --check gave you the all-clear, you run the same command again, without the --check flag. But wait! Let’s talk about the two most important runtime flags:

  • -k (or --link): This is the secret sauce. Instead of copying all your data files (which takes forever and twice the disk space), it uses hard links. This is incredibly fast because it’s just creating new directory entries pointing to the same data blocks on disk. The trade-off? You can’t just roll back by deleting the new directory; the old and new are now linked together. If you need that rollback option, don’t use -k.
  • -c (or --check): Oh, wait, we already did that. See how important it is?

Here’s the real deal command, with linking enabled:

sudo -u postgres /usr/lib/postgresql/15/bin/pg_upgrade \
  -b /usr/lib/postgresql/14/bin \
  -B /usr/lib/postgresql/15/bin \
  -d /var/lib/postgresql/14/main \
  -D /var/lib/postgresql/15/main \
  -k

Go get a coffee. With -k, this should be surprisingly quick, proportional only to the number of files, not their size.

After the Party: What Now?

It worked! Hooray! But you’re not done. The pg_upgrade output will give you a list of tasks. It will look something like this:

--------------------------------------------------------------------
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
You will need to run:
    /usr/lib/postgresql/15/bin/vacuumdb --all --analyze-in-stages

... and a few other things you should probably do.
--------------------------------------------------------------------

  1. Update Statistics: This is non-optional. Your new cluster has no idea about your data distribution. Run what it tells you to run. This is what makes your queries fast again.

    sudo -u postgres /usr/lib/postgresql/15/bin/vacuumdb --all --analyze-in-stages

  2. Update Your Configuration: Your old postgresql.conf and pg_hba.conf have been copied over, but they might have old settings. The new version might have new parameters or deprecated old ones. You need to manually review and merge any changes from the new default postgresql.conf. This is the most tedious part. Don’t just blindly start the new server with the old config.

  3. Test Everything: Before you switch your applications over, start the new server and connect to it. Run some queries. Test your important functions. Ensure your extensions are available and at the correct version.

  4. Delete the Old Cluster (Eventually): Once you’{{< bibleref “Revelation 100 ” >}}% certain the new cluster is running perfectly and you have a verified backup of the new cluster, then you can delete the old data directory to free up space. If you used -k, remember that deleting the old files will break the new cluster because they are hard-linked. You must first run the delete_old_cluster.sh script that pg_upgrade creates in the new data directory. It knows how to safely remove the old files without harming the new ones.