Upgrading PostgreSQL Major/Minor Versions

Quick Start

PostgreSQL has two types of version upgrade: minor and major. Their risk and complexity differ substantially.

# Rolling upgrade: replicas first, then primary
ansible <cls> -b -a 'yum upgrade -y postgresql17*'
pg restart --role replica --force <cls>
pg switchover <cls>
pg restart <cls> <old-primary> --force

# Recommended: Logical replication migration
bin/pgsql-add pg-new              # Create new version cluster
# Configure logical replication to sync data...
# Switch traffic to new cluster

ansible <cls> -b -a 'yum upgrade -y postgis36_17*'
psql -c 'ALTER EXTENSION postgis UPDATE;'

For detailed online migration process, see Online Migration documentation.

Minor Version Upgrade

Minor version upgrades, such as 17.2 → 17.3, are the most common upgrade type. They usually contain security patches and bug fixes. The data directory remains fully compatible. Apply the upgrade with a rolling restart.

Strategy: Use a rolling upgrade to minimize service interruption. Upgrade replicas first. Then switch the primary role and upgrade the original primary.

1. Update repo → 2. Upgrade replica packages → 3. Restart replicas
4. Switchover → 5. Upgrade original primary packages → 6. Restart original primary

Step 1: Prepare packages

Verify that the local software repository has the latest PostgreSQL packages. Then refresh the node package cache:

cd ~/pigsty
./infra.yml -t repo_upstream      # Add upstream repos (needs internet)
./infra.yml -t repo_build         # Rebuild local repo

ansible <cls> -b -a 'yum clean all'
ansible <cls> -b -a 'yum makecache'

ansible <cls> -b -a 'apt clean'
ansible <cls> -b -a 'apt update'

Step 2: Upgrade replicas

Upgrade packages on all replicas and verify version:

ansible <cls> -b -a 'yum upgrade -y postgresql17*'
ansible <cls> -b -a '/usr/pgsql/bin/pg_ctl --version'

ansible <cls> -b -a 'apt install -y postgresql-17'
ansible <cls> -b -a '/usr/lib/postgresql/17/bin/pg_ctl --version'

Restart all replicas to apply new version:

pg restart --role replica --force <cls>

Step 3: Switchover

Execute switchover to transfer primary role to upgraded replica:

pg switchover <cls>
# Or non-interactive:
pg switchover --leader <old-primary> --candidate <new-primary> --scheduled=now --force <cls>

Step 4: Upgrade original primary

The original primary is now a replica. Upgrade its packages and restart it:

ansible <old-primary-ip> -b -a 'yum upgrade -y postgresql17*'

ansible <old-primary-ip> -b -a 'apt install -y postgresql-17'

pg restart <cls> <old-primary-name> --force

Step 5: Verify

Confirm all instances have consistent version:

pg list <cls>
pg query <cls> -c "SELECT version()"

Minor Version Downgrade

In rare cases, you may need to return to the previous PostgreSQL version. For example, the new version may introduce bugs.

Step 1: Get old version packages

cd ~/pigsty; ./infra.yml -t repo_upstream     # Add upstream repos
cd /www/pigsty; repotrack postgresql17-*-17.1 # Download specific version packages
cd ~/pigsty; ./infra.yml -t repo_create       # Rebuild repo metadata

ansible <cls> -b -a 'yum clean all'
ansible <cls> -b -a 'yum makecache'

Step 2: Execute downgrade

ansible <cls> -b -a 'yum downgrade -y postgresql17*'

ansible <cls> -b -a 'apt install -y postgresql-17=17.1*'

Step 3: Restart cluster

pg restart --force <cls>

Major Version Upgrade

Major version upgrades, such as 17 → 18, change the data format. They require specialized data migration tools.

Logical Replication Migration

Logical replication is the recommended approach for production major version upgrades. Core steps:

1. Create new version target cluster → 2. Configure logical replication → 3. Verify data consistency
4. Switch app traffic to new cluster → 5. Decommission old cluster

Step 1: Create new version cluster

pg-meta-new:
  hosts:
    10.10.10.12: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-meta-new
    pg_version: 18                    # New version

