Essential Checklist Before Migrating from PostgreSQL 14 to 17

Real-world cases with pg_upgrade · Logical Replication · Breaking Changes

PostgreSQL 14 reaches EOL in November 2026. There are still five months to go, and it's tempting to think "I can do it later," but with large-scale database migrations, preparing how to do it takes far longer than deciding when. I used to think it was just an apt upgrade, but when I actually went through it, it was a completely different story.

14→17 is a jump across three major versions (15, 16, 17) all at once. The breaking changes, removed features, and SQL behavior changes accumulated across each version all hit you simultaneously — and a checklist is exactly what saved me from days of troubleshooting when I faced all those accumulated changes at once. This post walks through a practical, usable checklist focused on pre-flight checks that apply regardless of which migration method you choose, all the way through strategies for minimizing downtime.

If you have a production PostgreSQL 14 database and you're the one responsible for upgrading it, this post should help. Especially if you manage search_path settings directly, use extensions, or have monitoring dashboards — the changes covered here may directly affect your code and operations.

Core Concepts

Why a PostgreSQL Major Upgrade Is Different from a Regular Package Update

A PostgreSQL major version upgrade is not a simple binary replacement. Because the internal format of the data directory itself differs between versions, a conversion process is always required to transform existing data into the new format.

Data Directory (PGDATA): The filesystem path where PostgreSQL stores all data — tables, indexes, transaction logs, and more. Because the directory structure itself changes during a major upgrade, existing data cannot be read with a simple package update.

WAL (Write-Ahead Log): A transaction log that records database changes before they are applied to the actual tables. It is central to crash recovery and replication, and is stored in the pg_wal folder inside the PGDATA directory.

There are three broad migration approaches, and the right choice depends on your service scale and acceptable downtime.

A Quick Overview of Breaking Changes Accumulated Between 14 and 17

Jumping three versions means changes from each version all apply at once. This is a common situation in practice, and organizing them by version makes pre-flight checks much more manageable.

The switch to scram-sha-256 as the default authentication method in PG16 deserves particular attention. If you're using older JDBC drivers or client libraries, you may encounter authentication errors after upgrading — it's worth checking your driver versions at the same time.

Notable Changes in PG17

PG17 significantly strengthens Logical Replication upgrade support. Previously, running pg_upgrade would destroy logical replication slots, requiring you to set up subscriptions from scratch after the upgrade. Starting with PG17, when the publisher is on PG17, logical replication slots and subscriber state are preserved. If the publisher is PG16 or below, slots are still not preserved, so if you have a replication setup, confirm the publisher version first.

Logical Replication Slot: A server-side pointer that tracks how far a specific subscriber has consumed the WAL. If a slot is lost, a full resync from scratch is required.

Practical Application

Pre-flight Checks: Queries You Should Run Before Upgrading

Regardless of which method you choose, I recommend running the queries below first. I learned this the hard way — I trusted the --check option and then hit search_path issues at runtime. This isn't well-covered in the official docs, but in my experience these queries catch problems the earliest.

-- 1. Check current version and installed extensions
SELECT version();
 
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;
 
-- 2. Check for configuration values removed in PG17
SHOW db_user_namespace;       -- removed in PG17
SHOW old_snapshot_threshold;  -- removed in PG17
 
-- 3. Find user-defined functions without an explicit search_path
--    Using such functions in expression indexes or Materialized Views can cause query failures after upgrade
SELECT proname, pronamespace::regnamespace
FROM pg_proc
WHERE prokind = 'f'
  AND pronamespace NOT IN (
    SELECT oid FROM pg_namespace
    WHERE nspname LIKE 'pg_%' OR nspname = 'information_schema'
  )
  AND NOT (proconfig @> ARRAY['search_path=pg_catalog']);
 
-- 4. Check column list of pg_stat_bgwriter
--    buffers_backend_fsync was removed in PG17 — update any monitoring queries that reference it
SELECT attname
FROM pg_attribute
WHERE attrelid = 'pg_stat_bgwriter'::regclass
  AND attnum > 0
  AND NOT attisdropped;

Extension issues in PG17 fall into three distinct categories. Grouping them all under "problematic" causes confusion, so it helps to keep them separate.

In-place Upgrade with pg_upgrade

If your environment can tolerate tens of minutes of downtime, pg_upgrade is the most practical choice. Using --link mode avoids copying files by connecting them with hard links, which dramatically speeds things up. However, because of how hard links work, a failure can corrupt the old cluster's files — always take a full backup first.

# 1. Install PG17 binaries (leave the existing PG14 cluster in place)
sudo apt install postgresql-17
 
# 2. Dry run: check compatibility only, no actual changes
/usr/lib/postgresql/17/bin/pg_upgrade \
  --old-datadir /var/lib/postgresql/14/main \
  --new-datadir /var/lib/postgresql/17/main \
  --old-bindir  /usr/lib/postgresql/14/bin \
  --new-bindir  /usr/lib/postgresql/17/bin \
  --check
 
