PostgreSQL 18: What's New and How to Upgrade

New Features in PostgreSQL 18

PostgreSQL 18 focuses on performance, replication, and partitioning rather than headline new syntax.

• Query parallelism: more complex queries can now execute across multiple CPU cores, which helps analytical workloads and large data sets in particular.
• Logical replication: changes can now be applied in parallel on the subscriber side, cutting replication lag in high-transaction environments.
• Declarative partitioning: more flexible management of partitioned tables, with easier maintenance and better performance.
• Security and authentication: more granular control over user roles and permissions, plus improved authentication method support.
• New SQL features: additional SQL-standard features for data manipulation and query writing, closing gaps with the latest standard.

Step-by-Step Upgrade Guide

Upgrading a major PostgreSQL version involves more than a simple apt-get upgrade. It requires migrating data from the old database cluster to a new one, as the internal data storage format can change between major releases. This guide outlines the two primary methods for upgrading: pg_upgrade for in-place upgrades and pg_dump/pg_restore for logical backups.

Method 1: Using pg_upgrade

pg_upgrade is the recommended method for most users because it’s significantly faster and results in minimal downtime. It works by creating hard links or copying files from the old cluster to the new one, avoiding a full data dump and reload.

1. Install the new PostgreSQL version (18): Install the new binaries alongside your existing PostgreSQL 17 installation.
2. Initialize the new cluster: Create a new, empty PostgreSQL 18 data directory using the initdb command.
3. Perform a dry run: Run pg_upgrade with the --check flag to ensure compatibility between your old and new clusters. This is where incompatible extensions or data type mismatches surface, before the actual upgrade.
4. Shut down both clusters: Stop both the old PostgreSQL 17 and the new PostgreSQL 18 services.
5. Run pg_upgrade: Execute the pg_upgrade command to perform the actual upgrade. You must specify the paths to the old and new binary directories and data directories.
6. Analyze the new cluster: After the upgrade, pg_upgrade will generate a script to run vacuumdb --all --analyze-in-stages. This step is important because pg_upgrade does not transfer optimizer statistics, and running this script will rebuild them for optimal performance on the new cluster.
7. Clean up: Once you’ve confirmed that the new cluster is working correctly, you can run the provided script to delete the old cluster’s data files.

Method 2: Using pg_dump and pg_restore

This method involves a full logical backup and restore. It is a good option if you need to migrate to a different server or operating system, or if you want to eliminate database bloat during the upgrade. However, it can be much slower and result in more downtime than pg_upgrade.

1. Backup global objects: Use pg_dumpall -g to back up global objects like users, roles, and tablespaces.
2. Back up individual databases: Use pg_dump to create a logical backup of each database you want to migrate. For best results, use the pg_dump binary from the new PostgreSQL 18 installation to perform the backup from the old cluster.
3. Install PostgreSQL 18: Install the new binaries on your target server.
4. Restore the global objects: Use psql to restore the global objects dump file to the new cluster.
5. Restore individual databases: Use pg_restore to restore the individual database dumps to the new cluster. You can also pipe the pg_dump output directly to psql to avoid creating intermediate files.

Important Considerations for Both Methods:

• Backup: Always perform a full backup of your entire PostgreSQL data directory before attempting any major version upgrade.
• Test Environment: Always test the upgrade process on a non-production system first to ensure a smooth transition and verify that your applications work with the new version.
• Downtime: Plan for downtime, as the database will be unavailable during the upgrade process. Use the --check flag on pg_upgrade to get a rough estimate of the time required.