bin/pgsql-add pg-meta-new

Step 2: Configure logical replication

-- Source cluster (old version) primary: create publication
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;

-- Target cluster (new version) primary: create subscription
CREATE SUBSCRIPTION upgrade_sub
  CONNECTION 'host=10.10.10.11 port=5432 dbname=mydb user=replicator password=xxx'
  PUBLICATION upgrade_pub;

Step 3: Wait for sync completion

-- Target cluster: check subscription status
SELECT * FROM pg_stat_subscription;

-- Source cluster: check replication slot LSN
SELECT slot_name, confirmed_flush_lsn FROM pg_replication_slots;

Step 4: Switch traffic

After you verify that data synchronization is complete, stop application writes to the source. Wait for final synchronization, then move application connections to the new cluster. Drop the subscription and decommission the source:

-- Target cluster: drop subscription
DROP SUBSCRIPTION upgrade_sub;

For detailed migration process, see Online Migration documentation.

pg_upgrade In-Place Upgrade

pg_upgrade is the official PostgreSQL tool for major version upgrades. Use it for test environments or when longer downtime is acceptable.

Step 1: Install new version packages

./pgsql.yml -l <cls> -t pg_pkg -e pg_version=18

Step 2: Stop Patroni

pg pause <cls>                        # Pause auto-failover
systemctl stop patroni                # Stop Patroni (stops PostgreSQL)

Step 3: Run pg_upgrade

sudo su - postgres
mkdir -p /data/postgres/pg-meta-18/data

# Pre-check (-c parameter: check only, don't execute)
/usr/pgsql-18/bin/pg_upgrade \
  -b /usr/pgsql-17/bin -B /usr/pgsql-18/bin \
  -d /data/postgres/pg-meta-17/data \
  -D /data/postgres/pg-meta-18/data \
  -v -c

# Execute upgrade
/usr/pgsql-18/bin/pg_upgrade \
  -b /usr/pgsql-17/bin -B /usr/pgsql-18/bin \
  -d /data/postgres/pg-meta-17/data \
  -D /data/postgres/pg-meta-18/data \
  --link -j 8 -v

Step 4: Update links and start

rm -rf /usr/pgsql && ln -s /usr/pgsql-18 /usr/pgsql
rm -rf /pg && ln -s /data/postgres/pg-meta-18 /pg
# Edit /etc/patroni/patroni.yml to update paths
systemctl start patroni
pg resume <cls>

Step 5: Post-processing

/usr/pgsql-18/bin/vacuumdb --all --analyze-in-stages
./delete_old_cluster.sh   # Cleanup script generated by pg_upgrade

Extension Upgrade

When you upgrade PostgreSQL, you usually also need to upgrade its extensions.

Upgrade extension packages

ansible <cls> -b -a 'yum upgrade -y postgis36_17 timescaledb-2-postgresql-17* pgvector_17*'

ansible <cls> -b -a 'apt install -y postgresql-17-postgis-3 postgresql-17-pgvector'

Upgrade extension objects

After package upgrade, execute extension upgrade in database:

-- View upgradeable extensions
SELECT name, installed_version, default_version FROM pg_available_extensions
WHERE installed_version IS NOT NULL AND installed_version <> default_version;

-- Upgrade extensions
ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION timescaledb UPDATE;
ALTER EXTENSION vector UPDATE;

-- Check extension versions
SELECT extname, extversion FROM pg_extension;

Important Notes

1. Back up first: Make a complete backup before every upgrade.
2. Test first: Verify the upgrade procedure in a test environment.
3. Check extensions: Verify that all extensions support the target version.
4. Plan rollback: Prepare a rollback plan, especially for major upgrades.
5. Monitor closely: Monitor database performance and error logs after the upgrade.
6. Keep records: Record all operations and problems during the upgrade.

Related Documentation

• Online Migration: Zero-downtime migration using logical replication
• Patroni Management: Manage cluster with patronictl
• Cluster Management: Cluster creation, scaling, destruction
• Backup Recovery: PostgreSQL backup and recovery
• Extension Management: Extension installation and management