PostgreSQL 14 EOL: Rehearse an Upgrade and Restore the Backup

PostgreSQL 14 is still supported on October 27, 2026, but its final release is scheduled for November 12, 2026. The official versioning policy currently lists 14.24 and 18.6. A major upgrade requires a migration method such as dump/restore or pg_upgrade; swapping binaries over an old data directory is not the procedure.

This small Linux rehearsal moves a synthetic database from 14.24 to 18.6, restores the same pre-upgrade backup into a fresh 14.24 instance, and checks that the original source stayed unchanged. It is an actual executed rehearsal, not proof that an application or production database is ready.

Decide what must survive

Before using a real database copy, inventory database sizes, encoding and locale, extensions and their target-version availability, roles, ownership, grants, tablespaces, replication, and application connection settings. Read the target and intervening release notes. Define a write-freeze window and who can approve or abort cutover.

The SQL dump documentation describes logical backups. pg_dump backs up one database; cluster-wide roles and tablespaces require separate handling, such as pg_dumpall. This fixture deliberately uses only the existing postgres owner and no extensions, so it does not rehearse those additional objects. Protect real backups as sensitive data and restore only trusted dumps in isolation.

Run the disposable rehearsal

Requirements: Bash, Docker Engine, mktemp, sha256sum, and standard file utilities on Linux. Allow enough space for three temporary PostgreSQL containers and two images. No host database connection, host directory mount, or published port is used. --network none isolates each container; database commands use its local Unix socket. The readiness probe uses container loopback TCP so it waits for the final server, not the temporary initialization server. The trust setting is only for these isolated synthetic containers and must not be copied into a production deployment.

Pull the exact patch tags used in this run:

docker pull postgres:14.24
docker pull postgres:18.6

Tags can move. The observed image digests were:

postgres:14.24 sha256:8ab94f61668dc73bbab4dc13273ff2fc37e635536af62aef5306a49a9682a446
postgres:18.6 sha256:edb0c08dc33befd0262ab3b5cf1c4f3f8cfd8b09e88c05b45adee29d6761ddb6

Create a directory for the script, keeping the same terminal open:

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

Save this as rehearse-postgres.sh:

#!/usr/bin/env bash
set -euo pipefail
umask 077
lab_dir="$(mktemp -d)"
old_id=
new_id=
rollback_id=
cleanup() {
  for container_id in "$old_id" "$new_id" "$rollback_id"; do
    if [ -n "$container_id" ]; then
      docker rm -fv "$container_id" >/dev/null
    fi
  done
  rm -f -- "$lab_dir/fixture.dump" "$lab_dir/fixture.sha256" "$lab_dir/toc.txt"
  rmdir -- "$lab_dir"
}
trap cleanup EXIT
wait_ready() {
  local container_id="$1"
  for attempt in {1..60}; do
    if docker exec "$container_id" pg_isready -h 127.0.0.1 -U postgres >/dev/null 2>&1; then
      return 0
    fi
    sleep 1
  done
  return 1
}
old_id="$(docker run -d --network none -e POSTGRES_HOST_AUTH_METHOD=trust postgres:14.24)"
new_id="$(docker run -d --network none -e POSTGRES_HOST_AUTH_METHOD=trust postgres:18.6)"
rollback_id="$(docker run -d --network none -e POSTGRES_HOST_AUTH_METHOD=trust postgres:14.24)"
for container_id in "$old_id" "$new_id" "$rollback_id"; do
  wait_ready "$container_id"
  docker exec "$container_id" createdb -U postgres rehearsal
