Tech Notes

How to Upgrade PostgreSQL Without Losing Your Data

A practical guide to upgrading PostgreSQL major versions with minimal downtime, covering pg_upgrade, logical replication, and the gotchas nobody warns you about.

#PostgreSQL#Linux#Docker

There's a special kind of dread that comes with upgrading PostgreSQL. Your data is right there, humming along happily on version 14, and someone on the team drops the bomb: "We need to get to 16." And suddenly you're reading docs at 11 PM, wondering if pg_upgrade is going to eat your tables.

I've done this dance few times to have opinions. Here's what actually works, what to watch out for, and the approach I'd pick depending on how much downtime you can afford.

Why Major Version Upgrades Are a Big Deal

Minor version upgrades (14.1 → 14.2) are painless — the on-disk format stays the same, you just swap binaries and restart. Major version upgrades (14 → 16) are a different beast entirely. The internal data storage format changes between majors, so you can't just point the new binary at the old data directory and hope for the best.

You have three real options:

  1. pg_dump / pg_restore — the classic, slow, reliable approach
  2. pg_upgrade — the faster in-place(ish) upgrade
  3. Logical replication — the near-zero-downtime option

Let's break each one down.

Option 1: pg_dump and pg_restore

This is the "nobody ever got fired for choosing IBM" approach. Dump everything out, bring up the new version, load it back in.

# Dump the entire cluster
pg_dumpall -U postgres -h localhost -p 5432 > full_dump.sql

# Stop old server, start new one on the same port
sudo systemctl stop postgresql@14-main
sudo systemctl start postgresql@16-main

# Restore
psql -U postgres -f full_dump.sql

Or if you want parallelism and compression (you do), use the custom format per-database:

pg_dump -U postgres -Fc -j 4 -d myapp > myapp.dump
pg_restore -U postgres -Fc -j 4 -d myapp myapp.dump

The good: Dead simple. Works every time. You get a clean, freshly-organized database.

The bad: Your database is fully offline for the entire dump+restore cycle. For a 500 GB database, that can be hours. If you're running a SaaS product, this is probably not going to fly.

When to use it: Small databases (under ~50 GB), dev/staging environments, or situations where you can schedule a generous maintenance window.

Option 2: pg_upgrade (The Practical Middle Ground)

pg_upgrade is the tool most people should reach for first. It upgrades in place by transferring the data files directly — either by copying them or, better yet, by hard-linking them.

Here's the workflow:

# Install the new version alongside the old one
sudo apt install postgresql-16

# Stop both clusters
sudo systemctl stop postgresql@14-main
sudo systemctl stop postgresql@16-main

# Run pg_upgrade in check mode first (always do this)
sudo -u postgres /usr/lib/postgresql/16/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/14/main \
  --new-datadir=/var/lib/postgresql/16/main \
  --old-bindir=/usr/lib/postgresql/14/bin \
  --new-bindir=/usr/lib/postgresql/16/bin \
  --check

# If checks pass, do it for real with --link for speed
sudo -u postgres /usr/lib/postgresql/16/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/14/main \
  --new-datadir=/var/lib/postgresql/16/main \
  --old-bindir=/usr/lib/postgresql/14/bin \
  --new-bindir=/usr/lib/postgresql/16/bin \
  --link

The --link flag is key — instead of copying data files, it creates hard links. This turns what could be a 2-hour copy operation into a matter of seconds. The tradeoff is that you can't go back to the old cluster once you start the new one (the files are shared), so take a filesystem snapshot or backup first.

After the upgrade:

# Start the new cluster
sudo systemctl start postgresql@16-main

# Run the recommended analyze
sudo -u postgres /usr/lib/postgresql/16/bin/vacuumdb \
  --all --analyze-in-stages

That --analyze-in-stages step is important. Without it, the query planner has no statistics and your first few hours of queries will have terrible plans. It runs three passes with increasing accuracy so you get reasonable performance quickly.

The good: Fast (minutes, not hours). Reliable. Built into PostgreSQL itself.

The bad: Still requires downtime — both clusters need to be stopped. Usually 5–15 minutes for the upgrade itself, but plan for 30+ if you're cautious.

