PostgreSQL Major Version Upgrade on AlmaLinux 10 with pg_upgrade (17 to 18)

November 7, 2026 · by Zwiegnet Consulting · Database, Linux

PostgreSQL on AlmaLinux series, part 6. Previous: part 5, PostgreSQL Performance Tuning on AlmaLinux 10: postgresql.conf, EXPLAIN ANALYZE, Indexes and Autovacuum. Next: part 7, PostgreSQL SSL/TLS and PgBouncer Connection Pooling on AlmaLinux 10.

A major-version upgrade is the one PostgreSQL change that still needs a planned outage. Minor releases (18.0 to 18.1) are a package update and a restart; majors (17 to 18) change the catalog layout, so you either dump and restore or use pg_upgrade. This guide walks through a PostgreSQL 17 to 18 upgrade on AlmaLinux 10 with PGDG packages: install 18 beside 17, initialize a compatible new cluster, run pg_upgrade --check then the real upgrade, restore config, rebuild any missing statistics, and only then remove the old data directory. The same pattern applies to any supported major jump (for example 16 to 18).

Applies to: PostgreSQL from the PGDG repository on AlmaLinux 10. Versioned paths are /usr/pgsql-17/ and /usr/pgsql-18/, data under /var/lib/pgsql/17/data and /var/lib/pgsql/18/data, systemd units postgresql-17 and postgresql-18. AlmaLinux 10 AppStream ships PostgreSQL 16 in the unversioned postgresql packages (/var/lib/pgsql/data); for production major upgrades, PGDG versioned packages are the usual path because you can install two majors side by side. Also applies to RHEL, Rocky Linux, Oracle Linux and CentOS Stream 10.

Prerequisites

  • A working PostgreSQL 17 cluster on db1 (192.168.1.40) installed from PGDG the same way as part 1 (Install PostgreSQL on AlmaLinux 10: PGDG vs AppStream, pg_hba.conf and Remote Access), just with the 17 packages. If you followed parts 1–5 on PostgreSQL 18 already, use a lab clone or a spare host that still runs 17 for this walkthrough.
  • A verified backup before you touch anything: at least pg_dumpall (globals) plus a logical dump or a base backup. See part 3 (PostgreSQL Backup and Restore on AlmaLinux 10: pg_dump, pg_dumpall and pg_basebackup). pg_upgrade is fast, but a failed link/swap run can leave you needing that backup.
  • Disk space: default --copy needs roughly as much free space as the current data directory (plus WAL and tablespaces). --link, --clone and --swap need far less free space but share or move files with the old cluster.
  • A downtime window long enough for stop → upgrade → verify. Do a dry run on a restored copy first.
  • Matching extension RPMs for the new major (postgresql18-contrib, PostGIS 18 packages, and so on). Shared libraries must exist for 18 before pg_upgrade runs; you do not CREATE EXTENSION again during the upgrade.
  • Root or sudo, and the ability to run commands as the postgres OS user. Always run the new major’s pg_upgrade binary (/usr/pgsql-18/bin/pg_upgrade).

What pg_upgrade does (and does not do)

pg_upgrade creates new system catalogs for the new major and reuses the old user data files. It is not needed for minor upgrades. It checks compile-time compatibility (32/64-bit, data checksums, locale and so on) via pg_controldata, but it cannot prove that every third-party .so is binary-compatible — that is why matching new-major packages matter. Logical replication cluster upgrades have their own procedure in the PostgreSQL docs; this guide covers a normal primary (and briefly, physical standbys).

Step 1: Record the old cluster settings you must match

Before installing anything new, capture the facts initdb must reproduce. Run these on the running 17 cluster:

sudo -iu postgres /usr/pgsql-17/bin/psql -c "SELECT version();"
sudo -iu postgres /usr/pgsql-17/bin/psql -c "SHOW data_directory;"
sudo -iu postgres /usr/pgsql-17/bin/psql -c "SHOW server_encoding; SHOW lc_collate; SHOW lc_ctype; SHOW data_checksums;"
sudo -iu postgres /usr/pgsql-17/bin/pg_controldata /var/lib/pgsql/17/data | egrep 'Catalog version|Database block size|Data page checksum|LC_COLLATE|LC_CTYPE|Database encoding'
df -h /var/lib/pgsql
du -sh /var/lib/pgsql/17/data

