PostgreSQL 18 is the new major version, released in late 2025. The right way to upgrade depends on the workload: pg_upgrade for the fast path, logical replication for the no-downtime path. The choice is not always obvious, and the wrong choice costs the team hours of downtime.
Table of contents
- What’s new in 18
- The two paths
- pg_upgrade
- Logical replication
- The migration runbook
- How to verify
- FAQ
What’s new in 18
PostgreSQL 18 ships several notable changes:
- Asynchronous I/O subsystem. A new AIO subsystem that makes sequential scans 2-3x faster on most workloads.
- UUIDv7. Native UUIDv7 support for time-ordered UUIDs.
- OAuth 2.0 authentication. Native OAuth 2.0 support for the client-side authentication.
- Virtual generated columns. Virtual generated columns (the column is computed on read, not stored).
- Stats that survive restarts. The pg_stat_statements statistics now survive a database restart.
- pg_upgrade matching checksum option. pg_upgrade now has an option to match the cluster checksum settings, which is useful for upgrading non-checksum old clusters.
The two paths
The two paths for upgrading:
- pg_upgrade. The fast path. Copies the data files from the old cluster to the new cluster, with the in-place upgrade. Takes 10-60 minutes for a 1 TB database. Requires a brief downtime (the time to copy the data files).
- Logical replication. The no-downtime path. Sets up a logical replication slot from the old cluster to the new cluster, applies the changes in real time, and switches over when the new cluster is caught up. Requires zero downtime, but the new cluster has to be set up and the data has to be replicated.
pg_upgrade
The pg_upgrade path is the right answer when:
- The team can afford 10-60 minutes of downtime.
- The team has a 1 TB or smaller database.
- The team does not want to set up logical replication.
The steps:
- Install PostgreSQL 18 alongside PostgreSQL 17 (or whatever the old version is).
- Stop the old cluster.
- Run
pg_upgrade --old-datadir /var/lib/postgresql/17 --new-datadir /var/lib/postgresql/18 --old-bindir /usr/lib/postgresql/17/bin --new-bindir /usr/lib/postgresql/18/bin. - Start the new cluster.
- Verify the upgrade.
Logical replication
The logical replication path is the right answer when:
- The team cannot afford any downtime.
- The team has a large database (over 1 TB) and the pg_upgrade window is too long.
- The team wants to test the new version with real traffic before the cutover.
The steps:
- Set up the new PostgreSQL 18 cluster.
- Create a publication on the old cluster:
CREATE PUBLICATION my_pub FOR ALL TABLES. - Create a subscription on the new cluster:
CREATE SUBSCRIPTION my_sub CONNECTION '...' PUBLICATION my_pub. - Wait for the new cluster to catch up.
- Stop the writes to the old cluster.
- Wait for the new cluster to catch up one more time.
- Switch the traffic to the new cluster.
- Drop the subscription and the publication.
The migration runbook
The runbook for either path:
- Take a full backup of the old cluster before starting.
- Test the migration on a staging environment first.
- Schedule the migration for a low-traffic window, even if the path is zero-downtime.
- Have a rollback plan: the ability to switch back to the old cluster if the new cluster fails.
- Monitor the new cluster for the first 24-48 hours after the cutover.
How to verify
The verification after the upgrade:
- Compare the row counts in the key tables.
- Compare the query plans for the slow queries.
- Compare the application behavior in the staging and production environments.
- Monitor the new cluster for errors, slow queries, and high CPU for the first 24-48 hours.
- Have a rollback plan: the ability to switch back to the old cluster if the new cluster fails.
FAQ
What is new in PostgreSQL 18?
Asynchronous I/O subsystem (2-3x faster sequential scans), UUIDv7, OAuth 2.0 authentication, virtual generated columns, stats that survive restarts, and pg_upgrade matching checksum option.
How do I upgrade to PostgreSQL 18?
Two paths: pg_upgrade for the fast path (10-60 minutes of downtime), or logical replication for the no-downtime path (zero downtime, but the new cluster has to be set up and the data has to be replicated).
What is the difference between pg_upgrade and logical replication?
pg_upgrade copies the data files in place. Logical replication sets up a publisher on the old cluster and a subscriber on the new cluster. pg_upgrade is faster; logical replication is zero-downtime.
How long does a PostgreSQL upgrade take?
pg_upgrade takes 10-60 minutes for a 1 TB database. Logical replication takes 1-7 days for a 1 TB database, depending on the network and the write rate.
How do I verify a PostgreSQL upgrade?
Compare the row counts in the key tables, compare the query plans for the slow queries, compare the application behavior in the staging and production environments, and monitor the new cluster for the first 24-48 hours.
If you are sizing a database tier for a new project, the RunxBuild hosting calculator is the place to model the line items. The database, the replicas, the storage, the I/O - each one is a separate number, and the team’s mental model for the platform is the sum of those numbers. The RunxBuild dashboard is where the team sees the actual database in one place.