PostgreSQL 14 End of Support: Rehearse the Upgrade Before Switching Writes

Imagine your app writes orders through an API and updates their status from a worker. The API has a database connection string you can find in seconds. The worker has its own pool and a separate deployment. If you move PostgreSQL, both need to switch. The database could upgrade cleanly and you could still leave one of those writers on the wrong side.

Ask the database your app connects to

Run a read-only query through an authorized connection to the endpoint the app uses. If there are separate read and write endpoints, check each and record which is which. Your terminal’s psql --version identifies a client binary; it doesn’t identify the server behind a pool or proxy.

SHOW server_version;
SELECT current_database(), pg_is_in_recovery();
SELECT extname, extversion FROM pg_extension ORDER BY extname;

Collect the extension list in each application database. Record any external extension packages, preload libraries, tablespaces and replicas alongside the version. Then choose a supported major version that your app, drivers and extensions can all use. PostgreSQL 18 is an example below; use the current minor of your chosen supported major, and review the intervening release notes. The versioning policy explains why installing another PostgreSQL 14 minor doesn’t move your data to a new major.

Pick a rehearsal that matches your move

The PostgreSQL upgrade guide describes several routes. A logical dump and restore is worth considering if you are rebuilding the stack on another host and can allow the restore window. pg_upgrade is a candidate for compatible installations where old and new clusters can be made available together. Logical replication is a separate migration design: it can keep supported table data moving while you prepare, but schema changes and sequence state need separate handling. Don’t decide on a method just by comparing how long the copy step sounds.

For a pg_upgrade rehearsal, use a consistent, privately restored copy of the PostgreSQL 14 cluster and an empty initialized target. Keep production credentials, network access and outbound integrations out of that environment. Install both sets of binaries and the target-compatible extension libraries, and check tablespace mappings and disk headroom. If you first need to prove that a backup can be recovered, use Krova’s restore drill; that answers a different question from whether the new major runs your application.

Here is an example preflight for a Linux rehearsal with both clusters stopped and their configuration files in the illustrated directories. Substitute verified paths and run it as the authorized database OS account from a writable directory. This uses the target version’s pg_upgrade and the default copy mode. If your installation separates configuration and data paths, follow the target version’s pg_upgrade instructions for your layout.

/opt/postgresql/18/bin/pg_upgrade --check \
  --old-bindir=/opt/postgresql/14/bin \
  --new-bindir=/opt/postgresql/18/bin \
  --old-datadir=/srv/rehearsal/pg14 \
  --new-datadir=/srv/rehearsal/pg18

A pass here answers a narrow question about the clusters you supplied. Read the diagnostics and fix the actual incompatibilities before going further. If you plan a different file-transfer mode, include its flag in the check so that mode’s constraints are tested too. Link mode changes the recovery options: once the upgraded cluster starts, the old cluster is unsafe to restart because files were shared. Keep an independently recoverable backup and choose your mode with recovery in mind.

Run the business operation, not just the database check

Complete the chosen upgrade in the rehearsal environment using the target manual, including its post-upgrade processing. For the hypothetical orders app, connect with the same role and pool configuration the API uses, create a synthetic order, then have a test worker read and update it. Reconnect and confirm the status persists. Disable real email, payments and webhooks. Compare a few representative queries and note whether permissions, extensions or query plans need attention.

• Record the compatibility check and the exact target build, including extensions.
• Record the end-to-end application result, the time to usable service and the cleanup or post-upgrade steps still outstanding.
• Record the cutover decision: which writers will be paused, how their queues and connection pools will be drained or redirected, and which check must pass before reopening writes.

At production cutover, establish one writable authority. Pause the old writers and verify they’re stopped, complete the final transfer or catch-up for your method, and check the new endpoint before resuming. A rollback plan needs two answers: what happens before the first real write on the target, and what happens after. Once the target accepts new data, pointing clients at the old cluster can lose those writes unless you have a tested way to reconcile them. An old cluster you can boot is not necessarily a database you can safely return traffic to.

If your app uses physical standbys, logical replication, or an orchestrator, add its specific cutover and rebuild steps to the plan. This preflight is a way to discover missing pieces while production is still intact, not a substitute for the runbook you’ll use on the day.

Creation context: This AI-assisted guide uses a hypothetical application and examples based on PostgreSQL documentation. No upgrade or application test was performed for this article. The cover is a generated illustration.