Also list installed extensions so you know which 18 packages to install:

sudo -iu postgres /usr/pgsql-17/bin/psql -c "SELECT extname, extversion FROM pg_extension ORDER BY 1;"
rpm -qa 'postgresql17*' | sort

Save a copy of postgresql.conf, postgresql.auto.conf and pg_hba.conf from the 17 data directory. After the upgrade you will merge those settings into the new cluster (pg_upgrade does not carry every config file change for you in a useful form).

Step 2: Take the backup and freeze the change window

At minimum, dump globals and a logical backup of every database. On a large cluster, also take a base backup or filesystem snapshot while the server is either stopped or in backup mode:

sudo -iu postgres mkdir -p /var/lib/pgsql/upgrade-backup-17
sudo -iu postgres /usr/pgsql-17/bin/pg_dumpall -g -f /var/lib/pgsql/upgrade-backup-17/globals.sql
sudo -iu postgres /usr/pgsql-17/bin/pg_dumpall -f /var/lib/pgsql/upgrade-backup-17/full.sql
# optional physical safety net (part 3):
# sudo -iu postgres /usr/pgsql-17/bin/pg_basebackup -D /var/lib/pgsql/upgrade-backup-17/base -Fp -X stream -c fast

Tell application owners the cutover time. Streaming standbys (part 4, PostgreSQL Streaming Replication on AlmaLinux 10: Primary/Standby Setup, Switchover and Failover) should be fully caught up before you stop the primary for the upgrade.

Step 3: Install PostgreSQL 18 packages alongside 17

Do not remove the 17 packages yet. PGDG versioned RPMs install side by side:

sudo dnf install -y postgresql18-server postgresql18-contrib
# plus any extension packages that match what 17 had, for example:
# sudo dnf install -y postgresql18-llvmjit  # if you used JIT
rpm -qa 'postgresql18*' | sort
ls /usr/pgsql-18/bin/pg_upgrade /usr/pgsql-17/bin/postgres

Confirm pg_upgrade comes from the new tree:

/usr/pgsql-18/bin/pg_upgrade --version

Step 4: Initialize the new 18 cluster with compatible options

Create the empty 18 data directory with the same encoding, locale and checksum setting the 17 cluster used. On PGDG, the systemd helper wraps initdb:

# Inspect how 17 was initialized (example — use YOUR pg_controldata values)
sudo -iu postgres /usr/pgsql-17/bin/pg_controldata /var/lib/pgsql/17/data | egrep 'checksum|LC_|encoding|block size'

If 17 was created with the PGDG defaults (UTF8, data checksums on — the PostgreSQL 18 default as well), the stock setup unit is enough:

sudo /usr/pgsql-18/bin/postgresql-18-setup initdb
# equivalent manual form (as postgres), adjusting locale flags to match 17:
# /usr/pgsql-18/bin/initdb -D /var/lib/pgsql/18/data --data-checksums --encoding=UTF8 --locale=en_US.UTF-8
ls /var/lib/pgsql/18/data

Do not start postgresql-18 yet for a production cutover. Install extension shared objects (the RPMs above) into the new tree, but do not run CREATE EXTENSION — pg_upgrade will migrate the extension catalogs from 17.

If you have custom full-text search dictionary or synonym files under the old share/tsearch_data, copy them into the 18 share directory before the upgrade.

Step 5: Adjust authentication for the upgrade connections

pg_upgrade starts short-lived postmasters and connects as the install user (usually postgres). The simplest approach on a local AlmaLinux box is peer auth for local sockets, or a ~/.pgpass file. Temporarily tighten pg_hba.conf on both data directories if needed:

