Postgres logical recovery adapter

H3 is implemented locally. A disposable PostgreSQL 18.4 rehearsal passed with real database utilities and a test-only Docker-exec shim. A subsequent local Docker rehearsal passed on Postgres 18.6 with real named volumes. Production deployment and full application recovery remain unverified.

postgres-logical-v1 separates description, discovery, read-only preflight, capture, restore, and verification. It uses the existing Docker/container credentials and does not create accounts, change permissions, or read connection settings from an artifact.

Contract and commands

hostssh recovery-pg describe
hostssh recovery-pg discover --container app-db --user app --database app --select app
hostssh recovery-pg check --container app-db --user app --database app
hostssh recovery-pg capture --container app-db --user app --database app --file /backup/app.dump
hostssh recovery-pg inspect --file /backup/app.dump --database app
hostssh recovery-pg restore --container recovery-db --user app --database app --file /backup/app.dump
hostssh recovery-pg verify --container recovery-db --user app --database app

The destination database must already exist. The restore command applies data; use it only on the intended recovery target. Add --clean to replace existing objects using --clean --if-exists. All adapter restores use --exit-on-error --single-transaction and omit ownership/ACL restoration. Required extensions must already be available.

The adapter's initial policy allows Postgres majors 14–18 and requires the source server, dump tool, restore tools, and destination server to share the same major. These are policy bounds exercised with mocked tools, not a claim of live validation across five releases. Cross-major upgrade recovery is rejected. PostgreSQL supports broader migration scenarios, but version compatibility depends on the dump/restore tool versions; this adapter deliberately starts narrower. PostgreSQL pg_dump documentation

Connection identities come from --container, --user, and --database. Names are restricted to the adapter's supported ASCII identifiers, preventing connection-string/option injection. --host accepts an empty value for the existing local socket, or 127.0.0.1. If a password is needed, provide PGPASSWORD in the invoking process environment; the adapter forwards its name to Docker without placing the value in command arguments, receipts, or output. This does not change the fleet engine's existing credential handling for container provisioning or global-role SQL.

Discovery distinguishes required selections from optional exclusions. Unsupported optional database identities are reported as excluded; selecting an unsupported or absent identity fails. Discovery output is JSON evidence and is never evaluated as shell configuration.

Artifacts and failure behavior

Capture writes a temporary custom-format dump, validates its archive listing, hashes and syncs it, then publishes the payload and <dump>.pg-recovery.json receipt. The receipt contains adapter/schema versions, database identity, source/tool majors, capture format, consistency scope, archive verification level, size and SHA-256. It contains no reusable credentials. An existing valid dump remains available when a new capture fails before publication. An interrupted publication may leave a payload without a valid receipt; native restore rejects it.

Restore validates the receipt, digest, selected database, target tools/access/versions and archive readability before applying SQL. It then checks database connectivity after restore. Failure returns a nonzero status and retains the dump/receipt. Review the target before any retry. Directory leases prevent cooperating capture/restore processes from replacing an artifact mid-operation. Keep artifact directories private; hostile concurrent local writers are outside this contract. Killed captures can leave temporary files for operator cleanup.

Archive listing and successful pg_restore execution are different evidence. A standalone verify reports connection-only; a successful restore reports restore-exit-and-connection. Neither proves data completeness, application health, or a whole-system consistent snapshot. The transaction/error-stop behavior follows the native tool contract. PostgreSQL pg_restore documentation

Fleet and managed-job integration

Fleet capture uses the adapter for logical Postgres dumps and saves discovery reports under the stage's system/ directory. H2 catalogs hash the dumps/receipts and declare the required postgres-logical-v1 adapter when receipts are present. Global-role SQL, live filesystem volumes and cross-database consistency remain outside the logical adapter's snapshot guarantee. Global-role SQL also retains its legacy error handling; it is not covered by the adapter's error-stop transaction or post-restore verification. Role reconciliation needs a separate drill.

Fleet restore checks every selected dump before destination replacement. Its image preflight requires an already-present image and runs fixed version probes with no network, no writable root filesystem, no added capabilities and no image pulling. It checks the actual server/tools again before database creation/global SQL/restore. Supply --pg-image with a matching-major image; the engine's existing default image may differ from your source major.

Managed db-backup / db-restore jobs use the adapter through their existing database functions. Provisioning support for MariaDB/Redis does not imply H3 recovery support; those paths remain separate legacy behavior. No new job schema, scheduler, registry or connector runtime was added.

Rollback is explicit: HOSTSSH_PG_ADAPTER=legacy selects the existing managed/fleet Postgres path and its weaker guarantees. It is independent of H2's legacy pack setting. There is no fallback after a native adapter failure. The direct recovery-pg command always enforces its contract. Upgrade readers before producing adapter-bearing packs; old H2 readers reject the new required adapter. Live adapter parity, stronger data verification and H4/H5 remain pending.