Postgresql Upgrade 14 to 18

PostgreSQL 14 is approaching the end of its support life, so now is a good time to plan your next major-version upgrade. In this guide, we will walk through a PostgreSQL 14 to PostgreSQL 18 upgrade on Ubuntu, from the initial health checks and backup to migration, validation and cleanup.

The procedure uses Ubuntu's pg_upgradecluster utility, which runs PostgreSQL's pg_upgrade process underneath. The example assumes that PostgreSQL 14 is installed as the 14/main cluster and that PostgreSQL 18 packages are available from the official PostgreSQL APT repository.

Environment covered in this guide

Source: PostgreSQL 14, cluster 14/main
Target: PostgreSQL 18
Operating system: Ubuntu 22.04, 24.04 or 26.04
Upgrade method: pg_upgradecluster -m upgrade (copy mode)
Downtime: required while applications are stopped and the upgrade is performed

Important: Test the complete procedure on a staging copy before using it in production. Confirm the commands and options against the PostgreSQL and postgresql-common versions installed on your server.

Why upgrade from PostgreSQL 14 to PostgreSQL 18?

PostgreSQL supports each major version for five years. PostgreSQL 14 reaches community end of life on 12 November 2026. PostgreSQL 18 was released on 25 September 2025 and is supported until 14 November 2030.

The good news is that you can upgrade directly from PostgreSQL 14 to PostgreSQL 18. You do not need to install versions 15, 16 and 17 as intermediate upgrade steps. You should, however, review the compatibility changes introduced in those releases before starting.

PostgreSQL 18 also brings several improvements, including an asynchronous I/O subsystem, B-tree skip scan, uuidv7(), virtual generated columns and OAuth authentication. Its pg_upgrade improvements also help retain optimizer statistics, reducing the amount of performance preparation required after an upgrade.

Understand PostgreSQL clusters on Ubuntu

Ubuntu manages PostgreSQL installations through the postgresql-common package. Multiple major versions can exist on the same server, and each cluster is identified by a version and a name. For example, 14/main means PostgreSQL 14, cluster name main.

These are the main locations used in this example:

Item	PostgreSQL 14	PostgreSQL 18
Binaries	`/usr/lib/postgresql/14/bin`	`/usr/lib/postgresql/18/bin`
Data directory	`/var/lib/postgresql/14/main`	`/var/lib/postgresql/18/main`
Configuration	`/etc/postgresql/14/main/`	`/etc/postgresql/18/main/`
Log file	`/var/log/postgresql/postgresql-14-main.log`	`/var/log/postgresql/postgresql-18-main.log`

During the upgrade, pg_upgradecluster creates the new cluster, carries over and adjusts configuration, migrates the data, and assigns the original port to PostgreSQL 18. The old cluster is moved to another port and is not automatically deleted.

Which upgrade method should you choose?

Dump and restore: uses logical export and import. It is usually the slowest option and needs additional disk space.
Copy mode: uses pg_upgrade to copy the data files. It needs additional disk space, but keeps the old cluster intact. This is the method used in this article.
Link mode: uses hard links and is very fast, but the old cluster cannot safely be used after the new cluster is started.
Clone mode: uses filesystem reflinks where supported, such as on suitable XFS or Btrfs filesystems. The old cluster remains intact.

For a production upgrade where rollback flexibility matters, copy mode is a sensible choice if sufficient disk space is available. Link, clone and swap modes have additional filesystem and recovery requirements; review the official documentation before choosing one.

Phase 1: Pre-upgrade checks and backup

PostgreSQL 14 can remain online while you complete this phase. Collect the information you will need to validate the new cluster later.

Step 1: Identify the current installation

Check the Ubuntu version, list the PostgreSQL clusters and confirm the running database version:

lsb_release -a
pg_lsclusters
sudo -u postgres psql -c "SELECT version();"

Confirm that the expected cluster is 14/main and is online. The pg_lsclusters output also shows its port, owner and data directory.

Step 2: Take an inventory of databases and extensions

First, record the size of each database:

sudo -u postgres psql -c "
SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datallowconn
ORDER BY pg_database_size(datname) DESC;"

Next, list the installed extensions in every database. Each third-party extension must have a compatible PostgreSQL 18 package available.

for db in $(sudo -u postgres psql -Atc \
"SELECT datname FROM pg_database WHERE datallowconn")
do
    echo "== $db"
    sudo -u postgres psql -d "$db" -Atc \
    "SELECT extname, extversion
     FROM pg_extension
     ORDER BY 1"
done

For important databases, capture a small baseline of table statistics and object counts:

sudo -u postgres psql -d your_db -c "
SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC
LIMIT 20;"

sudo -u postgres psql -d your_db -Atc \
"SELECT count(*) FROM pg_class;" \
> /tmp/pg14_objcount.txt

Finally, check the current data size and available filesystem capacity:

sudo du -sh /var/lib/postgresql/14/main
df -h /var/lib/postgresql

Copy mode needs enough free space for the new data copy. Use the current data size as a planning baseline and allow additional room for operational requirements.

Step 3: Check data checksums

PostgreSQL 18 enables data checksums by default when initializing a new cluster. The old and new clusters must have matching checksum settings for pg_upgrade.

sudo -u postgres psql -c "SHOW data_checksums;"

Record whether the result is on or off. The Ubuntu pg_upgradecluster workflow handles creation of the target cluster with a compatible setting. If you use the manual method later in this article, you must account for the setting yourself.

Step 4: Check compatibility changes

Some database objects and application configurations may be affected by changes between PostgreSQL 14 and 18.

Check for unsupported reg* data types in table columns. Run this query in each database:

SELECT n.nspname, c.relname, a.attname, a.atttypid::regtype
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE a.atttypid IN (
    'regcollation'::regtype,
    'regconfig'::regtype,
    'regdictionary'::regtype,
    'regnamespace'::regtype,
    'regoper'::regtype,
    'regoperator'::regtype,
    'regproc'::regtype,
    'regprocedure'::regtype
)
AND c.relkind IN ('r','m')
AND NOT a.attisdropped
AND n.nspname NOT IN ('pg_catalog','information_schema');

The expected result is zero rows. If the query returns rows, review those columns and resolve the incompatibility before upgrading. The types regclass, regrole and regtype are not part of this particular list.

Identify users with MD5 password hashes.

sudo -u postgres psql -c \
"SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';"

sudo grep -nE '^[^#].*md5' \
/etc/postgresql/14/main/pg_hba.conf

MD5 authentication is deprecated in PostgreSQL 18, although it continues to work. Plan a controlled migration to SCRAM after confirming that application drivers support it.

Also review custom backup scripts, configuration files, PL/Python functions, extension dependencies, monitoring queries and functions used by expression indexes or materialized views. In particular, review the changes introduced in PostgreSQL 15 and 17, including removed configuration parameters and renamed pg_stat_statements columns.

Step 5: Take a full backup

Take a backup before installing or upgrading the target cluster. The following example creates a logical backup and a separate archive of the PostgreSQL 14 configuration:

sudo mkdir -p /backup
sudo chown postgres:postgres /backup

sudo -u postgres pg_dumpall \
-f /backup/pg14_full_$(date +%F).sql

sudo tar czf \
/backup/pg14_conf_$(date +%F).tar.gz \
/etc/postgresql/14/main

ls -lh /backup

For a large production database, also consider per-database custom-format dumps, a physical backup using pg_basebackup or your established backup tool, and a VM snapshot where appropriate. Verify that the backups are usable by testing a restore.

Phase 2: Install PostgreSQL 18

Step 6: Configure the official PostgreSQL APT repository

Check whether the PGDG repository is already configured:

ls /etc/apt/sources.list.d/ | grep -i pgdg

If it is not configured, install the required packages and run the official repository setup script:

sudo apt install -y postgresql-common ca-certificates
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt update

Step 7: Install PostgreSQL 18 and extension packages

Install the PostgreSQL 18 server and client packages:

sudo apt install -y postgresql-18 postgresql-client-18

Install the PostgreSQL 18 package for each third-party extension identified during the inventory. For example, if PostGIS is installed and required:

sudo apt install -y postgresql-18-postgis-3

Then run the package pre-check:

sudo pg_upgradecluster -v 18 --check 14 main

This checks whether the required packages are installed. Database-level compatibility checks are also performed when the upgrade starts.

Step 8: Remove an automatically created empty PostgreSQL 18 cluster

Installing PostgreSQL 18 may create an empty 18/main cluster, often on port 5433. Check first:

pg_lsclusters

Only if you confirm that 18/main is the newly created empty cluster, remove it:

sudo pg_dropcluster 18 main --stop
pg_lsclusters

Be careful: pg_dropcluster permanently deletes the specified cluster's data directory. Remove only the empty PostgreSQL 18 cluster. Do not drop 14/main.

Phase 3: Run the upgrade

This is where the maintenance downtime begins. Coordinate with the application team before proceeding.

Step 9: Stop application traffic

Stop application services, scheduled jobs, connection poolers and any other processes that can write to PostgreSQL. Check for remaining client sessions:

sudo -u postgres psql -c "
SELECT pid, usename, datname, application_name, state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
AND pid <> pg_backend_pid();"

Step 10: Stop PostgreSQL 14

sudo pg_ctlcluster 14 main stop
pg_lsclusters

Confirm that the PostgreSQL 14 cluster is down before starting the upgrade.

Step 11: Execute pg_upgradecluster

Run the following command to upgrade the cluster using copy mode, with four parallel jobs:

cd /tmp

sudo pg_upgradecluster \
-v 18 \
-m upgrade \
-j 4 \
--start \
14 main

Here, -v 18 selects the target version, -m upgrade selects copy mode, -j 4 specifies the parallel job count, and --start tells the utility to start the new cluster after the upgrade.

Wait for the process to finish and check its final output. A successful run reports that the upgraded cluster should be checked and that the old cluster can be removed once you are satisfied that the new one works.

If the upgrade fails in copy mode, PostgreSQL 14 remains untouched. The partially created PostgreSQL 18 cluster is normally removed automatically. You can restart the old cluster with:

sudo pg_ctlcluster 14 main start

Review the error, resolve the cause and retry. The --keep-on-error option can be used when you need to retain the failed target cluster for diagnosis.

Step 12: Confirm the cluster layout

After the upgrade, check the cluster list:

pg_lsclusters

The expected arrangement is PostgreSQL 18 online on port 5432 and PostgreSQL 14 stopped on another port. The exact port assigned to the old cluster may vary.

Phase 4: Post-upgrade validation

Do not return the application to service just because the upgrade command completed. Validate the database and its dependencies first.

Step 13: Verify the server and database objects

sudo -u postgres psql -c "SELECT version();"
sudo -u postgres psql -c "SHOW port;"
sudo -u postgres psql -c "\l+"

sudo -u postgres psql -d your_db -Atc \
"SELECT count(*) FROM pg_class;"

sudo tail -n 50 \
/var/log/postgresql/postgresql-18-main.log

Compare the database list, sizes and object counts with the inventory from Step 2. Run representative application queries against real tables and review the PostgreSQL 18 log for errors.

Step 14: Refresh optimizer statistics

PostgreSQL 18 retains most optimizer statistics during the upgrade, but some statistics are not transferred. Run the following commands as recommended in the upgrade guide:

sudo -u postgres vacuumdb \
--all --analyze-in-stages --missing-stats-only -j 4

sudo -u postgres vacuumdb \
--all --analyze-only -j 4

The first command fills in missing statistics in stages. The second refreshes cumulative statistics used by autovacuum and autoanalyze. The application can generally be reconnected after the first command completes.