Watch out for:

  • Extensions that aren't available for the new version will block the upgrade. The --check flag catches this.
  • Custom postgresql.conf settings don't carry over. Diff your configs manually.
  • If you're using tablespaces, the paths need to be set up correctly on both sides.
Option 3: Logical Replication (Near-Zero Downtime)

If you genuinely can't afford more than a few seconds of downtime, logical replication is how you get there. The idea is simple: run old and new versions side by side, replicate data in real time, then cut over.

On the old server (publisher):

-- Make sure wal_level is set to 'logical' (requires restart if changed)
ALTER SYSTEM SET wal_level = 'logical';

-- Create a publication for all tables
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;

On the new server (subscriber):

# First, copy the schema (logical replication doesn't handle DDL)
pg_dump -U postgres -s -d myapp | psql -U postgres -d myapp -h new-server
-- Create a subscription pointing to the old server
CREATE SUBSCRIPTION upgrade_sub
  CONNECTION 'host=old-server dbname=myapp user=replicator password=secret'
  PUBLICATION upgrade_pub;

Once the initial data sync completes and the replication lag drops to zero, you cut over:

# Verify replication is caught up
psql -U postgres -c "SELECT * FROM pg_stat_subscription;"

# Point your application to the new server
# (update connection strings, DNS, pgbouncer config, etc.)

# On the new server, drop the subscription
psql -U postgres -c "DROP SUBSCRIPTION upgrade_sub;"

The good: Your app stays up throughout. You can validate the new server with real traffic before cutting over.

The bad: It's significantly more complex to set up. You need to handle:

  • Sequences (they don't replicate — you need to manually sync them before cutover)
  • Large objects (not supported by logical replication)
  • DDL changes during the migration window (you need to apply them to both sides)
  • Schema differences and extension compatibility

Syncing sequences is the thing that bites people most often:

-- On the old server, grab current sequence values
SELECT sequencename, last_value
FROM pg_sequences
WHERE schemaname = 'public';

-- On the new server, set them (with some headroom)
SELECT setval('users_id_seq', 158432 + 1000);
SELECT setval('orders_id_seq', 891204 + 1000);

Adding a buffer prevents ID collisions if a few writes sneak in during cutover.

If You're Running PostgreSQL in Containers

The concepts are the same, but the mechanics shift a bit. With Docker or Kubernetes, you're typically swapping image tags rather than installing packages side by side.

A common pattern with Docker Compose:

services:
  postgres-old:
    image: postgres:14
    volumes:
      - pgdata:/var/lib/postgresql/data
    ports:
      - "5432:5432"

  postgres-new:
    image: postgres:16
    volumes:
      - pgdata-new:/var/lib/postgresql/data
    ports:
      - "5433:5432"

You can then use pg_dump from one container to the other, or set up logical replication between them. For pg_upgrade, you'd typically need both sets of binaries in the same container, which means building a custom image — it works but it's fiddly.

In Kubernetes with operators like CloudNativePG or Zalando's postgres-operator, major upgrades usually involve spinning up a new cluster on the target version and using either pg_dump or logical replication to migrate. The operator handles the replica management, but the actual data migration is still on you.

My Honest Take

For most teams, pg_upgrade with --link is the sweet spot. It's fast, it's battle-tested, and the downtime is short enough that a maintenance window at 3 AM covers it. I've used it to upgrade ~hundred-gigabyte databases with under 20 minutes of total downtime.

Logical replication is powerful but it's also where I've seen the most "oh no" moments — forgotten sequences, schema drift, extensions that behave differently. Use it when you genuinely need near-zero downtime and you have the engineering bandwidth to test it thoroughly.

And whatever you do:

  1. Test the upgrade on a copy first. Always. Restore a backup to a throwaway server and run the full upgrade procedure. This catches 95% of surprises.
  2. Take a backup right before you start. Even if you took one yesterday.
  3. Don't skip the analyze step. Bad query plans after an upgrade are a classic "everything is on fire but nothing is technically broken" scenario.
  4. Read the release notes for every major version you're skipping. If you're jumping from 13 to 16, read the notes for 14, 15, and 16. Breaking changes stack up.

PostgreSQL upgrades aren't glamorous work, but they don't have to be scary either. Pick the right tool for your situation, test it first, and you'll be fine.