# 3. Actual upgrade (--link: completes in minutes via hard links)
#    Adjust --jobs to match your server's CPU core count (e.g. --jobs=2 for a 2-core server)
/usr/lib/postgresql/17/bin/pg_upgrade \
  --old-datadir /var/lib/postgresql/14/main \
  --new-datadir /var/lib/postgresql/17/main \
  --old-bindir  /usr/lib/postgresql/14/bin \
  --new-bindir  /usr/lib/postgresql/17/bin \
  --link \
  --jobs=8
 
# 4. Collect statistics immediately after upgrade (required — skipping this can break query plans)
./analyze_new_cluster.sh
 
# 5. After thorough validation, clean up the old cluster
./delete_old_cluster.sh

Even if the --check step reports no errors, search_path-related issues can still surface at runtime. It's strongly recommended to run through the full flow once in a staging environment.

--link mode (hard-link upgrade): Instead of copying files, this mode connects existing PG14 data files to the PG17 directory via hard links. It cuts upgrade time to minutes for large databases, but because the source and link share the same inode, any problem during the upgrade can also affect the old cluster files.

Near-Zero Downtime Upgrade via Logical Replication

When production downtime is almost entirely unacceptable, the practical approach is to spin up a new PG17 server, synchronize data via logical replication, and then switch the application connection over.

[PG14 Primary]
      │
      │  Logical Replication
      ▼
[PG17 New Server]  ← after replication is in sync
      │
      │  Switch app connection (seconds of downtime)
      │  Shut down PG14
      ▼
[PG17 Primary]

If setting this up manually feels complex, open-source CLI tools like pg_easy_replicate can help. Our team used it in practice and found that setting up slots and subscriptions really was as simple as a few commands. The one weak spot was real-time visibility into initial replication progress — for large tables, we had to monitor progress manually via pg_replication_slots. On AWS RDS, the Blue/Green Deployments feature serves the same purpose.

pg_easy_replicate: An open-source tool that abstracts Logical Replication-based zero-downtime upgrades into just a few CLI commands. It automates the complex management of slots and subscriptions.

Pros and Cons

Advantages

Disadvantages and Caveats

The Most Common Mistakes in Practice

1. Thinking that passing --check means you're done — pg_upgrade --check only verifies data directory structure compatibility. Runtime failures like search_path-related function errors or monitoring query failures caused by the removed pg_stat_bgwriter column only surface at runtime. This isn't well-documented officially, and it's the most disorienting thing to run into in practice.

2. Forgetting to run analyze_new_cluster.sh, or putting it off — Immediately after an upgrade, there are no statistics, so the optimizer can choose completely wrong index scans. Running it before traffic picks up is the way to go.

3. Checking extension compatibility by version number alone — Extensions like postgis can produce function resolution errors even with a matching version if the search_path server parameter isn't updated alongside it. Check extension release notes together with the server parameter change history.

Closing Thoughts

The biggest risk in a PostgreSQL 14→17 migration isn't the version gap itself — it's search_path issues and extension compatibility that slip through without pre-flight checks. The tooling is mature enough, and with managed services, upgrades can be done in a few console clicks. Having this checklist ready in advance is enough to avoid being up at 2 a.m. on production upgrade day, wondering whether to roll back.

Three steps you can start right now:

1. Start by pulling your list of installed extensions. Run SELECT name, installed_version FROM pg_available_extensions WHERE installed_version IS NOT NULL; to check for items like adminpack or age that are removed or have incomplete compatibility in PG17.

2. Run pg_upgrade --check in a staging environment first. A dry run before touching production lets you discover compatibility issues early. Running the search_path inspection query introduced above at the same time can significantly reduce runtime errors.

3. If your monitoring dashboards reference pg_stat_bgwriter.buffers_backend_fsync, fix them now. If Grafana or Prometheus is using this column, your dashboards will start throwing errors after the PG17 upgrade. Fixing dashboard errors ahead of upgrade day removes one more source of confusion.

References

• PostgreSQL Official Docs: pg_upgrade
• PostgreSQL Official Docs: Upgrading a Cluster
• PostgreSQL 17 Release Notes (Official)
• PostgreSQL 17 Official Announcement
• GitHub: PostgreSQL 14→17 Upgrade Guide | alimoradimllm
• Data Egret: PostgreSQL Major Upgrades Ultimate Guide (2026)
• Techbuddies: 7 Best Practices for pg_upgrade
• pgEdge: Zero Downtime Major Version Postgres Upgrades
• pganalyze: Zero downtime Postgres upgrades with logical replication
• AWS RDS: PostgreSQL Major Version Upgrade Guide
• Azure: PostgreSQL Major Version Upgrade Concepts
• PostgreSQL Logical Replication Upgrade Official Docs