Eric Guo's blog.cloud-mes.com

Hoping writing JS, Ruby & Rails and Go article, but fallback to DevOps note

Upgrade PostgreSQL 16.x to 17.x on Rocky Linux 9.2

• Permalink

This guide upgrades a dedicated Rocky Linux 9.2 server from PostgreSQL 16.x to 17.x in place with pg_upgrade. PostgreSQL 17 is installed alongside PostgreSQL 16, and the old data directory is retained for rollback.

The commands use --copy, not --link. Copy mode requires enough free disk space for another copy of the cluster, but PostgreSQL 16 remains usable until PostgreSQL 17 receives new production writes.

The paths below match installations made with the official PostgreSQL Yum repository:

Set the PostgreSQL upgrade paths
export OLD_BIN=/usr/pgsql-16/bin

export NEW_BIN=/usr/pgsql-17/bin

export OLD_DATA=/var/lib/pgsql/16/data

export NEW_DATA=/var/lib/pgsql/17/data

export UPGRADE_DIR=/var/lib/pgsql/upgrade-16-to-17

Adjust them if your current server uses different paths.

1. Inventory the PostgreSQL 16 cluster

Confirm the running version and data directory:

Check the PostgreSQL 16 version and data directory
sudo -u postgres "$OLD_BIN/psql" -XAtc "SELECT version();"

sudo -u postgres "$OLD_BIN/psql" -XAtc "SHOW data_directory;"

Record the cluster encoding, locale, and checksum setting. The new cluster must be initialized compatibly.

Check encoding, locale, and data checksums
sudo -u postgres "$OLD_BIN/psql" -X -c "

SELECT current_setting('server_encoding') AS encoding,

       current_setting('lc_collate') AS lc_collate,

       current_setting('lc_ctype') AS lc_ctype;

"

 
sudo -u postgres "$OLD_BIN/pg_controldata" "$OLD_DATA" \

  | grep -E "Database cluster state|Data page checksum version"

Check tablespaces because their data also needs space during a copy-mode upgrade:

List PostgreSQL tablespaces
sudo -u postgres "$OLD_BIN/psql" -X -c "

SELECT spcname, pg_tablespace_location(oid)

FROM pg_tablespace

ORDER BY spcname;

"

List extensions in every connectable database:

List extensions in every PostgreSQL database
sudo -iu postgres bash <<'EOF'

while IFS= read -r database; do

  echo "=== Database: $database ==="

  /usr/pgsql-16/bin/psql -X -d "$database" -c \

    "SELECT extname, extversion FROM pg_extension ORDER BY extname;"

done < <(

  /usr/pgsql-16/bin/psql -XAtc \

    "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate"

)

EOF

Install a PostgreSQL 17-compatible build of every extension containing native code before running pg_upgrade. Do not manually create those extensions in the empty PostgreSQL 17 cluster; pg_upgrade migrates their definitions.

2. Update PostgreSQL 16 to its latest minor release

First update the existing 16.x packages. Plan a short outage for this step.

Update PostgreSQL 16 packages
sudo systemctl stop postgresql-16

sudo dnf upgrade -y 'postgresql16*' 'pgvector_16*'

sudo systemctl start postgresql-16

sudo systemctl status postgresql-16 --no-pager

Verify that the server is ready:

Verify the updated PostgreSQL 16 server
sudo -u postgres "$OLD_BIN/psql" -XAtc "SELECT version();"

sudo -u postgres "$OLD_BIN/pg_isready"

If pgvector was installed from PGXN rather than an RPM, omit pgvector_16* and update it using the same installation method originally used.

3. Take backups

Stop here if there is no tested backup. Create a logical backup of all databases and roles:

Back up all PostgreSQL 16 databases and roles
sudo install -d -m 700 /var/backups/postgresql

sudo -u postgres "$OLD_BIN/pg_dumpall" \

  | gzip > "/var/backups/postgresql/pgsql16-before-pg17-$(date +%Y%m%d-%H%M%S).sql.gz"

Back up the configuration files:

Back up the PostgreSQL 16 configuration
sudo install -d -m 700 /root/postgresql16-config-backup

sudo cp -a \

  "$OLD_DATA/postgresql.conf" \

  "$OLD_DATA/postgresql.auto.conf" \

  "$OLD_DATA/pg_hba.conf" \

  "$OLD_DATA/pg_ident.conf" \

  /root/postgresql16-config-backup/

Check disk space for the data directory and every user tablespace:

Check data size and available disk space
sudo du -sh "$OLD_DATA"

sudo df -h "$OLD_DATA"

A filesystem or VM snapshot taken while PostgreSQL is stopped provides an additional rollback layer.

4. Install PostgreSQL 17 and extensions

Install PostgreSQL 17 alongside PostgreSQL 16:

Install PostgreSQL 17 packages
sudo dnf install -y \

  postgresql17-server \

  postgresql17-contrib \

  postgresql17-devel

If pgvector was installed from the PostgreSQL Yum repository, install its PostgreSQL 17 package:

Install pgvector for PostgreSQL 17
sudo dnf install -y pgvector_17

Prefer the RPM package when available and do not install pgvector with both RPM and PGXN. If an extension is available only through PGXN, select the PostgreSQL 17 pg_config explicitly:

Install a PGXN extension for PostgreSQL 17
sudo pgxnclient install \

  --pg_config /usr/pgsql-17/bin/pg_config \

  vector

Confirm that both versions are installed:

Check the installed PostgreSQL binaries
"$OLD_BIN/postgres" --version

"$NEW_BIN/postgres" --version

"$NEW_BIN/pg_upgrade" --version

5. Initialize an empty PostgreSQL 17 cluster

Initialize the new cluster with the encoding and locale recorded earlier. Based on the original Rocky Linux installation, these are likely UTF8 and en_US.UTF-8:

Initialize the PostgreSQL 17 cluster
sudo install -d -o postgres -g postgres -m 700 "$NEW_DATA"

sudo -u postgres "$NEW_BIN/initdb" \

  --pgdata="$NEW_DATA" \

  --encoding=UTF8 \

  --locale=en_US.UTF-8

If pg_controldata reported checksum version 1, remove the newly initialized empty directory and initialize it with checksums instead:

Initialize PostgreSQL 17 with data checksums
sudo rm -rf "$NEW_DATA"

sudo install -d -o postgres -g postgres -m 700 "$NEW_DATA"

sudo -u postgres "$NEW_BIN/initdb" \

  --pgdata="$NEW_DATA" \

  --encoding=UTF8 \

  --locale=en_US.UTF-8 \

  --data-checksums

Only remove NEW_DATA at this point, while it is a newly initialized empty cluster. Do not start PostgreSQL 17 yet.

6. Stop database clients and PostgreSQL 16

Stop applications, scheduled jobs, backup jobs, monitoring, and connection pools. Review remaining sessions:

Check active PostgreSQL client connections
sudo -u postgres "$OLD_BIN/psql" -X -c "

SELECT pid, usename, datname, application_name, client_addr, state

FROM pg_stat_activity

WHERE pid <> pg_backend_pid()

ORDER BY datname, usename;

"

Stop both database services:

Stop PostgreSQL 16 and PostgreSQL 17
sudo systemctl stop postgresql-16

sudo systemctl stop postgresql-17 2>/dev/null || true

sudo systemctl is-active postgresql-16

sudo systemctl is-active postgresql-17

Both services should report inactive.

7. Run the compatibility check

Create a working directory owned by postgres, then run the PostgreSQL 17 pg_upgrade binary with --check:

Check PostgreSQL 16 and 17 cluster compatibility
sudo install -d -o postgres -g postgres -m 700 "$UPGRADE_DIR"

 
sudo -iu postgres bash <<'EOF'