# In both /var/lib/pgsql/17/data/pg_hba.conf and /var/lib/pgsql/18/data/pg_hba.conf
# ensure a local peer (or trust, only for the outage window) line for postgres:
# local   all   postgres   peer

You will restore the production pg_hba.conf after the upgrade.

Step 6: Stop both servers

Both clusters must be stopped for the real upgrade. --check can run while the old server is still up; the real run cannot.

sudo systemctl stop postgresql-17
# 18 should not be running yet; stop it if a package script started it
sudo systemctl stop postgresql-18 || true
sudo systemctl status postgresql-17 postgresql-18 --no-pager || true

If you have standbys, leave them running long enough to receive the final WAL, then stop them too before you begin the primary upgrade. Check that the primary and each standby report the same “Latest checkpoint location” from pg_controldata if you plan to use the rsync hard-link standby method later.

Step 7: Run pg_upgrade –check, then the real upgrade

Always invoke the 18 binary. Run the check first from a directory the postgres user can write (pg_upgrade needs write permission in the current working directory for sockets and logs; if the path is very long, use -s /tmp):

sudo -iu postgres
cd ~
/usr/pgsql-18/bin/pg_upgrade \
  --old-bindir=/usr/pgsql-17/bin \
  --new-bindir=/usr/pgsql-18/bin \
  --old-datadir=/var/lib/pgsql/17/data \
  --new-datadir=/var/lib/pgsql/18/data \
  --check

Fix anything the check reports (missing .so files, incompatible reg* columns, tablespace paths) before continuing. Then run the real upgrade. Default mode is --copy:

/usr/pgsql-18/bin/pg_upgrade \
  --old-bindir=/usr/pgsql-17/bin \
  --new-bindir=/usr/pgsql-18/bin \
  --old-datadir=/var/lib/pgsql/17/data \
  --new-datadir=/var/lib/pgsql/18/data \
  --jobs="$(nproc)"

Add --link, --clone, --copy-file-range or --swap when you have chosen that mode (and pass the same flag to --check). On success, pg_upgrade prints next steps and may leave scripts such as delete_old_cluster.sh and rebuild SQL under the new data directory’s pg_upgrade_output.d (kept on failure; removed automatically on clean success unless you use --retain).

Upgrade modes: copy, link, clone, swap

ModeHow it worksWhen to useRollback notes
--copy (default)Copies relation files into the new data directorySafest default; needs ~2× diskOld cluster untouched; restart 17 if you abort
--link (-k)Hard-links files; old and new share inodesSame filesystem; fastest common production cutover; enables rsync standby trickDo not start 18 if you still want 17. If you started 18, restore from backup
--cloneReflinks / file clones (Btrfs, XFS with reflink, APFS)Same filesystem + clone support; speed of link without poisoning the old clusterOld cluster stays usable if clone succeeded
--copy-file-rangeUses copy_file_range(2)Linux/FreeBSD; may share blocks or copy faster depending on FSSame rollback idea as copy
--swapMoves data dirs then swaps in new catalogsMany relations; often fastest; same filesystem; prefer --sync-method=fsyncOnce file transfer starts, old cluster may be unsafe — restore from backup

Link, clone and swap all require the old and new data directories on the same filesystem (tablespaces and pg_wal may live elsewhere). Never use --no-sync on production.

Step 8: Restore configuration and start PostgreSQL 18

Copy your production settings into the new cluster. At minimum, restore pg_hba.conf and re-apply the tuning from part 5 (PostgreSQL Performance Tuning on AlmaLinux 10: postgresql.conf, EXPLAIN ANALYZE, Indexes and Autovacuum) with ALTER SYSTEM or by merging postgresql.conf / postgresql.auto.conf:

# Example: restore hba from the saved 17 copy, then start
sudo -iu postgres cp /var/lib/pgsql/upgrade-backup-17/pg_hba.conf /var/lib/pgsql/18/data/pg_hba.conf
# merge listen_addresses, shared_buffers, etc. from your notes / auto.conf
sudo systemctl enable --now postgresql-18
sudo systemctl status postgresql-18 --no-pager
sudo -iu postgres /usr/pgsql-18/bin/psql -c "SELECT version();"