Step 15: Update extensions

Check whether installed extensions have newer versions available:

SELECT name, installed_version, default_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
AND installed_version <> default_version;

For each extension that requires an update, connect to the relevant database and run the appropriate command. For example:

ALTER EXTENSION pg_stat_statements UPDATE;

If pg_upgrade generated an update_extensions.sql script, you can use that script instead.

Step 16: Check collation versions

Text sorting and comparison can depend on the operating system's libc or ICU libraries. If the stored and actual collation versions differ, review the affected indexes and rebuild them as required.

SELECT datname,
       datcollversion,
       pg_database_collation_actual_version(oid) AS os_version
FROM pg_database
WHERE datallowconn;

If a version mismatch is confirmed, rebuild the affected indexes. For example, you may use REINDEX DATABASE dbname; for the relevant database, then refresh the recorded collation version:

REINDEX DATABASE dbname;
ALTER DATABASE dbname REFRESH COLLATION VERSION;

Review the PostgreSQL 18 guidance for full-text-search and pg_trgm indexes where ICU or builtin collation providers are used.

Step 17: Review configuration and authentication

Compare the old and new configuration files. Pay attention to custom settings, authentication rules, obsolete parameters and settings that may have changed between releases.

sudo diff /etc/postgresql/14/main/postgresql.conf \
/etc/postgresql/18/main/postgresql.conf

sudo diff /etc/postgresql/14/main/pg_hba.conf \
/etc/postgresql/18/main/pg_hba.conf

You can also review non-default runtime settings:

sudo -u postgres psql -c "
SELECT name, setting, source
FROM pg_settings
WHERE source NOT IN ('default','override')
ORDER BY name;"

For users still using MD5 password hashes, plan a migration to SCRAM. Set password_encryption to scram-sha-256, reset the affected users' passwords, update the corresponding pg_hba.conf rules and reload the configuration. Confirm client-library compatibility before changing production authentication.

Step 18: Reconnect applications and update tools

Once validation is complete, start application services, connection poolers and scheduled jobs. Monitor the PostgreSQL log and pg_stat_activity while application traffic returns.

Also update backup jobs, monitoring queries and client utilities to PostgreSQL 18-compatible versions. If the environment uses streaming replicas, plan to rebuild the standbys from the upgraded primary, for example with pg_basebackup.

Step 19: Enable data checksums (optional)

If checksums are not enabled and you want to enable them on the upgraded cluster, schedule a separate maintenance window. Enabling checksums requires a clean shutdown and rewrites every data file, so the duration depends on the size of the cluster.

sudo pg_ctlcluster 18 main stop

sudo -u postgres \
/usr/lib/postgresql/18/bin/pg_checksums \
--enable --progress \
-D /var/lib/postgresql/18/main

sudo pg_ctlcluster 18 main start

sudo -u postgres psql -c "SHOW data_checksums;"

Phase 5: Remove the old PostgreSQL cluster

Step 20: Remove PostgreSQL 14 after the observation period

Keep PostgreSQL 14 until PostgreSQL 18 has passed the agreed observation period, application testing is complete and backups of the new cluster have been successfully verified.

When you are ready to remove the old cluster, run:

sudo pg_dropcluster 14 main
pg_lsclusters

You can optionally remove the old packages as well:

sudo apt purge postgresql-14 postgresql-client-14

Final warning: Removing the old cluster permanently deletes its data directory. After this step, returning to PostgreSQL 14 requires restoring from backup.

Rollback plan: What if the upgrade does not work?

The rollback procedure depends on the upgrade mode and whether PostgreSQL 18 has been started.

With copy mode, if PostgreSQL 18 is running but you need to return to PostgreSQL 14, stop PostgreSQL 18 first:

sudo pg_ctlcluster 18 main stop

Make sure PostgreSQL 14 is configured to use the original application port, such as 5432. Configure PostgreSQL 18 to use a different port if you need to retain it for investigation. Then start PostgreSQL 14:

