Major Version Upgrade with pg_upgrade

pg_upgrade --link (-k)

Hard-links the old cluster's data files into the new cluster instead of copying them, making the upgrade nearly instantaneous regardless of database size, at the cost of making the old cluster permanently unsafe to start once the new one has started. Copy mode (the default) is slower but leaves the old cluster fully intact and independently startable as a fallback.

Why Statistics Do Not Survive an Upgrade

pg_upgrade migrates schema and data files directly, but pg_statistic's contents are tied to the exact internal representation of the PostgreSQL version that produced them — rather than risk migrating something version-specific, pg_upgrade leaves the new cluster's statistics empty and tells you explicitly, in its own final output, to run vacuumdb --analyze-only afterward.

🛑 Stop the Cluster and Build a Fresh Target

pg_upgrade needs both the old and new postmaster stopped, and a genuinely fresh new cluster built with initdb.

as-postgres pg_ctl stop -D /var/lib/postgresql/18/data -w

as-postgres mkdir -p /tmp/new_data

as-postgres initdb -D /tmp/new_data -U postgres --auth=trust

student@lab:~$ as-postgres pg_ctl stop -D /var/lib/postgresql/18/data -w waiting for server to shut down.... done server stopped student@lab:~$ as-postgres mkdir -p /tmp/new_data student@lab:~$ as-postgres initdb -D /tmp/new_data -U postgres --auth=trust The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "C". The default database encoding has accordingly been set to "SQL_ASCII". The default text search configuration will be set to "english". Data page checksums are enabled. creating directory /tmp/new_data ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... sysv selecting default "max_connections" ... 100 selecting default "shared_buffers" ... 128MB selecting default time zone ... UTC creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... sh: locale: not found 2026-06-26 16:02:45.142 UTC [144] WARNING: no usable system locales were found ok syncing data to disk ... ok Success. You can now start the database server using: pg_ctl -D /tmp/new_data -l logfile start

✅ Check Before Touching Anything

Run pg_upgrade --check and confirm the two clusters are compatible before any real changes happen.

as-postgres sh -c 'cd /tmp && pg_upgrade --old-datadir=/var/lib/postgresql/18/data --new-datadir=/tmp/new_data --old-bindir=/usr/local/pgsql/bin --new-bindir=/usr/local/pgsql/bin --check'

student@lab:~$ as-postgres sh -c 'cd /tmp && pg_upgrade --old-datadir=/var/lib/postgresql/18/data --new-datadir=/tmp/new_data --old-bindir=/usr/local/pgsql/bin --new-bindir=/usr/local/pgsql/bin --check' Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database connection settings ok Checking database user is the install user ok Checking for prepared transactions ok Checking for contrib/isn with bigint-passing mismatch ok Checking for valid logical replication slots ok Checking for subscription state ok Checking data type usage ok Checking for objects affected by Unicode update ok Checking for not-null constraint inconsistencies ok Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok Checking for new cluster tablespace directories ok *Clusters are compatible*

⚡ Run the Real Upgrade, Link Mode

Run the actual upgrade with --link, hard-linking data files instead of copying them.

as-postgres sh -c 'cd /tmp && pg_upgrade --old-datadir=/var/lib/postgresql/18/data --new-datadir=/tmp/new_data --old-bindir=/usr/local/pgsql/bin --new-bindir=/usr/local/pgsql/bin --link'

student@lab:~$ as-postgres sh -c 'cd /tmp && pg_upgrade --old-datadir=/var/lib/postgresql/18/data --new-datadir=/tmp/new_data --old-bindir=/usr/local/pgsql/bin --new-bindir=/usr/local/pgsql/bin --link' Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database connection settings ok Checking database user is the install user ok Checking for prepared transactions ok Checking for contrib/isn with bigint-passing mismatch ok Checking for valid logical replication slots ok Checking for subscription state ok Checking data type usage ok Checking for objects affected by Unicode update ok Checking for not-null constraint inconsistencies ok Creating dump of global objects ok Creating dump of database schemas template1 postgres beer_db social_db tv_db weather_db ok Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok Checking for new cluster tablespace directories ok If pg_upgrade fails after this point, you must re-initdb the new cluster before continuing. Performing Upgrade ------------------ Setting locale and encoding for new cluster ok Analyzing all rows in the new cluster ok Freezing all rows in the new cluster ok Deleting files from new pg_xact ok Copying old pg_xact to new server ok Setting oldest XID for new cluster ok Setting next transaction ID and epoch for new cluster ok Deleting files from new pg_multixact/offsets ok Copying old pg_multixact/offsets to new server ok Deleting files from new pg_multixact/members ok Copying old pg_multixact/members to new server ok Setting next multixact ID and offset for new cluster ok Resetting WAL archives ok Setting frozenxid and minmxid counters in new cluster ok Restoring global objects in the new cluster ok Restoring database schemas in the new cluster template1 postgres beer_db social_db tv_db weather_db ok Adding ".old" suffix to old "global/pg_control" ok If you want to start the old cluster, you will need to remove the ".old" suffix from "/var/lib/postgresql/18/data/global/pg_control.old". Because "link" mode was used, the old cluster cannot be safely started once the new cluster has been started. Linking user relation files ok [... every table and index file in every database, hard-linked one by one ...] Setting next OID for new cluster ok Sync data directory to disk ok Creating script to delete old cluster ok Checking for extension updates ok Upgrade Complete ---------------- Some statistics are not transferred by pg_upgrade. Once you start the new server, consider running these two commands: /usr/bin/vacuumdb --all --analyze-in-stages --missing-stats-only /usr/bin/vacuumdb --all --analyze-only Running this script will delete the old cluster's data files: ./delete_old_cluster.sh

🚀 Start the New Cluster and Verify the Data

Start the upgraded cluster and confirm real data and real roles both survived.

as-postgres pg_ctl start -D /tmp/new_data -l /tmp/new_data_log.txt -w

psql -U postgres -d beer_db -c "SELECT count(*) FROM beers;"

student@lab:~$ as-postgres pg_ctl start -D /tmp/new_data -l /tmp/new_data_log.txt -w waiting for server to start.... done server started student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM beers;" SET count ------- 400 (1 row)

🔍 Confirm Roles Survived Too

Check that every role from the old cluster — not just table data — made it across.

psql -U postgres -d postgres -c "\\du"

student@lab:~$ psql -U postgres -d postgres -c "\\du" SET List of roles Role name | Attributes ------------+------------------------------------------------------------ beer_db | postgres | Superuser, Create role, Create DB, Replication, Bypass RLS social_db | tv_db | weather_db |

📊 Refresh the Planner Statistics

Run the vacuumdb command pg_upgrade itself recommended, exactly as its own output told you to.

as-postgres vacuumdb --all --analyze-only -U postgres

student@lab:~$ as-postgres vacuumdb --all --analyze-only -U postgres vacuumdb: vacuuming database "beer_db" vacuumdb: vacuuming database "postgres" vacuumdb: vacuuming database "social_db" vacuumdb: vacuuming database "template1" vacuumdb: vacuuming database "tv_db" vacuumdb: vacuuming database "weather_db"