Disable the old unit so a reboot does not start 17 by accident:

sudo systemctl disable postgresql-17

Step 9: Run post-upgrade scripts and refresh statistics

If pg_upgrade printed rebuild or extension scripts, run them as instructed, for example:

sudo -iu postgres
# paths vary; pg_upgrade tells you the exact filenames
psql -f /var/lib/pgsql/18/data/update_extensions.sql postgres   # example name

PostgreSQL 18’s pg_upgrade transfers most optimizer statistics by default, so the planner is not starting from zero the way older majors did. It still does not transfer everything (for example CREATE STATISTICS objects, some extension stats, and cumulative stats). Unless you passed --no-statistics, follow the recommended two-step analyze:

sudo -iu postgres
# quickly fill in anything that has no stats yet
/usr/pgsql-18/bin/vacuumdb --all --analyze-in-stages --missing-stats-only --jobs="$(nproc)"
# then refresh cumulative stats used for vacuum/analyze triggering
/usr/pgsql-18/bin/vacuumdb --all --analyze-only --jobs="$(nproc)"
# if vacuum_cost_delay is non-zero and you want this to finish faster during the outage:
# PGOPTIONS='-c vacuum_cost_delay=0' /usr/pgsql-18/bin/vacuumdb --all --analyze-only --jobs="$(nproc)"

Step 10: Update extensions

After the server is up, bump extension versions where new packages shipped SQL updates:

sudo -iu postgres psql -c "SELECT extname, extversion FROM pg_extension ORDER BY 1;"
# per database, for each extension that has an update path:
sudo -iu postgres psql -d myapp -c "ALTER EXTENSION pg_stat_statements UPDATE;"

If pg_upgrade generated an extension update script, prefer that script so you do not miss a database.

Step 11: Standbys (brief)

You have two practical options after the primary is on 18:

  • Recreate standbys with pg_basebackup from the new primary (same flow as part 4, PostgreSQL Streaming Replication on AlmaLinux 10: Primary/Standby Setup, Switchover and Failover). Simplest and recommended if you used --copy/--clone or want a clean slate.
  • rsync hard-link method (only if you used --link on the primary): install 18 binaries on each standby, stop old standbys, remove any empty new data dirs, then from a parent directory on the primary run rsync --archive --delete --hard-links --size-only --no-inc-recursive for the versioned data paths (and each tablespace / relocated pg_wal). Do not start any server until rsync finishes. Then configure streaming to the new primary.

This is not a full HA runbook — test the standby path in the lab before production cutover. Logical replication upgrades are covered separately in the upstream docs (Section 29.13).

Step 12: Verify before deleting the old cluster

  1. Version and data directory: SELECT version(); SHOW data_directory; should show 18 and /var/lib/pgsql/18/data.
  2. Databases and roles exist: \l, \du.
  3. Extensions load: SELECT extname, extversion FROM pg_extension; and a quick smoke query that uses them.
  4. Row counts on critical tables match your pre-upgrade notes.
  5. Application connection test (DSN, pooler, scram passwords).
  6. If you have replication, SELECT * FROM pg_stat_replication; on the new primary once standbys are rebuilt.
  7. Optional: run your usual pgbench or application canary from part 5.
sudo -iu postgres psql <<'SQL'
SELECT version();
SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;
SELECT extname, extversion FROM pg_extension ORDER BY 1;
-- example sanity check; replace with your tables
-- SELECT 'orders' AS t, count(*) FROM orders;
SQL

Step 13: Delete the old cluster (only after verification)

When you are satisfied, remove the 17 data directory with the script pg_upgrade wrote (name is printed at the end of a successful run), or remove it manually. Automatic deletion is skipped if you have user-defined tablespaces inside the old data directory.

sudo -iu postgres
./delete_old_cluster.sh
# or: rm -rf /var/lib/pgsql/17/data
# Later, when nothing needs the 17 binaries:
# sudo dnf remove postgresql17-server postgresql17