cd /var/lib/pgsql/upgrade-16-to-17

/usr/pgsql-17/bin/pg_upgrade \

  --check \

  --old-bindir=/usr/pgsql-16/bin \

  --new-bindir=/usr/pgsql-17/bin \

  --old-datadir=/var/lib/pgsql/16/data \

  --new-datadir=/var/lib/pgsql/17/data \

  --username=postgres

EOF

Do not continue unless the final result is:

Expected pg_upgrade compatibility result
Clusters are compatible

For example, an error about not loading $libdir/vector means the PostgreSQL 17 pgvector library is missing. Install pgvector_17, then rerun the check.

8. Run the PostgreSQL major upgrade

Run the real upgrade in copy mode:

Upgrade PostgreSQL 16 to PostgreSQL 17
sudo -iu postgres bash <<'EOF'

cd /var/lib/pgsql/upgrade-16-to-17

/usr/pgsql-17/bin/pg_upgrade \

  --old-bindir=/usr/pgsql-16/bin \

  --new-bindir=/usr/pgsql-17/bin \

  --old-datadir=/var/lib/pgsql/16/data \

  --new-datadir=/var/lib/pgsql/17/data \

  --username=postgres \

  --jobs="$(nproc)" \

  --copy

EOF

Do not use --no-sync on a production server. Read the complete output and run any required extension-update or index-rebuild scripts exactly as printed. Keep the generated old-cluster deletion script, but do not run it yet.

9. Migrate configuration

Do not overwrite PostgreSQL 17's entire postgresql.conf with the PostgreSQL 16 file. Compare the files and manually reapply custom settings that remain valid:

Compare PostgreSQL 16 and 17 configuration files
sudo diff -u \

  "$NEW_DATA/postgresql.conf" \

  "$OLD_DATA/postgresql.conf" \

  | less

Review settings such as listen_addresses, port, memory limits, WAL settings, logging, SSL, and shared_preload_libraries. Any library named in shared_preload_libraries must also be installed for PostgreSQL 17.

The client authentication files can normally be copied after review:

Copy PostgreSQL client authentication configuration
sudo cp -a "$OLD_DATA/pg_hba.conf" "$NEW_DATA/pg_hba.conf"

sudo cp -a "$OLD_DATA/pg_ident.conf" "$NEW_DATA/pg_ident.conf"

sudo chown postgres:postgres \

  "$NEW_DATA/pg_hba.conf" \

  "$NEW_DATA/pg_ident.conf"

Review postgresql.auto.conf separately and reapply only valid PostgreSQL 17 settings:

Review automatically configured PostgreSQL settings
sudo cat "$OLD_DATA/postgresql.auto.conf"

Check key configuration values before starting the service:

Validate the PostgreSQL 17 configuration
sudo -u postgres "$NEW_BIN/postgres" -D "$NEW_DATA" -C data_directory

sudo -u postgres "$NEW_BIN/postgres" -D "$NEW_DATA" -C listen_addresses

10. Start and verify PostgreSQL 17

Switch the enabled service and start PostgreSQL 17:

Enable and start PostgreSQL 17
sudo systemctl disable postgresql-16

sudo systemctl enable postgresql-17

sudo systemctl start postgresql-17

sudo systemctl status postgresql-17 --no-pager

Verify the server identity, data directory, encoding, and locale:

Verify the upgraded PostgreSQL 17 cluster
sudo -u postgres "$NEW_BIN/psql" -X -c "

SELECT version();

SHOW data_directory;

SHOW server_encoding;

SHOW lc_collate;

SHOW lc_ctype;

"

 
sudo -u postgres "$NEW_BIN/pg_isready"

Check that all databases are present:

List upgraded PostgreSQL databases and sizes
sudo -u postgres "$NEW_BIN/psql" -X -c "

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size

FROM pg_database

