Migrating to PostgreSQL 18: What Changed and How to Prepare

PostgreSQL 18.0 has arrived with major improvements in performance, SQL features, and security. But as with every major release, upgrading requires more than just running pg_upgrade or restoring a dump. Version 18 introduces several compatibility changes that may affect existing applications and administrative workflows.

This post highlights what’s new in the migration process, why it matters, and how you can prepare your systems for a smooth transition.

As with prior releases, you have three supported methods to move to PostgreSQL 18:

• Dump/restore using pg_dumpall or pg_dump/pg_restore. This provides the cleanest migration but comes with downtime proportional to your dataset size.
• In-place upgrade using pg_upgrade. This is the fastest path for large clusters but requires compatible binary builds and storage settings.
• Logical replication. This allows a rolling migration with minimal downtime but needs careful planning around replication slots, publications, and schema compatibility.

Whichever path you choose, plan a dress rehearsal in staging before touching production. Several changes in PostgreSQL 18 affect both cluster initialization and day-to-day operations.

Key Incompatibilities and Their Implications

1. Checksums Enabled by Default

New clusters created with initdb now enable data checksums automatically. Checksums protect against corruption but come with a slight performance cost. If you’re upgrading with pg_upgrade, the old and new clusters must match checksum settings. Use the new --no-data-checksums option at initialization if you need consistency with older non-checksum clusters, but weigh the operational risks of running without corruption detection.

2. Time Zone Abbreviation Precedence

The system now favors the current session’s time zone abbreviations before consulting the server’s timezone_abbreviations setting. Previously, the global setting always took precedence. Applications relying on ambiguous time zone abbreviations (like “CST”) may behave differently. The safest approach is to use full time zone names such as America/Chicago or Asia/Shanghai.

3. MD5 Authentication Deprecated

MD5 password authentication is officially on its way out. While still supported, PostgreSQL 18 emits warnings when you create or alter roles with MD5 passwords. You can suppress the warnings by setting md5_password_warnings = off, but the real solution is to migrate to SCRAM-SHA-256 or OAuth. Both are stronger and better aligned with modern security practices.

4. VACUUM and ANALYZE Process Children by Default

In prior versions, running VACUUM or ANALYZE on a parent table ignored its child tables unless you specified them explicitly. Version 18 flips the default: child tables are now included automatically. Use the new ONLY option if you want to restrict maintenance to a parent alone. If you have automation or cron jobs for table maintenance, check them carefully—your workload may change.

5. COPY FROM EOF Marker Behavior

When reading CSV files, COPY FROM no longer treats \. as an end-of-file marker unless it appears alone on a line. This change prevents accidental truncation of data but may trip up older client workflows. Note that psql still treats \. as EOF when reading from STDIN. If you use \copy with CSV input, especially from older clients, test thoroughly against a PostgreSQL 18 server.

6. Partitioned Tables Can’t Be Unlogged

PostgreSQL now disallows unlogged partitioned tables entirely. Previously, the commands to set partitions logged or unlogged did nothing, which created silent inconsistencies. If you have schemas that assumed unlogged partitions for performance, you’ll need to rework them with logged tables or consider alternative approaches like unlogged children.

7. AFTER Trigger Execution Role Clarified

AFTER triggers are now executed as the role active when the trigger event was queued, not the role at commit time. This matters if your applications switch roles inside a transaction—for example, security definer functions that escalate privileges. Review your triggers to ensure the correct role semantics are applied.

8. Legacy Rule Privileges Removed

PostgreSQL has carried vestigial support for “rule privileges” since version 8.2, but they’ve never actually worked. These entries are now gone. If you have tooling that parses system catalogs or permission dumps, expect them to look slightly different.

9. Backend Memory Context Reporting Updated

System views for backend memory contexts have changed. The column pg_backend_memory_contexts.parent has been removed (replaced by path), and context levels are now one-based instead of zero-based. Update monitoring dashboards or scripts that track memory usage.

10. Full-Text Search Collation Behavior

Full-text search no longer always relies on libc for configuration files and dictionaries. Instead, it uses the cluster’s default collation provider, which may be ICU or builtin. If you upgrade a cluster with non-libc collations, some text search or pg_trgm behavior may change. The release notes recommend reindexing full-text search and trigram indexes after upgrade.

Preparing for the Upgrade

Here’s a practical checklist before upgrading to PostgreSQL 18:

1. Decide on checksum policy. If your current cluster runs without checksums, plan whether to adopt them or preserve the old configuration.
2. Audit authentication. Move remaining MD5-based users to SCRAM or OAuth.
3. Check maintenance scripts. Update VACUUM and ANALYZE calls that depend on inheritance behavior.
4. Test COPY workflows. Validate CSV imports against PostgreSQL 18 servers, especially with older client versions.
5. Review partitioning. Remove assumptions about unlogged partitioned tables.
6. Inspect triggers. Confirm AFTER triggers still execute with the expected role.
7. Update monitoring. Adjust memory context queries and dashboards.
8. Reindex text search. If using ICU or builtin collations, reindex full-text search and trigram indexes after upgrading.

PostgreSQL 18 offers major advances in performance, security, and feature depth, but successful adoption requires attention to these compatibility changes. By planning ahead—revising scripts, migrating authentication, and validating workloads—you can make the transition smooth and take full advantage of the new release.

For full details, see the official PostgreSQL 18 release notes.