Reverting / failure modes

  • --check only: old cluster unmodified; start 17 again.
  • Real run with default --copy (or --clone / --copy-file-range): old cluster unmodified; start 17 again if you have not discarded it.
  • --link: if linking started, $PGDATA/global/pg_control on the old cluster may be renamed to pg_control.old. If you never started 18, rename it back to pg_control and start 17. If you did start 18, the old cluster is unsafe — restore from backup.
  • --swap: once pg_upgrade reports the old cluster is no longer safe to start, restore from backup. Before that message, 17 may still be restartable.
  • Schema restore errors mid-run: fix the cause on the old cluster (often a contrib module), recreate a fresh 18 data directory with initdb, and try again.

Troubleshooting tips

  • Missing shared library / incompatible extension: install the matching postgresql18-* RPM before re-running. Do not CREATE EXTENSION on 18 before the upgrade.
  • Tablespaces: ensure paths exist and are writable by postgres on the new major; link/clone/swap still require the main data dirs on one filesystem.
  • reg* OID types: columns using regproc, regprocedure, regoper, regoperator, regconfig, regdictionary, regcollation or regnamespace cannot be upgraded in place (regclass, regrole and regtype are fine). Change those columns before upgrading.
  • Socket path too long: pg_upgrade -s /tmp (directory must not be world-writable in a careless way; restrict permissions).
  • Long current working directory: cd to ~postgres or /tmp before running.
  • Wrong binary: using /usr/pgsql-17/bin/pg_upgrade against an 18 new bindir is unsupported — always use the new major’s binary.
  • AppStream vs PGDG: AppStream PostgreSQL 16 uses unversioned paths; migrating AppStream → PGDG 18 is still pg_upgrade, but point --old-bindir/--old-datadir at the AppStream locations (/usr/bin, /var/lib/pgsql/data) and keep package sets straight so two servers do not fight over port 5432.

Cheat sheet

TaskCommand
Check only/usr/pgsql-18/bin/pg_upgrade ... --check
Upgrade (copy)/usr/pgsql-18/bin/pg_upgrade --old-bindir=/usr/pgsql-17/bin --new-bindir=/usr/pgsql-18/bin --old-datadir=/var/lib/pgsql/17/data --new-datadir=/var/lib/pgsql/18/data -j$(nproc)
Upgrade (link)Same as above plus --link (same filesystem)
Controldatapg_controldata /var/lib/pgsql/17/data
Init new clusterpostgresql-18-setup initdb
Stop old/newsystemctl stop postgresql-17 postgresql-18
Start newsystemctl enable --now postgresql-18
Missing statsvacuumdb --all --analyze-in-stages --missing-stats-only
Full analyzevacuumdb --all --analyze-only
Extension bumpALTER EXTENSION ... UPDATE;
Remove old data./delete_old_cluster.sh

What’s next in the PostgreSQL series

Part 7, PostgreSQL SSL/TLS and PgBouncer Connection Pooling on AlmaLinux 10, covers SSL/TLS client and server connections and PgBouncer connection pooling — the usual next hardening step once the major version is current. Earlier parts: part 1, Install PostgreSQL on AlmaLinux 10: PGDG vs AppStream, pg_hba.conf and Remote Access; part 2, PostgreSQL Users, Roles and Privileges on AlmaLinux 10; part 3, PostgreSQL Backup and Restore on AlmaLinux 10: pg_dump, pg_dumpall and pg_basebackup; part 4, PostgreSQL Streaming Replication on AlmaLinux 10: Primary/Standby Setup, Switchover and Failover; and part 5, PostgreSQL Performance Tuning on AlmaLinux 10: postgresql.conf, EXPLAIN ANALYZE, Indexes and Autovacuum.

Official references: PostgreSQL documentation: pg_upgrade, vacuumdb, pg_controldata and the PostgreSQL Yum Repository (PGDG).

, , , , , , , , , , , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *