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

Duration: 5

PostgreSQL follows a predictable release cadence: one new major version per year, with five years of support for each. Here's the current landscape:

Version	Release Year	End of Life	Notes
PG 14	2021	Nov 2026	Final year of support
PG 15	2022	Nov 2027
PG 16	2023	Nov 2028
PG 17	2024	Nov 2029
PG 18	2025	Nov 2030
PG 19	Sep 2026 (anticipated)	Nov 2031	Beta 1 released Jun 2026

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

The golden rule: always read the release notes. Every single time. Even for minor releases. The Postgres community is excellent about documenting what changed and why.

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

Duration: 5

Minor version upgrades are low-risk and should be applied promptly — they fix bugs and security vulnerabilities.

aside positive Using Snowflake Postgres? Minor version patches are applied automatically with zero downtime — no action required on your part. The instructions below are for local and self-managed development 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 modifications. But — say it with us — always read the release notes before applying them.

Major Version Upgrades

Duration: 15

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

aside positive 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 conceptually — 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 transforms the data directory in place, which is dramatically faster than a logical dump for large databases.

# 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

Critical: 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 run the generated statistics script:

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

# Refresh optimizer statistics — essential for good query plans
/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 rather than waiting for a full analyze of every table.

Option 3: Logical Replication (Zero-Downtime)

For environments that cannot tolerate maintenance windows, logical replication lets you set up the new version as a replica, 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 17 as a subscriber to your PG 16 publisher
Let it replicate and catch up
Cut over application connections to the new cluster
Decommission the old cluster

aside positive Snowflake Postgres handles minor version patches automatically with zero downtime. For major version upgrades, use ALTER POSTGRES INSTANCE ... SET POSTGRES_VERSION = '<version>' — Snowflake provisions the upgraded instance and switches over seamlessly. See the full documentation: Snowflake Postgres version upgrades.

Pre-Upgrade Checklist

Duration: 10

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

1. Test in Non-Production First

Always. No exceptions. Clone your production data into a test environment and run the upgrade there.

# 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. Look for:

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

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 verify before upgrading.

4. Verify Disk Space

pg_upgrade needs roughly 2x your current data directory size during the upgrade (for the old and new data directories). 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. For pg_upgrade, the old data directory is preserved until you explicitly delete it. For logical replication upgrades, the old cluster is still 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

Duration: 2

Keeping Postgres up to date is one of the most important parts of database administration. Minor upgrades should be routine — apply them promptly for security fixes. Major upgrades need planning but shouldn't be feared; the tooling is mature and well-documented.

Key takeaways:

If you use Snowflake Postgres, minor patches are automatic and major upgrades are a single command
For local/self-managed environments, apply minor versions promptly (bug fixes and security patches)
Plan major upgrades carefully with the pre-upgrade checklist
Always use pg_upgrade --check before committing
Run ANALYZE after major upgrades to refresh optimizer statistics
Test in non-production first, every time

Related Resources

PostgreSQL Versioning Policy

Official documentation on PostgreSQL's version numbering and support timelines.

Snowflake Postgres Version Upgrades

Managed upgrade workflow for Snowflake Postgres instances — initiate major version upgrades with a single ALTER command.

Greg Sabino Mullane — pg_upgrade Tips

Practical tips and gotchas for running pg_upgrade from one of the Postgres community's most experienced DBAs.

Replication Guide — Logical Replication for Upgrades

How to use logical replication for zero-downtime major version upgrades.

Backups Guide

Always have a solid backup before upgrading — this guide covers your options.

PostgreSQL Release Notes

The release notes you should always read before upgrading. Always.

Updated 2026-07-08

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances