PostgreSQL 14 to 18 upgrade

The choice between pg_upgrade and logical replication comes down to four things: how much downtime the business can accept at cutover, how large the data set is, whether every extension you run supports PostgreSQL 18, and what rollback position each method leaves you in after cutover. Make these decisions in writing before you pick a method, because each one eliminates options. A team with a short maintenance window and a large cluster will usually land in a different place from a team with a few hours of tolerance and a small database. Confirm your actual downtime budget with the application owners first, since that number drives everything else.

Logical replication works between different major versions, which is why the PostgreSQL upgrading documentation describes it as a route to major upgrades with only several seconds of downtime. The cost of that short cutover is setup and ongoing work. You need to configure publications and subscriptions, confirm that tables have primary keys or replica identities, and handle schema changes, sequences and large objects, which logical replication does not carry in the same way as physical copies. Ask your team to inventory tables without primary keys and any DDL that runs during the migration window, because both affect whether the replication stream can stay consistent until cutover.

pg_upgrade is an in-place style migration that rewrites or links the data directory into the new major version's format. It is generally simpler to reason about because the upgrade is a single controlled operation with a documented check step. Its rollback position is different, though. Depending on the mode you choose, the old cluster may no longer be usable once the new one has started writing data, so you should decide in advance whether you can afford to return to PostgreSQL 14 and how you would replay writes made after cutover. Test the rollback path in a rehearsal rather than assuming it will work on the day.

Extension support is a gating question for either method. Check each extension your applications and monitoring depend on against its PostgreSQL 18 release, and confirm the version with the extension maintainer rather than relying on a general statement. An extension that is missing or behind on 18 can stop a pg_upgrade run outright, or force you to change the target version. Record the results in your compatibility inventory so the decision is traceable.

For the detailed comparison of pg_upgrade copy, link, clone and swap modes, and how each one affects disk use and rollback, read the sibling page on those modes. This section only sets the decision criteria. Once you have a downtime budget, data size estimate, extension list and rollback requirement, you can narrow the method quickly and move to the rehearsal plan.

PostgreSQL 18 deprecates MD5 password authentication in favor of SCRAM, so any role or pg_hba.conf rule that depends on MD5 needs attention before cutover. Start with an inventory on the PostgreSQL 14 source. As a superuser, run a query against pg_authid that selects role names where rolpassword begins with md5. That lists every role whose stored password hash is MD5. Then review pg_hba.conf for lines whose method is md5, and record the database, user and address range each line covers. Give the list to the application owners, because each entry is a login path that must still work on 18.

Passwords have to be re-hashed, and this cannot be done in place. The server stores only the hash, so an MD5 hash cannot be converted to SCRAM without the plaintext. On the source, set password_encryption to scram-sha-256, then change the password for each flagged role with ALTER ROLE. Update the matching entries in your secret store at the same time, so applications do not keep the old value. Role definitions are global objects, and the upgrade documentation describes how they move under pg_upgrade and under logical replication. Logical replication in particular does not copy roles, so confirm how you will create them on the target. Plan this work for the rehearsal, so the cutover run uses the same hashes you will use in production.

Client drivers and connection poolers must support SCRAM-SHA-256 before the target accepts their logins. Inventory every driver, ORM and language library, including JDBC, ODBC, libpq based tools and older scripting modules. Check each version against the authentication methods documentation and your vendor's release notes, because some older builds only negotiate MD5. Connection poolers need separate attention: confirm that PgBouncer, or whichever pooler you run, can authenticate to the server using SCRAM, and check how it stores or passes credentials. Confirm these details in your own environment with the exact versions you deploy. A driver that cannot negotiate SCRAM fails at login, which is an early and visible failure.

Test every login path against the PostgreSQL 18 target before cutover. Include application service accounts, batch and ETL jobs, reporting and BI tools, the replication role, backup tooling, monitoring agents, scheduled scripts and administrator accounts. Test each one from the same host and network path it will use in production, because pg_hba.conf matches on client address as well as role and database. Record the role, database, client version, address and result for each test. Keep the PostgreSQL 14 credentials valid until the cutover is confirmed, so a failed login has a fallback. Any path without a passing test is a blocker for the cutover date.

A full rehearsal on a copy of production data is the main risk control for the PostgreSQL 14 to 18 cutover, because it is the only place where your schema, extensions, roles, data volume and application traffic meet PostgreSQL 18 before users do. Restore a recent production backup to a server that matches the target in CPU, memory and storage class, install the same extensions at the versions you will run, and execute the production runbook step by step. Include the MD5 to SCRAM password change and the connection string switch. A rehearsal that skips steps or uses a smaller dataset will not show you how the production window behaves.

The rehearsal should time and record each step separately: pre-upgrade checks, the upgrade or replication catch-up, post-upgrade statistics work, configuration changes and application reconnection. Log every error and warning, then separate known, expected messages from anything new. On statistics, PostgreSQL 18 pg_upgrade retains planner statistics, so compare execution plans for your most important queries before and after the run. Confirm whether an ANALYZE is still needed, and if so, time it. Measure application latency from the client side, at the percentiles your service levels use, and record connection errors from PgBouncer or any other pooler in the path.

Write pass and fail criteria before the first rehearsal run, and agree them with the business owner and the application team, not only the database team. Useful criteria include the maximum allowed cutover window, zero unexpected errors in the upgrade log, every extension loaded at its target version, row counts and checksums matching on agreed tables, key query plans within an agreed tolerance, latency under agreed thresholds, and successful logins for every application role using SCRAM. Also decide who has authority to stop the cutover and what happens when one criterion fails: stop, roll back, or proceed with a recorded risk acceptance.

Rollback testing means proving that you can return traffic to the PostgreSQL 14 cluster and run the application against it. In rehearsal, route connections back, confirm the old cluster starts cleanly, accepts logins and passes your smoke tests. Any writes accepted by PostgreSQL 18 after cutover will not exist on PostgreSQL 14 unless you replicate them back, so define how much data loss is acceptable and who makes that call. Decide how long the old cluster and its data stay available after go-live. That period is a business and risk decision based on how long problems can take to surface. Confirm in the upgrading documentation whether the pg_upgrade mode you choose leaves the old cluster in a usable state.

Backups and point-in-time recovery are the second half of the rollback plan. Before cutover, take a fresh base backup and confirm that WAL archiving is working, so you can restore to a moment just before the window. In rehearsal, restore that backup to a separate host, replay archived WAL to a chosen target time and time the restore. Repeat the test for a backup taken from PostgreSQL 18 once it is in service. If you use incremental backups with pg_basebackup, rehearse reconstruction with pg_combinebackup at restore time. Record who runs each restore step, what event triggers a rollback, and where the runbook is stored.