PostgreSQL 17 Released: What Changed and a Safe Upgrade Runbook

PostgreSQL 17.0 was released on 26 September 2024. The older supported branches stay at their August minor releases (16.4), with the next scheduled minor release due in November. This post summarises what is new for operators, then gives a repeatable upgrade runbook. It builds on the PostgreSQL 16 install from the earlier post.

Features that matter to operations

Feature	What it gives you
Incremental backup	pg_basebackup --incremental with summarize_wal = on; pg_combinebackup merges a chain into a restorable directory
Faster vacuum	A new memory structure for dead tuples cuts vacuum memory use and time on large tables
Logical replication improvements	pg_upgrade keeps logical replication slots when upgrading from 17 onward (it does not help a 16 to 17 move); failover slots; the pg_createsubscriber tool turns a physical standby into a logical replica
JSON_TABLE	Query JSON as a relational table in SQL
MERGE ... RETURNING and updatable views	Easier upsert-style code
Built-in collation provider	A C.UTF-8 style locale independent of the operating system libraries
Faster I/O paths	Better write throughput on high-concurrency workloads (results vary by workload)

Not every feature helps every system; benchmark your own queries. PostgreSQL 17 is supported for five years, until November 2029.

Choosing an upgrade method

flowchart TB
  S[Plan upgrade from 16 to 17] --> Q{Acceptable downtime?}
  Q -->|Minutes| PU[pg_upgrade with link mode]
  Q -->|Seconds| LR[Logical replication to a new 17 server<br/>then switch applications]
  Q -->|Hours fine| DP[pg_dump and restore<br/>simplest and cleanest]
  PU --> T[Rehearse on a restored backup first]
  LR --> T
  DP --> T
  T --> G[Go or no-go - tests pass and rollback ready]

pg_upgrade --link is the usual in-place route: downtime is minutes, because data files are hard-linked, not copied. The cost: once the new cluster starts, the old data directory cannot be used safely, so your rollback is a backup, not a quick switch.

Runbook: pg_upgrade on Ubuntu

# 1. Prerequisites: a fresh pgBackRest full backup and a verified restore test
sudo -u postgres pgbackrest --stanza=main --type=full backup

# 2. Install 17 side by side (PGDG repository from the previous post)
sudo apt install -y postgresql-17
sudo pg_dropcluster 17 main --stop     # drop the default empty 17 cluster

# 3. Create the target cluster with matching encoding and locale
sudo pg_createcluster 17 main --locale=en_US.UTF-8 -- --data-checksums

# 4. Dry run (no changes)
sudo -u postgres /usr/lib/postgresql/17/bin/pg_upgrade --check \
  -b /usr/lib/postgresql/16/bin -B /usr/lib/postgresql/17/bin \
  -d /etc/postgresql/16/main -D /etc/postgresql/17/main \
  -o ' -c config_file=/etc/postgresql/16/main/postgresql.conf' \
  -O ' -c config_file=/etc/postgresql/17/main/postgresql.conf'

The simpler wrapper on Debian and Ubuntu is sudo pg_upgradecluster 16 main (it also keeps configuration), and you can add -m upgrade --link. Whatever the tool, read the --check output carefully.

Before the maintenance window

• Install the 17 versions of every extension you use (PostGIS, pg_stat_statements, pgvector, …); missing extensions stop the upgrade.
• Compare postgresql.conf changes; some settings were removed or renamed, and pg_hba.conf must be copied across.
• Test the application against a 17 copy built from last night’s backup.

During the window

1. Stop applications and PgBouncer, then confirm no connections remain.
2. pg_upgrade --link (not --check).
3. Start 17, then run vacuumdb --all --analyze-in-stages, because statistics are not carried over.
4. Run a smoke test and check logs, then re-enable archive_command and take a new full backup with the 17 stanza (pgbackrest stanza-upgrade).
5. Re-create replicas with pg_basebackup; do not reuse the old 16 standby.

Rollback plan

• Before step 2 of the window: simply start 16 again; nothing has changed.
• After the new cluster has started and accepted writes: restore the pre-upgrade backup on a 16 server, then replay the archived WAL if you need to, and accept the data loss window since the upgrade, or replay from application logs. Link mode makes this the real constraint, so run a copy-mode upgrade (slower, more disk) if you need the old directory intact.
• Keep the 16 packages installed for a week, and remove the old cluster only after a good full backup of 17.

Version timing advice

Many teams wait for the first few minor releases (17.1 is scheduled for November) before moving production. Plan upgrades for versions approaching end of life: PostgreSQL 12 stops receiving fixes on 14 November 2024, and 13 follows in November 2025.