How to Upgrade PostgreSQL Without Losing Data

How do you move a PostgreSQL database to a new major version without taking it down for hours? There are three ways to do it. Here is what usually breaks with each of them, and which one to choose first.

Why major version upgrades are different

Minor version upgrades (14.1 to 14.2) are easy. The on-disk format stays the same, so you swap binaries and restart. Major version upgrades (14 to 16) change the internal storage format. This means you can't just point the new binary at the old data directory.

Three tools actually do this. pg_dump / pg_restore is the old standard, slow but very reliable. pg_upgrade is the faster in-place upgrade, and most people use it when the database becomes too big for a dump and restore window. Logical replication is the heavy option when you need near-zero downtime.

Dumping and restoring with pg_dump

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

For parallelism and compression, 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

It is simple, it works every time, and in the end you get a clean, freshly organized database. The problem is the downtime. The database is fully offline for the whole dump and restore, which on a 500 GB database can take hours. It is fine for small databases (under 50 GB or so), for dev and staging, or when there is a long maintenance window.

pg_upgrade, in place

pg_upgrade upgrades in place. It moves the data files directly, either by copying them or by hard-linking them.

# 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, run 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 creates hard links instead of copying data files. This turns a two-hour copy into a few seconds. The tradeoff is that once you start the new cluster you can't go back to the old one, because both share the same files. Take a filesystem snapshot or backup before you start.

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

Don't skip the --analyze-in-stages step. Right after the upgrade the planner has no statistics, so your queries get bad plans until it builds them. It runs three passes with increasing accuracy. The first pass is rough but quick, so the database is usable again sooner.

This is the fast one. Minutes instead of hours, well tested, and it ships with PostgreSQL. You do pay in downtime, since both clusters have to be stopped while it runs. Usually that's 5 to 15 minutes, but I plan for 30 or more, so I do not need to hurry.

A few things can cause problems. Extensions that aren't available for the new version will block the upgrade, but the --check flag shows this before the start. Custom postgresql.conf settings don't carry over, so diff the configs by hand. And if you use tablespaces, the paths have to be set up correctly on both sides.

Logical replication, for near-zero downtime

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 does not 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 replication lag drops to zero, 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 application stays up the whole time, and you can test the new server before you switch to it. That is the whole reason to do it. Everything else about logical replication needs work from you. Sequences do not replicate, so you sync them by hand before the cutover. Large objects are not supported at all. Any DDL during the migration you must apply on both sides. And the schema and extension versions must stay the same on the two servers.

Syncing sequences before cutover:

-- 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 writes happen during cutover.

Doing this with containers

The ideas are the same. You just swap image tags instead of installing packages side by side.

A common Docker Compose pattern:

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"

Use pg_dump from one container to the other, or set up logical replication between them. pg_upgrade is harder here, because it needs both sets of binaries in the same container. So you end up building a custom image.

In Kubernetes with operators like CloudNativePG or Zalando's postgres-operator, a major upgrade usually means starting a new cluster on the target version and moving the data over with pg_dump or logical replication.

What I choose

For most teams pg_upgrade --link is the right choice. It's fast, well tested, and the downtime fits inside a normal maintenance window. I've run it on a fairly large database and finished well inside half an hour.

Logical replication can do more, but with it I saw the most problems. A forgotten sequence. Schema drift. An extension that's a version behind on the new side. Use it only when you really need near-zero downtime and have time to test the whole process properly.

Whatever you pick, test it on a copy first. Restore a recent backup to a spare server and run the thing end to end. The step that breaks is rarely the one you were worried about. Then take a fresh backup right before the real run.

And read more release notes than you think you need to. If you're jumping several major versions at once, read the notes for every version in between, not just the target. A breaking change can be in a version which you skipped.