Upgrades and Version Management

Snowflake for Developers/Postgres/Upgrades and Version Management

On this page

Upgrades and Version Management

Postgres

Elizabeth Christensen

Postgres Version Policy

Postgres runs on a predictable schedule: one new major version a year, and five years of support for each one. Here's where things stand as of late October 2026:

Version	Released	End of Life	Notes
PG 13	Sep 2020	Nov 13, 2025	EOL. No more fixes, including security fixes. Upgrade now.
PG 14	Sep 2021	Nov 12, 2026	Final minor release in November 2026. Plan your upgrade.
PG 15	Oct 2022	Nov 2027
PG 16	Sep 2023	Nov 2028
PG 17	Sep 2024	Nov 2029
PG 18	Sep 25, 2025	Nov 2030	Current stable release
PG 19	Not yet released	~Nov 2031 (expected)	RC1 released Oct 15, 2026. GA expected late 2026.

Supported versions right now are 14 through 18. Check the official versioning policy for exact dates.

Minor releases (16.3 to 16.4, say) come out quarterly and contain only bug fixes and security patches, never new features. Major releases (16 to 17) land every fall and can include new syntax, configuration changes, and internal catalog changes.

Always read the release notes. Every single time, even for minor releases. The Postgres community is very good about documenting what changed and why, and minor releases sometimes include "if you're affected by X, reindex Y" instructions you really don't want to miss.

Check your current version:

SELECT version();

Check when you last upgraded:

-- pg_controldata shows the catalog version, which changes with major upgrades
-- Run from the command line:
pg_controldata /var/lib/postgresql/data | grep "Catalog version"

Minor Version Upgrades

Minor version upgrades are low-risk. Apply them promptly, since they fix bugs and security vulnerabilities.

Using Snowflake Postgres? Minor version patches are applied for you. The instructions below are for local and self-managed environments.

Linux (apt)

# Update package list and upgrade PostgreSQL
sudo apt update
sudo apt install --only-upgrade postgresql-16

# Restart to pick up new binaries
sudo systemctl restart postgresql

Linux (yum/dnf)

sudo dnf update postgresql16-server postgresql16
sudo systemctl restart postgresql-16

Docker Compose

For Docker environments, the process is similarly simple:

# Stop the current container
docker compose down

# Pull the latest minor version tag
docker compose pull

# Start back up with the updated image
docker compose up -d

Make sure your docker-compose.yml references a tag like postgres:16 (which floats to the latest minor) rather than pinning to a specific patch like postgres:16.3.

Post-Upgrade Verification

After any minor upgrade, verify things are healthy:

-- Confirm the new version
SELECT version();

-- Check for any issues in the log
-- (from the command line)
tail -50 /var/log/postgresql/postgresql-16-main.log

Minor upgrades don't require ANALYZE, schema changes, or application changes. But say it with us: read the release notes before applying them.

Major Version Upgrades

Major version upgrades require more planning. You have three main approaches, all applicable to local development and self-managed environments.

Using Snowflake Postgres? Major version upgrades are initiated with a single command. See Snowflake Postgres version upgrades for the managed upgrade workflow.

Option 1: pg_dump / pg_restore (Logical Dump)

The simplest approach: dump the old database, restore into the new one. Best for smaller databases or when you want a fresh start.

# Dump from the old cluster
pg_dump -U postgres -Fc -f /tmp/mydb_backup.dump mydb

# Restore into the new cluster (already initialized)
pg_restore -U postgres -d mydb -Fc /tmp/mydb_backup.dump

Downside: Requires downtime proportional to database size. A 500 GB database might take hours.

Option 2: pg_upgrade (In-Place)

The most common approach for large databases. pg_upgrade builds the new cluster's system catalogs and reuses your existing data files, so it's much faster than a logical dump. With --link (hard links) it doesn't copy data files at all, and Postgres 18 added --swap, which moves the data directories into place and is usually the fastest option. Both mean you can't go back to the old cluster once the new one has started, so take a backup first.

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

# Run pg_upgrade (as the postgres user)
su - postgres
/usr/lib/postgresql/17/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/16/main \
  --new-datadir=/var/lib/postgresql/17/main \
  --old-bindir=/usr/lib/postgresql/16/bin \
  --new-bindir=/usr/lib/postgresql/17/bin

Always do a dry run first with --check:

/usr/lib/postgresql/17/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/16/main \
  --new-datadir=/var/lib/postgresql/17/main \
  --old-bindir=/usr/lib/postgresql/16/bin \
  --new-bindir=/usr/lib/postgresql/17/bin \
  --check

The --check flag validates everything without making changes. It catches incompatible extensions, encoding issues, and other blockers before you commit to the upgrade.

After pg_upgrade completes, start the new cluster and refresh optimizer statistics:

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

# Refresh optimizer statistics so the planner has good data
/usr/lib/postgresql/17/bin/vacuumdb --all --analyze-in-stages

The --analyze-in-stages flag runs ANALYZE in three passes with increasing sample sizes, so the optimizer gets usable statistics quickly instead of waiting for a full analyze of every table.

This step got a lot less painful in Postgres 18. When the target is Postgres 18 or later, pg_upgrade carries over most planner statistics, so you don't start with an empty pg_statistic and terrible plans for the first hour. Extended statistics (from CREATE STATISTICS) still aren't carried over. Run vacuumdb --all --analyze-in-stages --missing-stats-only to fill in only what's missing, then a regular vacuumdb --all --analyze-only when you have time.

Option 3: Logical Replication (Zero-Downtime)

If you can't take a maintenance window, logical replication lets you set up the new version as a subscriber, let it catch up, then switch over with minimal downtime (seconds, not hours).

This approach is covered in detail in our Replication Guide. The high-level flow:

Set up PG 18 as a subscriber to your PG 16 publisher.
Let it replicate and catch up.
Sync sequence values (logical replication doesn't carry them), then cut over application connections to the new cluster.
Decommission the old cluster.

Snowflake Postgres applies minor version patches for you. For major version upgrades, use ALTER POSTGRES INSTANCE ... SET POSTGRES_VERSION = <version>. See Snowflake Postgres version upgrades for how the upgrade runs and what downtime to expect.

Pre-Upgrade Checklist

Don't wing it. Every major upgrade should follow a checklist:

1. Test in Non-Production First

Always. Copy your production data into a test environment and run the upgrade there first.

# Create a test instance from a backup
pg_restore -U postgres -d mydb_upgrade_test -Fc /backups/latest.dump

2. Read the Release Notes

Go to the PostgreSQL Release Notes for your target version, and every major version in between if you're skipping some. Look for:

Removed or renamed configuration parameters
Changed default values
Deprecated features you rely on
Migration notes specific to your source version

If you're going to 18, two things catch people. initdb now enables data checksums by default, and pg_upgrade requires checksum settings to match between clusters, so if your old cluster doesn't have checksums, initialize the new one with --no-data-checksums. And MD5 passwords are deprecated, so it's a good time to move to SCRAM.

3. Check Extension Compatibility

Extensions must be compatible with the new major version. Check what's available:

-- On the NEW cluster, check available extensions
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name IN (
  SELECT extname FROM pg_extension
)
ORDER BY name;

Common extensions like pg_stat_statements, PostGIS, and pgvector usually support new versions quickly, but check before you upgrade. After the upgrade, run ALTER EXTENSION ... UPDATE for any extension that has a newer version available.

4. Verify Disk Space

In the default copy mode, pg_upgrade needs roughly 2x your current data directory size (old and new data directories side by side). With --link or --swap you need much less. For pg_dump/pg_restore, you need space for the dump file plus the new cluster.

# Check current data directory size
du -sh /var/lib/postgresql/16/main

# Check available space
df -h /var/lib/postgresql

5. Plan Your Maintenance Window

Even with pg_upgrade, plan for:

Time to stop applications
Time to run the upgrade
Time to run ANALYZE
Time to verify application functionality
Buffer for unexpected issues

6. Script Everything

Don't type commands manually during the upgrade. Write a script, test it in non-production, and execute the tested script in production.

#!/bin/bash
set -e  # Exit on any error

echo "=== Stopping applications ==="
# your app stop commands here

echo "=== Running pg_upgrade --check ==="
/usr/lib/postgresql/17/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/16/main \
  --new-datadir=/var/lib/postgresql/17/main \
  --old-bindir=/usr/lib/postgresql/16/bin \
  --new-bindir=/usr/lib/postgresql/17/bin \
  --check

echo "=== Check passed, proceeding with upgrade ==="
# ... rest of upgrade steps

7. Have a Rollback Plan

Know how you'll get back to the old version if something goes wrong. In pg_upgrade copy mode, the old data directory is untouched until you delete it. With --link or --swap, the old cluster is no longer safe to start once the new one has run, so your rollback is the backup. For logical replication upgrades, the old cluster keeps running until you decommission it.

Always take a backup immediately before starting:

# Final backup before upgrade
pg_dump -U postgres -Fc -f /backups/pre_upgrade_$(date +%Y%m%d).dump mydb

Conclusion

Minor upgrades should be routine: apply them promptly for the security fixes. Major upgrades need planning, but they're nothing to be scared of. The tooling is mature and well-documented, and with Postgres 14 hitting end of life in November 2026, now's a good time to get comfortable with it.

Key takeaways:

If you use Snowflake Postgres, minor patches are handled for you and major upgrades are a single command.
For self-managed environments, apply minor versions promptly.
Plan major upgrades with the checklist above.
Run pg_upgrade --check before the real thing.
Refresh statistics after a major upgrade (much less work when the target is Postgres 18+).
Test in non-production first, every time.