sudo pg_ctlcluster 14 main start
pg_lsclusters

Remember: transactions written to PostgreSQL 18 after the upgrade are not automatically copied back to PostgreSQL 14. Plan how to handle those writes before deciding to roll back. In link mode, once the new cluster has been started, restore the old environment from backup rather than attempting to use the old data directory.

Common upgrade problems and troubleshooting

Symptom	Likely cause and action
Target cluster 18/main already exists	An empty cluster was automatically created. Confirm it is empty, then remove it with `pg_dropcluster 18 main --stop`.
Could not restart old cluster	Check whether PostgreSQL 14 is still running or another process is using the port.
Checksum mismatch	The source and target checksum settings differ. Initialize the target with compatible settings.
Could not load library .so	Install the matching PostgreSQL 18 extension package.
Error involving reg* data types	Review the affected columns and resolve the compatibility issue before retrying.
Expression index creation fails	Review functions that depend on `search_path`; schema-qualify names or set an appropriate function search path.
PostgreSQL 18 does not start	Check the log for obsolete or unrecognized configuration parameters.
Queries are slow after upgrade	Check optimizer statistics and complete the analysis steps described earlier.

For this Ubuntu example, the PostgreSQL 18 log is located at:

/var/log/postgresql/postgresql-18-main.log

Manual upgrade using pg_upgrade

If you need more control or are working with a custom PostgreSQL installation, you can run pg_upgrade directly instead of using the Ubuntu wrapper. In this approach, you are responsible for creating the new cluster, matching locale and checksum settings, carrying over configuration and managing the ports.

Create the new cluster. The following example disables checksums because it assumes that the old cluster has checksums disabled. Use the actual locale of your source cluster:

sudo pg_createcluster 18 main \
--locale=en_US.UTF-8 \
-- --no-data-checksums

Stop both clusters:

sudo pg_ctlcluster 14 main stop
sudo pg_ctlcluster 18 main stop

Run the check-only command first:

cd /tmp

sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade \
-b /usr/lib/postgresql/14/bin \
-B /usr/lib/postgresql/18/bin \
-d /var/lib/postgresql/14/main \
-D /var/lib/postgresql/18/main \
-o '-c config_file=/etc/postgresql/14/main/postgresql.conf' \
-O '-c config_file=/etc/postgresql/18/main/postgresql.conf' \
-j 4 --check

If the checks succeed, run the same command without --check to perform the upgrade. Then configure the ports, review the configuration and start PostgreSQL 18. Continue with the validation steps in this article.

Final production checklist

Before closing the change request, make sure the following activities are complete:

Confirm the source version, cluster name and port.
Inventory databases, extensions, object counts and disk usage.
Check checksum settings and version compatibility.
Take backups and verify that they can be restored.
Install PostgreSQL 18 and all required extension packages.
Stop application traffic before the upgrade.
Run pg_upgradecluster and verify the new cluster.
Refresh optimizer statistics and update extensions.
Review collation, authentication and configuration settings.
Validate application queries, monitoring and backup jobs.
Retain PostgreSQL 14 for the agreed observation period.
Remove the old cluster only after approval.

Conclusion

A PostgreSQL 14 to PostgreSQL 18 upgrade is manageable when it is treated as a complete migration project rather than a single command. The pg_upgradecluster utility simplifies the cluster migration, but the DBA still needs to validate extensions, configuration, authentication, optimizer statistics, application connectivity and recovery procedures.

For production systems, rehearse the complete process on staging, agree on the downtime window, verify backups and document the rollback plan before making the change.

A successful major-version upgrade is not just about moving database files to a new version. It is about ensuring that applications, performance, monitoring, backup and recovery continue to work as expected.

Official references

Commands and version-specific behavior can change. Check the documentation and the installed postgresql-common manual on your own server before performing a production upgrade.

Thanks for reading!