WHERE datallowconn

ORDER BY datname;

"

11. Update extensions

First run any extension-update script produced by pg_upgrade. Then list the installed extension versions:

Check extensions after the PostgreSQL upgrade
sudo -iu postgres bash <<'EOF'

while IFS= read -r database; do

  echo "=== Database: $database ==="

  /usr/pgsql-17/bin/psql -X -d "$database" -c \

    "SELECT extname, extversion FROM pg_extension ORDER BY extname;"

done < <(

  /usr/pgsql-17/bin/psql -XAtc \

    "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate"

)

EOF

Update pgvector in each database that uses it:

Update and verify the pgvector extension
sudo -u postgres "$NEW_BIN/psql" \

  --dbname=your_database \

  --command="ALTER EXTENSION vector UPDATE;"

 
sudo -u postgres "$NEW_BIN/psql" \

  --dbname=your_database \

  --command="SELECT extversion FROM pg_extension WHERE extname = 'vector';"

Update other extensions only where they are installed:

Update PostgreSQL contrib extensions
ALTER EXTENSION pg_trgm UPDATE;

ALTER EXTENSION btree_gist UPDATE;

12. Regenerate optimizer statistics

pg_upgrade does not transfer optimizer statistics. Regenerate them in stages before returning the server to normal traffic:

Regenerate PostgreSQL optimizer statistics in stages
sudo -u postgres "$NEW_BIN/vacuumdb" \

  --all \

  --analyze-in-stages \

  --jobs="$(nproc)"

After normal operation resumes, run a complete analyze:

Run a complete PostgreSQL analyze
sudo -u postgres "$NEW_BIN/vacuumdb" \

  --all \

  --analyze-only \

  --jobs="$(nproc)"

13. Validate before deleting PostgreSQL 16

Review the service log:

Check recent PostgreSQL 17 service logs
sudo journalctl \

  -u postgresql-17 \

  --since "1 hour ago" \

  --no-pager

Check for invalid indexes in each important database:

Find invalid PostgreSQL indexes
sudo -u postgres "$NEW_BIN/psql" -X -d your_database -c "

SELECT n.nspname AS schema_name, c.relname AS index_name

FROM pg_index i

JOIN pg_class c ON c.oid = i.indexrelid

JOIN pg_namespace n ON n.oid = c.relnamespace

WHERE NOT i.indisvalid

ORDER BY 1, 2;

"

Validate database and table counts, important row counts, application login, reads and writes, scheduled jobs, backups, monitoring, connection pools, and pgvector searches and indexes. Keep the PostgreSQL 16 packages and /var/lib/pgsql/16/data for an agreed retention period.

Roll back before production writes

Because the upgrade used --copy, PostgreSQL 16 remains unchanged. Before PostgreSQL 17 receives production writes, rollback is straightforward:

Roll back to PostgreSQL 16 before new writes
sudo systemctl stop postgresql-17

sudo systemctl disable postgresql-17

sudo systemctl enable postgresql-16

sudo systemctl start postgresql-16

sudo systemctl status postgresql-16 --no-pager

After clients write to PostgreSQL 17, restarting PostgreSQL 16 would discard those new writes. Stop all clients and plan how to export or reconcile the PostgreSQL 17 changes before attempting rollback.

Final cleanup

Only after PostgreSQL 17 has passed validation and fresh backups are working, disable PostgreSQL 16 permanently:

Disable the old PostgreSQL 16 service
sudo systemctl disable postgresql-16

After the retention period, remove the old packages. Review the DNF transaction before accepting it so that libraries required by other software are not removed:

Remove the old PostgreSQL 16 packages
sudo dnf remove \

  postgresql16-server \

  postgresql16-contrib \

  postgresql16-devel

Finally, delete the old cluster only when you are certain it is no longer needed. Use the deletion script generated by pg_upgrade, or archive the old data directory according to your backup and retention policy.

Comments