done
printf 'Source version: '
docker exec "$old_id" psql -XAt -U postgres -d rehearsal -c 'SHOW server_version;'
printf 'Target version: '
docker exec "$new_id" psql -XAt -U postgres -d rehearsal -c 'SHOW server_version;'
docker exec "$old_id" psql -Xq -v ON_ERROR_STOP=1 -U postgres -d rehearsal -c "CREATE TABLE orders (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, amount integer NOT NULL CHECK (amount > 0)); INSERT INTO orders (amount) VALUES (10), (20), (30);"
docker exec "$old_id" pg_dump -U postgres -d rehearsal -Fc > "$lab_dir/fixture.dump"
test -s "$lab_dir/fixture.dump"
(cd "$lab_dir" && sha256sum fixture.dump > fixture.sha256 && sha256sum -c fixture.sha256)
docker exec -i "$new_id" pg_restore --list < "$lab_dir/fixture.dump" > "$lab_dir/toc.txt"
test -s "$lab_dir/toc.txt"
for container_id in "$new_id" "$rollback_id"; do
  docker exec -i "$container_id" pg_restore -U postgres -d rehearsal --exit-on-error --single-transaction < "$lab_dir/fixture.dump"
  result="$(docker exec "$container_id" psql -XAt -v ON_ERROR_STOP=1 -U postgres -d rehearsal -c 'SELECT count(*), sum(amount) FROM orders;')"
  test "$result" = '3|60'
  next_id="$(docker exec "$container_id" psql -XqAt -v ON_ERROR_STOP=1 -U postgres -d rehearsal -c 'INSERT INTO orders (amount) VALUES (40) RETURNING id;')"
  test "$next_id" = '4'
  docker exec "$container_id" vacuumdb -U postgres -d rehearsal --analyze-only >/dev/null
done
printf '%s\n' 'Target restore: 3 rows, total 60, next identity 4' 'Old-version backup restore: 3 rows, total 60, next identity 4'
source_result="$(docker exec "$old_id" psql -XAt -U postgres -d rehearsal -c 'SELECT count(*), sum(amount) FROM orders;')"
test "$source_result" = '3|60'
printf '%s\n' 'Original source unchanged: 3 rows, total 60' 'Disposable rehearsal passed; no production cutover performed'

Run it as a separate Bash process so its exit trap owns cleanup:

bash rehearse-postgres.sh

Captured output from Docker Engine 28.4.0 on Debian 13 Linux:

Source version: 14.24 (Debian 14.24-1.pgdg13+2)
Target version: 18.6 (Debian 18.6-1.pgdg13+2)
fixture.dump: OK
Target restore: 3 rows, total 60, next identity 4
Old-version backup restore: 3 rows, total 60, next identity 4
Original source unchanged: 3 rows, total 60
Disposable rehearsal passed; no production cutover performed

The script uses the source’s PostgreSQL 14 dump client and successfully restores that archive with PostgreSQL 18 and 14 clients. This tests this specific forward migration and same-version backup recovery. It is not a general promise of cross-version dump compatibility; in particular, a dump taken after upgrading must not be assumed restorable on an older server.

Read the checks honestly

The checksum checks local archive integrity, and pg_restore --list confirms that the archive can be read. Neither proves recovery by itself. The actual restores use pg_restore with --exit-on-error and --single-transaction, then verify row count, aggregate value, and the next identity value. Statistics are refreshed after restore. These checks cover a three-row fixture, not production performance or full schema semantics.

For an application rehearsal, add representative queries and writes, constraints, indexes, grants, extension behavior, locale-sensitive ordering, and application tests. Measure backup, restore, validation, and cutover duration against the allowed outage; no timing or benchmark claim is made here. Confirm that an independently stored backup can be retrieved and restored, not merely that the local file exists.

Rollback has a boundary

The old-version restore here recovers the pre-upgrade state. It does not bring back writes accepted by the new database. Before enabling new writes, an abort can return to the preserved old environment under a controlled connection switch. After new writes begin, rollback requires a separately tested reconciliation or replication plan and an explicit acceptable data-loss decision. Do not simply point clients back and discard new data.

If choosing pg_upgrade, run its checks and rehearse that method separately. This article did not execute pg_upgrade, physical backup recovery, replication, or link mode. The dump/restore results cannot validate those paths.

Clean up

The script’s exit trap removes only the three container IDs it created, their anonymous volumes, and its named temporary backup files. Docker images remain cached. If Docker becomes unavailable during cleanup, inspect the remaining containers and volumes before removing anything; never use a blanket prune command for this exercise.

Remove the saved script and empty practice directory:

rm -- rehearse-postgres.sh
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.