PostgreSQL 18 How to Upgrade: pg_upgrade, Dump/Restore, Logical Replication

Q: What about extensions?

Extensions need to be installed for the new version. `pg_upgrade` checks extension compatibility. The team that uses pg_stat_statements, PostGIS, etc. needs to install them in v18 too.

PostgreSQL 18 upgrade has three approaches per the Postgres docs: pg_upgrade for fast in-place migration, pg_dumpall/pg_dump for clean migration, logical replication for zero-downtime. The team that picks based on data size and downtime tolerance has the right approach. Major version upgrades (17 -> 18) require one of these methods; minor upgrades (18.0 -> 18.1) are just package updates.

The three methods
pg_upgrade (fast in-place)
pg_dumpall (clean migration)
Logical replication (zero-downtime)
Blue-green for safer cutover
Verify after upgrade
Rollback plan
FAQ

The three methods

Method	Downtime	Data size	Risk
pg_upgrade	Minutes	Any	Low (in-place)
pg_dumpall/restore	Hours for large DBs	Any	Lowest (clean)
Logical replication	Seconds (cutover)	Any	Medium (replication lag)

The team that picks by use case has the right answer.

pg_upgrade (fast in-place)

Per the pg_upgrade docs:

# Install PostgreSQL 18 alongside 17
sudo apt install postgresql-18

# Stop both
sudo systemctl stop postgresql

# Run pg_upgrade
sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade \
  -b /usr/lib/postgresql/17/bin \
  -B /usr/lib/postgresql/18/bin \
  -d /var/lib/postgresql/17/main \
  -D /var/lib/postgresql/18/main \
  -o ' -c config_file=/etc/postgresql/17/main/postgresql.conf' \
  -O ' -c config_file=/etc/postgresql/18/main/postgresql.conf'

The team that uses pg_upgrade has in-place upgrade in minutes. The team that has terabyte databases can use —link for hard links (faster, but no rollback without backup).

pg_dumpall (clean migration)

Dump from old, restore to new:

# Dump from 17
sudo -u postgres pg_dumpall > /backup/full-17.sql

# Initialize 18 cluster
sudo systemctl stop postgresql-17
sudo /usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/main
sudo systemctl start postgresql-18

# Restore
sudo -u postgres psql -d postgres -f /backup/full-17.sql

The team that uses pg_dumpall has the cleanest migration (works across any version, no in-place upgrade risks).

Logical replication (zero-downtime)

Set up logical replication from 17 to 18:

-- On 17 (publisher)
CREATE PUBLICATION alltables FOR ALL TABLES;

-- On 18 (subscriber)
CREATE SUBSCRIPTION pub17 CONNECTION 'host=pg17 port=5432 user=repl password=xxx' PUBLICATION alltables;

Wait for sync, then cutover:

# Stop writes to 17
sudo systemctl stop postgresql-17
# Wait for replication lag to hit 0
SELECT * FROM pg_stat_subscription;
# Switch app connection strings to 18
# Done

The team that needs zero-downtime uses logical replication.

Blue-green for safer cutover

The dbi-services guide shows a blue-green pattern: set up 18 as a physical replica, promote at cutover.

# 18 starts as a streaming replica of 17
# At cutover:
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/18/main
# 18 is now writable, 17 is read-only

The team that has blue-green has minimal cutover window.

Verify after upgrade

-- Version check
SELECT version();

-- Object count check
SELECT count(*) FROM pg_class WHERE relkind = 'r';

-- Statistics update
ANALYZE;

-- Test queries
SELECT * FROM critical_table LIMIT 10;

The team that verifies has confidence the upgrade worked.

Rollback plan

Always have a rollback before upgrading:

pg_upgrade with --link: backup before upgrade (or copy data dir).
pg_dumpall: dump before upgrade (you have the full data).
Logical replication: keep 17 running until 18 is verified.

The team that has rollback tested has a recovery path if the upgrade fails.

FAQ

Is upgrading from 17 to 18 a major upgrade?

Yes - 16 -> 17, 17 -> 18 are major version upgrades requiring one of the methods. 18.0 -> 18.1 is minor (just package update).

How long does pg_upgrade take?

Minutes for small/medium DBs, up to hours for terabyte databases with —link.

Can I use logical replication from 17 to 18?

Yes - logical replication works across major versions. The team that needs zero-downtime uses this.

What about extensions?

Extensions need to be installed for the new version. pg_upgrade checks extension compatibility. The team that uses pg_stat_statements, PostGIS, etc. needs to install them in v18 too.

Should I do a major upgrade in production?

Yes - but in a maintenance window with rollback plan. The team that does the upgrade in dev first has fewer surprises in production.

If you are sizing the infrastructure for the kind of project this post covers, the RunxBuild hosting calculator is the right place to model the line items. The compute, the memory, the storage, the bandwidth, the database - each one is a separate number, and the team’s mental model for the platform is the sum of those numbers. The RunxBuild dashboard is where the team sees the actual usage in one place.

Useful related references: