# PostgreSQL logical restore rehearsal

Observed on 9 October 2026, Asia/Bangkok: PostgreSQL 16.15 to 18.6, official Debian
Bookworm images pinned by digest, Linux arm64 in Docker Desktop. This is a data
transfer rehearsal with synthetic records. It does not install a Bitnami chart,
CloudNativePG, Kubernetes storage, replication or a production application.

Requires a local Docker socket/context and Python 3.9+. Remote Docker endpoints are
rejected. Run from the extracted download directory:

```sh
python3 postgresql-restore.py --output my-observed-results.json
```

The script creates a randomly named internal Docker network and three new database
containers (256 MB and one CPU each), then removes only those containers, their
anonymous volumes and that network. It does not publish host ports, read a kubeconfig
or connect to an existing database. Pinned images must be available locally or
downloadable from Docker Hub. The recorded run used cached images; a new run that
downloads them includes pull time in its elapsed duration. The local images remain
cached after the test. Cleanup failures are recorded, and cleanup continues for the
other resources owned by the run. A failed cleanup makes the overall result fail.

Trust authentication is confined to this disposable internal network. There are no
real credentials or data. Do not turn this fixture into a production deployment.

The actual checks are:

1. Seed 100 customers and 250 orders, including Unicode, JSONB, hstore, an enum,
   nullable text, binary data, numeric values and timestamp data. Create identities,
   foreign/check/unique constraints, indexes, a view and explicit owner/reader roles.
2. Freeze new source sessions using `default_transaction_read_only`; prove a write
   fails. This fixture has no existing application sessions. Production needs a
   complete writer/session cutover plan, not just this setting.
3. Use the target's PostgreSQL 18.6 `pg_dump` client to export the 16.15 source.
   Restore with `pg_restore --exit-on-error --single-transaction`. Compare row
   fingerprints, sequences, owners, constraints, indexes, view and extension.
4. Exercise read-only privileges and a rejected foreign-key insert. Reset the identity
   sequence consumed by the deliberately failed insert before comparing the backup.
5. Create a new logical backup on the target; restore it into a third container with
   independent storage. Confirm the same state. Reject a deliberately truncated
   backup and check that its single transaction leaves no partial application tables.
6. Before committing a write to the target, confirm the retained source can resume
   writes. Its test insert is rolled back; the nontransactional sequence advance is
   reset explicitly. No endpoint-switch, application or DNS behavior is implied.
7. Commit one target-only order. Confirm that the target now has 251 orders and the
   source still has 250. A return to the source at this point would lose the new
   record. Reverse synchronization is not implemented or claimed.

`observed-results.json` is the actual recorded run. Its 9.31-second duration describes
this small cached-image local experiment, not migration downtime or performance.
Dump byte hashes identify that run; PostgreSQL archives contain metadata that can
change across otherwise equivalent reruns. Compare the explicit state checks.

Unverified: real Bitnami image/entrypoint conversion, operator/PVC migration, other
extensions, locales/collations, role passwords, large objects, multiple databases,
active writes, replication, HA/failover, PITR, production backup tooling, throughput
and a rollback after the target accepts writes. The two synthetic NOLOGIN roles are
created explicitly on each server; this is not an automatic role migration tool.

Primary references:

- https://www.postgresql.org/docs/18/upgrading.html
- https://www.postgresql.org/docs/18/app-pgdump.html
- https://www.postgresql.org/docs/18/app-pgrestore.html
- https://hub.docker.com/_/postgres