--- summary: "Set up and operate a PostgreSQL-backed Gateway, with support boundaries, ports, backups, and recovery" read_when: - Setting up PostgreSQL for an OpenClaw Gateway - Migrating an existing installation between SQLite and PostgreSQL - Planning PostgreSQL backups, updates, or disaster recovery title: "PostgreSQL operator guide" doc-schema-version: 1 --- # PostgreSQL operator guide Use this guide to provision PostgreSQL for one Gateway, move existing data, and operate the resulting installation. PostgreSQL is a **supported alternative** for the five configurable store families on qualified PostgreSQL 16–18 runtimes. SQLite remains the default. Selecting PostgreSQL does not move every local file into the server. Review the [support matrix](#support-matrix) before deployment. ## Support matrix Supported means the named flow has PostgreSQL conformance or live acceptance proof. It does not promise identical search ranking or latency across engines. Full conformance covers PostgreSQL 16–18 with UTF8 and the search extensions below. Linux CI runs PostgreSQL 17 daily and PostgreSQL 16 and 18 weekly; manual qualification can run all three majors. See the [qualification evidence](#server-major-qualification) for the earlier Homebrew runs. | Family or surface | Status | Scope or remaining proof | | ------------------------------------------------- | --------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `state`, including Matrix account/token roots | Supported | Runtime, bidirectional port, matched backup/restore and schema migrations. | | `agent` | Supported | Runtime, transcripts, memory/search, bidirectional port and matched backup/restore. | | `workboard` | Supported | Runtime, bidirectional port and matched backup/restore. | | `logbook` | Supported | Runtime, bidirectional port and matched backup/restore; frame files stay local. | | `team-reports` | Supported | Runtime, bidirectional port and matched backup/restore; collection scratch stays session-local. | | CLI and interactive setup/onboard | Supported | Includes one-shot commands and Doctor; PostgreSQL `onboard --reset` deliberately refuses. | | Gateway turns and CLI parity | Supported | Real agent turns, tools, compaction, restart, child sessions and plugin operations. | | Doctor | Supported | Per-store configured/observed engines, schema, extensions, reachability and custody; unknown evidence stays unknown. | | Update admission, forward migrations and recovery | Supported | Exact captures and state/agent rollback; independent writes can prevent automatic recovery. | | Published stable 2026.10.5 → current upgrade | Supported, qualified scope | Published 2026.10.5 SQLite → current → PG17 port, foreground Gateway and matched-backup rollback verified. Stable PG → current is not applicable; [evidence and service/turn limits](#published-stable-upgrade-qualification). | | Port, backup, restore and disaster recovery | Supported | All five families, retained roots and mixed engines; memory indexes require the documented rebuild step. No cluster/PITR orchestration. | | Mixed engines | Supported | All four state/agent pairs and independently selected plugin families; one shared PostgreSQL connection configuration. | | Independent Gateways on one server | Supported | Separate databases recommended; distinct namespaces in one database also tested. One live Gateway per store. | | Multiple active Gateways sharing a store | Not supported | No shared-store failover or active-active ownership protocol. Use independent stores. | | Memory/search and standing intents | Supported | Real indexing, rebuild, filtering, bounds, forget/reset and missing-extension refusal; ranking is engine-local. | | Plugins using SDK stores | Supported | SDK shared-state stores and registered family owners; arbitrary private plugin databases and LanceDB are outside this selection. | | Warm-path performance | Supported, measured | No equal-latency guarantee; see [performance evidence](#performance-evidence). | | PostgreSQL 16–18, UTF8 | Supported | Local Homebrew 16.15/18.6 full suites and PG17 CI/live proof; [scope and results](#server-major-qualification). | | Other admitted PostgreSQL majors | Experimental | The admission floor is 16. Majors outside 16–18 need their own full conformance run. | | DSN with env/file SecretRef | Supported | Host resolution, missing-secret refusal and CLI/Gateway use. | | Exec SecretRef | Supported | CLI/Gateway host resolution, worker propagation, restart, and failed/empty provider refusal; [qualification](#tls-and-exec-connection-qualification). | | TLS DSN | Supported | `verify-full` with an absolute `sslrootcert` path; certificate/hostname verification and invalid-certificate/plaintext refusal; [qualification](#tls-and-exec-connection-qualification). | | `unaccent`, `pg_trgm`, `vector` | Supported requirements | Positive and missing-extension cases tested; administrators install extensions. | | Headless Linux, including Linux under WSL2 | Supported | Linux PostgreSQL conformance and CLI/Gateway paths. | | Headless macOS CLI | Supported | Real PostgreSQL CLI, interactive setup and Gateway runs. | | Native Windows + PostgreSQL | Supported | Native Windows client and PG17 server, including fresh backup restore; [qualification and limits](#native-windows-qualification). | | Native Windows managed service | Supported | Install, readiness, turns, graceful stop, restart continuity and data-preserving uninstall; [qualification](#native-windows-qualification). | | Native Windows package update with PG state | Not yet applicable | No published release selects PostgreSQL for state and agent stores; requalify when one ships. [Evidence](#native-windows-qualification). | | Native Windows database port | Not supported yet | Use fresh PostgreSQL state on Windows, or run the port on Linux/macOS; [details](#native-windows-qualification). | | macOS app | Experimental: pending signed-app proof | Credentials stay local, runtime pins stay local, and exec approvals use the shared owner. Combined signed-app acceptance is pending. | | iOS/iPad app | Not supported without real-device proof | Simulator evidence is insufficient; real-device authentication and approval flows remain unqualified. | Anchors, client credentials, daemon runtime pins, quarantine, managed-update and package-activation journals remain **local by design**, not missing PostgreSQL families. There are no other configurable families. Device caches, external read-only sources and file artifacts also remain outside engine selection. ## Server-major qualification The `PGQUAL-MAJORS-UPGRADE` runs on 2026-10-19 used Homebrew PostgreSQL 16.15_1, 17.11_1 and 18.6_1 on macOS arm64, with `unaccent` 1.1, `pg_trgm` 1.6 and `vector` 0.8.7. PostgreSQL 16 required building pgvector with that major's `pg_config`. No Docker image was used, so these are Homebrew bottle SHA256s, not container-image digests: | Major | Bottle SHA256 | | ----- | ------------------------------------------------------------------ | | 16 | `d60d675afb25019c6fab16aac94d44b2410534123799d87dfbda6f1485ebbc8b` | | 17 | `a5bf59bb6b458d858dbf2b69f83277917cb04404fc5add128f47ffd69d0501e2` | | 18 | `c03f8a2999453ed0c4f0290daba750f25602e3f32ab694d99830c7d252868137` | The pgvector source SHA256 was `cac0b10c360f05b2d521200105ba3697e773d4cd3731f5a915a7e37ebe0bea85`. Full runs used the CI entry point, without target filters: ```bash node scripts/test-database-conformance.mjs --engine postgres --connection "$OPENCLAW_DATABASE_DSN" ``` | Local run label | Source | Result | | -------------------------------- | -------------- | --------------------------------------------------------------------------------------------- | | `pg16-full` | `8082bfa81ab3` | 948 passed, 2 failed, 37 skipped; three affected files, including cleanup hooks. | | `pg17-comparison` / `pg16-rerun` | `ca36f46d5dc6` | The same three files passed on both majors: 10 tests each, including cleanup. | | `pg18-full` | `3ca588744dbc` | 947 passed, 3 failed, 37 skipped; failures in archive restore and temporary-database cleanup. | | `pg17-final-comparison` | `1ae8522f56c0` | Archive restore and search-admission files: 9 passed, including cleanup. | | `pg18-final-comparison` | `1ae8522f56c0` | The same files plus port and recovery integration: 43 passed, including cleanup. | | `pg18-journal-phase` | `c707172ff525` | State port and port-command integration after the published-install repair: 25 passed. | The full-run failures remain part of the record. PG16's archive timeout and cleanup failures did not reproduce in its single focused rerun. PG18's three failures also passed in its single focused rerun and on PG17. No server-version incompatibility was reproduced. PG18's initial malformed-JSON rejection exposed an overly specific conformance assertion: PG18 may report JSON parse failure before a CHECK violation, while still rejecting the row. Its first full attempt also exhausted the default lock table. The recorded full PG18 run used `max_locks_per_transaction=512` with 100 connections; production sizing depends on actual concurrent store use. ## Native Windows qualification The `PGQUALWIN` qualification on 2026-10-25 used Windows Server 2022 Datacenter build **20348.5761**, Node **24.22.1**, PostgreSQL **17.11** from the official EDB Windows installer, and pgvector **0.8.7** built from its official release tag with MSVC. Extensions were `pg_trgm` **1.6** and `unaccent` **1.1**. Both OpenClaw and PostgreSQL ran natively on Windows, connected over loopback TLS `verify-full`. Source pins, commands, timings, and redacted evidence are recorded in [PR #183512](https://github.com/openclaw/openclaw/pull/183512). Covered flows include non-interactive onboarding through a file DSN SecretRef, Doctor/status/models, foreground Gateway readiness and health, scripted turns, memory indexing/search, and forced Gateway restart continuity. A populated PG backup was created, verified, and restored into fresh staging with exact canonical digests and no embedding calls during backup/restore and index rebuild. Path checks covered spaces/non-ASCII names, drive-letter/backslash SecretRef and `sslrootcert` paths, and a **324-character state directory**. An unreachable DSN failed without credential leaks, and Gateway startup rejected invalid config with exit **78**. The `PGQUALWIN2` follow-up on the same Windows/Node/PostgreSQL versions qualified native Scheduled Task service lifecycle with a file DSN SecretRef. Patched build `8f8721740bdb`, whose service fix landed in [PR #183632](https://github.com/openclaw/openclaw/pull/183632), passed installation, readiness/health, a scripted turn, and completed graceful shutdown. No Gateway worker/bridge processes or OpenClaw role backends remained in `pg_stat_activity`. Start and restart preserved exact six-table, session/history and memory digests. Uninstall removed the service registration and retained PostgreSQL data, local anchors and the file SecretRef. Lifecycle checks emitted no embedding calls before deliberate memory-search probes, which separately embedded their queries. Host reboot recovery passed on the earlier build `5abc0c66e293`: the Scheduled Task ran automatically, a subsequent `gateway start` returned `already-running`, and readiness and continuity checks passed. Reboot was not repeated on the patched build. The baseline `2f95e977c77d`, target `5abc0c66e293` and patched service build used state **32** / agent **34**; this run does not qualify the later state 33 / agent 35–36 migrations. **Package update with PostgreSQL state is not yet applicable.** No published release selected PostgreSQL for state and agent stores in the 2026-10-25 registry inspection. Stable **2026.10.7** lacks that support; **2026.10.10-beta.3** accepts the configuration but its bundled runtime selects SQLite for state and agent stores, with PostgreSQL enabled only for Workboard. Requalify published-release updates when a version selecting PostgreSQL state and agent stores ships. The previous-main update and managed rollback attempts did not complete. Separate handoff-path and candidate-admission fixes landed in [PR #183622](https://github.com/openclaw/openclaw/pull/183622) and [PR #183645](https://github.com/openclaw/openclaw/pull/183645). Manual package replacement followed the documented [recovery procedure](/install/updating/rollback-and-recovery), but does not qualify managed activation or rollback. Commands, source pins, continuity digests and timings are recorded in [PR #183634](https://github.com/openclaw/openclaw/pull/183634). Native Windows does not yet support SQLite ↔ PostgreSQL database port, including dry-run, resume, and rollback. The command refuses before creating recovery artifacts and leaves existing data and engine selection unchanged. Start with fresh PostgreSQL state on Windows, or run the port on Linux/macOS. ## When to use PostgreSQL Choose PostgreSQL when you want supported Gateway stores on an independently managed database server and can operate its availability, access control, and backups. Keep SQLite when you want the default local installation without that server dependency. PostgreSQL does not add multiple active Gateways for one installation, automatic failover, or automatic replication management. Keep the local state directory and configured agent directories. Each PostgreSQL store still has a small SQLite **anchor** at its usual database path. It records the store UUID and server namespace, not the server's application rows. An anchor and its matching PostgreSQL data are one recovery unit. Never delete an anchor to change engines. If an agent's namespace is lost, follow the [retained-store recovery procedure](#recover-a-retained-agent-store). The quarantine, managed-update, and package-activation safety journals remain local SQLite. Daemon runtime pins remain in `control/services.sqlite` so service recovery does not require PostgreSQL. Device-local caches and file artifacts remain local; this includes Logbook frame images and cold transcript blobs. Incognito storage remains memory-only. The `memory-lancedb` plugin uses its own LanceDB store and is not switched by `database.engine`. Client credentials live in the fixed-local `client/credentials.sqlite` store. Exec approvals go through the shared-state owner and require PostgreSQL to be reachable when that family uses PostgreSQL; they fail closed if it is unavailable. These ownership changes are complete, but the macOS app claim remains pending signed-app proof. See the [native credential contract](/reference/database-schemas/versioning#local-client-credentials). When present, the local credential store is captured as a fixed-local resource, outside the engine families. User backups apply its credential sanitization; updater recovery captures it exactly. Read-only agent scopes retain their store identity and schema admission between reads, but return their PostgreSQL transport after the last borrower finishes. Each worker isolate caches **at most two returned transports across all stores**, keyed by connection and timeout settings. Reusing one for a different store changes its `search_path` before the next query. Same-store reuse within one second needs no extra query. After at least one second in the idle cache, the transport is validated before the next statement and reconnected if validation fails. An already-reported connection error also reconnects before reuse. A backend terminated less than one second after its last use can still fail the next statement if its termination notification has not arrived; user statements are never replayed after a connection failure. An open transaction is never returned to the cache; checked-out connections remain exclusive. Returned transports expire after three minutes idle or on worker shutdown. SQLite handle retention is unchanged. Budget PostgreSQL backends as the sum of each isolate's **checked-out connections plus up to two returned transports**, approximately `isolates × (active + 2)`. Here, active means a checked-out connection, including dedicated writable and maintenance handles, even when PostgreSQL reports it as idle. This is a retention bound, not a process-wide connection limit; concurrent work still needs headroom. A PostgreSQL 17 synthetic workload (four sessions per agent, startup through six idle minutes, then 130 history reads and a usage refresh) measured: | Agent stores | Earlier unbounded peak (role limit 200) | Bounded-retention peak (role limit 40) | Backends after six idle minutes | | ------------ | --------------------------------------- | -------------------------------------- | ------------------------------- | | 1 | 19 | 18 | 8 | | 4 | 43 | 18 | 11 | | 13 | 101 | 24 | 12 | The final retirement policy was replayed at 1 and 13 stores; the 4-store row is from the preceding candidate. All bounded runs completed without connection-limit errors. The 13-store read burst peaked at 16 backends after the idle window. These are workload measurements, not universal limits. For one Gateway with this 1–13-store workload, use a runtime role limit of at least 40 and server `max_connections` of at least 100, leaving room for administration and other clients. For multiple Gateways, budget each role separately and add their peak budgets, other clients, and PostgreSQL's reserved slots to `max_connections`. More concurrent work or plugin workers can require a larger budget even at the same agent count; check `pg_stat_activity` during startup and representative bursts. Gateway workers share four PostgreSQL I/O services per process. The Gateway starts them when it selects PostgreSQL, overlapping their initialization with config and plugin preparation. Replies wake only their own connection; blocked callers check for service failure at intervals of at most 50 milliseconds. These services multiplex connections; four services do not mean four backends. An externally constructed SDK or plugin worker without inherited pool endpoints starts its own four services on first PostgreSQL use. It emits a once-per-thread warning and `openclaw.postgres.pool.self-root` diagnostic with the worker label and thread ID. Worker diagnostics count these roots separately from physical I/O services, so unexpected worker creation remains visible. Usage reports retain their logical reader in the existing history worker between requests and return its idle transport. Each report still uses a fresh read snapshot; database maintenance and worker shutdown close the reader before releasing custody. ## Requirements ### Provision a database and runtime role Use a qualified **PostgreSQL 16–18** runtime with **UTF8 server and client encoding**. Admission accepts 16 or newer, but other majors retain the experimental qualification noted above. Provision a separate database per Gateway. The runtime role must be able to connect, create schemas, and own the objects it creates. It does not need superuser, role-administration, or database-creation privileges. For a fresh database, an administrator can run the following, then set the runtime role's password using the administrator's credential-management tooling: ```sql CREATE ROLE openclaw_runtime LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION; CREATE DATABASE openclaw ENCODING 'UTF8' TEMPLATE template0; REVOKE ALL ON DATABASE openclaw FROM PUBLIC; GRANT CONNECT, CREATE, TEMPORARY ON DATABASE openclaw TO openclaw_runtime; ``` In the new `openclaw` database, install the search extensions as an administrator: ```sql CREATE EXTENSION IF NOT EXISTS unaccent WITH SCHEMA public; CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public; GRANT USAGE ON SCHEMA public TO openclaw_runtime; ``` For semantic or hybrid memory search, also install **pgvector 0.7 or newer**: ```sql CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA public; ``` If `vector` is already installed at an older version, upgrade the server's pgvector package, then have an administrator run `ALTER EXTENSION vector UPDATE` in the selected database before reopening the store. `unaccent` is required for full-text search; `pg_trgm` is required when a store declares trigram search. Installing both covers either text mode. The extension SQL never runs automatically. Install any missing server extension packages before running it. Managed services must expose the extensions in the actual database you select. `CREATE` on the database is needed beyond the first store: schema verification can create and drop a private comparison namespace during bootstrap, migrations, and server-major upgrades. `TEMPORARY` supports memory rebuild staging and session-local plugin scratch tables. The runtime role owns its store schemas, tables, sequences, indexes, functions, and text-search configurations; keep that ownership when restoring or changing roles. Port and restore check namespace ownership, so table grants alone are insufficient. The grants above assume PostgreSQL's default public execution privileges for extension functions and usage privileges for extension types. If your administrator has revoked those defaults, also grant the runtime role access to the required extension functions and types. Do not grant the runtime role permission to install extensions or access other installations' schemas merely to make admission pass. ### Connection poolers Connect directly to PostgreSQL or use a pooler in **session mode**. Transaction-mode poolers, including PgBouncer with `pool_mode=transaction`, Supavisor transaction mode, and similar services, are **not supported**. The bridge relies on per-connection session state: `lock_timeout`, `statement_timeout`, `search_path`, and named prepared statements. A pooler must preserve that session for the life of the connection. ### Supply the connection securely Store the DSN in your secret manager and make it available to both the Gateway service and offline maintenance commands. Reference it from config; do not put the DSN itself in `openclaw.json`: ```json5 { database: { engine: "postgres", postgres: { connection: { source: "env", provider: "default", id: "OPENCLAW_DATABASE_DSN" }, schemaPrefix: "openclaw", }, }, } ``` Env, file, and exec [SecretRefs](/gateway/secrets/secretref-contract) are qualified. A store-backed SecretRef cannot bootstrap the database that holds that secret. The host resolves the connection before passing it to workers. Restart after changing the connection, its environment variable source, prefix, or engine map; these are fixed for the process. An all-SQLite configuration does not resolve an unused PostgreSQL connection. When the connection uses an env SecretRef or a whole-value `${NAME}` substitution, the Gateway withholds that environment variable from inherited host exec and sandbox environments, including custom variable names. Explicit command overrides and operator-configured sandbox environment values remain available. Offline model credential commands, including `models auth paste-api-key`, use the configured engine before the Gateway starts. Supply the same DSN SecretRef to these commands. With PostgreSQL selected, credentials are stored on the server; the local database path contains an anchor rather than SQLite application state. Setup commands (`onboard`, `setup --wizard`, and the legacy `crestodian` entry), including interactive sessions, and one-shot commands such as `agents add`, `models list`, `models status`, `config set`, and `doctor` (including `--fix`) can access PostgreSQL synchronously in their own CLI process. The Gateway still requires worker access outside synchronous admission and migrations. Setup starts any temporary or foreground Gateway in a separate process with its own admission; the setup process keeps its one-shot role throughout. Interactive remote Gateway setup also uses local migration and plugin metadata, so the configured local PostgreSQL credential must resolve before setup starts. If existing config cannot be parsed, interactive setup offers Doctor's file-only repair before selecting a database. Declining repair or leaving the config unparseable stops setup without creating application state. `onboard --reset` refuses PostgreSQL before changing configuration or data: onboarding reset retains database state and cannot discard its PostgreSQL routing. Rerun without `--reset`, or explicitly port supported stores to SQLite first. If the connection SecretRef is unavailable, `config set`, `config patch`, and `config unset` can still repair paths under `database` while the Gateway is stopped. These repairs leave database files untouched. A batch that also changes other settings requires the configured database connection as usual. TLS options are DSN query parameters handled by the PostgreSQL driver, not additional `database.postgres` config keys. For example, the **secret value** could have this shape (the password is a placeholder): ```text postgresql://openclaw_runtime:@db.example.com:5432/openclaw?sslmode=verify-full&sslrootcert=/etc/openclaw/postgres-ca.pem ``` Use `sslmode=verify-full` for certificate and hostname verification. Omit `sslrootcert` when the server uses a certificate trusted by the runtime's normal CA configuration. `sslcert` and `sslkey` can supply client-certificate file paths when required by the server. Those files must be readable on the Gateway and maintenance host. URL-encode special characters in credentials and paths. `sslmode=disable` disables TLS; reserve it for a deliberately local connection. Keep DSNs out of shell history, diagnostic captures, and logs. When PostgreSQL runs on the Gateway host, the server can accept connections only on its Unix socket (`listen_addresses = ''`). Put the socket directory in the DSN's `host` query parameter and disable TLS for it: ```text postgresql://openclaw_runtime:@localhost/openclaw?host=/path/to/socket-dir&sslmode=disable ``` PostgreSQL does not accept TLS on a Unix socket: keeping `sslmode=verify-full` or another TLS mode from a network DSN, or a `PGSSLMODE` environment variable, fails with "The server does not support SSL connections". Drop the certificate options and keep `sslmode=disable` in the DSN, which takes precedence over `PGSSLMODE`. The socket file belongs to the OS account that runs PostgreSQL, and `unix_socket_permissions` applies to that file, not to its directory. When PostgreSQL and the Gateway run as the same account, `unix_socket_permissions = 0700` limits the socket to that account. When they run as different accounts, put both in a shared group, set `unix_socket_group` to it with `unix_socket_permissions = 0770`, and make the socket directory traversable by that group. Either way, authenticate the runtime role with a `local ... scram-sha-256` line in `pg_hba.conf`. A file SecretRef can read the DSN from a private file: ```json5 { secrets: { providers: { pgdsn: { source: "file", path: "~/.openclaw/secrets/postgres-dsn", mode: "singleValue" }, }, }, database: { postgres: { connection: { source: "file", provider: "pgdsn", id: "value" }, }, }, } ``` ### TLS and exec connection qualification For a private CA, install its certificate on every host that runs the Gateway or maintenance commands. Use an absolute `sslrootcert` path: the driver opens that file in the process making the connection. The server certificate must include the DSN hostname in its subject alternative names. To require encryption on the server, use a `pg_hba.conf` rule such as this, without a matching plaintext `host` rule: ```text hostssl openclaw openclaw_runtime 127.0.0.1/32 scram-sha-256 ``` Enable `ssl = on` and configure the server's certificate and private key in `postgresql.conf`. A client using `sslmode=disable` then receives a `pg_hba.conf` refusal with `no encryption`; it cannot silently use plaintext. A resolver that prints only the DSN needs `jsonOnly: false`: ```json5 { secrets: { providers: { postgres: { source: "exec", command: "/usr/local/libexec/openclaw-postgres-dsn", jsonOnly: false, }, }, }, database: { engine: "postgres", postgres: { connection: { source: "exec", provider: "postgres", id: "database-dsn" }, }, }, } ``` The executable must satisfy the [exec provider trust rules](/gateway/secrets/secretref-contract#exec-provider). Alternatively, retain `jsonOnly: true` and return the documented protocol-v1 response. Keep the DSN out of command arguments and provider configuration; retrieve it from the secret manager inside the executable. The PostgreSQL conformance suite exercises [CLI onboarding and Doctor](https://github.com/openclaw/openclaw/blob/main/src/infra/database-engine/cli-oneshot.postgres.test.ts) with an exec connection and [Gateway bootstrap, workers, and restart](https://github.com/openclaw/openclaw/blob/main/src/gateway/server-database-engine.postgres.test.ts) with the same provider contract. Run it against a TLS-only PostgreSQL server to exercise those paths with certificate verification: ```bash node scripts/test-database-conformance.mjs --engine postgres --connection "$OPENCLAW_DATABASE_DSN" -- \ src/infra/database-engine/cli-oneshot.postgres.test.ts \ src/gateway/server-database-engine.postgres.test.ts \ src/infra/database-engine/postgres-search-admission.postgres.test.ts ``` The [PostgreSQL 17 TLS acceptance matrix](https://github.com/openclaw/openclaw/pull/177928) records the live CLI and Gateway runs, certificate rejection cases, and secret-redaction checks behind these support claims. ## Set up a fresh installation 1. Provision the database, role, extensions, and connection secret above. 2. Add the PostgreSQL config before the installation creates its first stores. Configure other Gateway settings through the normal [setup flow](/start/getting-started). If it has already created SQLite stores, follow [migration](#migrate-an-existing-installation) instead of changing the engine setting directly. 3. Start the Gateway through its normal service owner. First open creates each store's local anchor, a UUID-qualified PostgreSQL schema, and the current generated schema. Stores are created as their owners open them; an unused plugin need not have a namespace yet. 4. Verify from the same configured installation: ```bash openclaw doctor openclaw status openclaw health ``` Doctor's `Database engines` section should include: ```text Shared state: default=postgres, override=inherit, effective=postgres (configured) Agent data: default=postgres, override=inherit, effective=postgres (configured) ``` The `(configured)` suffix matters: these lines describe selection, not a successful open of every store. Doctor also reports physical shared-state roots and namespaces, including mismatches and actionable repair steps. Verify Gateway health and exercise the agent/plugin features you enabled. A failed PostgreSQL connection or schema admission never silently switches that store to SQLite. ## Run independent Gateways on one server Use a separate PostgreSQL database, runtime role, connection secret, and local state directory for each Gateway. Each database may use the default `schemaPrefix: "openclaw"`. Install extensions in each database. Alternatively, independent installations can share a database with distinct prefixes such as `installation_a` and `installation_b`. Prefixes contain 1–27 lowercase letters, digits, or underscores, beginning with a letter or underscore. New schemas have the shape `__`. Existing anchors keep their recorded namespace when the configured prefix changes. A prefix is a naming convention, not an access-control boundary. Use separate roles and appropriate schema permissions if sharing a database. Extensions in `public` are shared; store objects remain in their own namespaces. Keep each anchor with its originating installation. There is still **one Gateway per store**. Do not mount the same state directory on multiple Gateways or copy its anchors to make an HA/failover installation. The Gateway records and heartbeats an owner lease in shared state. A live or unverifiable previous owner prevents takeover; lease expiry alone is insufficient. Advisory writer locks protect transactions; they do not provide multi-Gateway ownership, routing, or failover. ## Mixed engines `database.families` can override `state`, `agent`, `workboard`, `logbook`, and `team-reports`. Every override requires an `engine`. All PostgreSQL families share the same connection settings and schema prefix. Fixed-local journals cannot be overridden. For fresh stores, this selects PostgreSQL shared state while retaining SQLite agents and plugin stores: ```json5 { database: { engine: "sqlite", families: { state: { engine: "postgres" } }, postgres: { connection: { source: "env", provider: "default", id: "OPENCLAW_DATABASE_DSN" }, }, }, } ``` Both SQLite and PostgreSQL agents are supported with either shared-state engine. Workboard, Logbook, and Team Reports can independently select either engine. Matrix account and token-hash roots belong to the state family and follow its effective engine, including retained and disabled accounts. Use a scoped port to change an existing family: ```bash openclaw database port --family workboard --to postgres --dry-run openclaw database port --family workboard --to postgres openclaw database port --family agent --to postgres openclaw database port --family workboard --to sqlite ``` Each operation ports every existing physical store of that family and publishes only its override. The default and unrelated families remain unchanged. A destination equal to the default removes the redundant override. Unscoped ports retain whole-installation behavior for homogeneous eligible families; mixed selections require `--family`. Mixed installations support matched backup/restore and compatible updates. Whole-installation archives containing PostgreSQL stores can be created while the Gateway runs; restore requires offline custody. [Archive restore](#restore-all-postgresql-stores) imports captured PostgreSQL stores into fresh namespaces and publishes matching local anchors. Updates require candidate proof of family-override support as well as support for each selected or retained PostgreSQL family. Do not change a populated family's engine to evade a refusal. Configuration does not migrate data. ## Migrate an existing installation ### Move an exact stopped source between disposable hosts An ordinary user backup intentionally sanitizes delivery and recovery state. It is not an exact migration source. When an external supervisor must retire a SQLite host before an offline migration job can run, use the native exact migration capture while the original host's filesystem is still available. Stop its Gateway and every embedded writer first; stopping the whole replica before capture may discard its only local files. Create a fresh verified filesystem archive for that quiesced installation, then: ```bash openclaw database migration-capture capabilities --json openclaw database migration-capture create --base-archive /private/filesystem.tar.gz --output /private/migration-capsule --json openclaw database migration-capture restore /private/migration-capsule --manifest-sha256 --target /private/new-staging --json ``` Creation requires exclusive offline ownership and an absent output directory outside the state/config roots. The private capsule contains the filesystem archive, exact native SQLite snapshots, and authored configuration/includes. Registered and retained native stores, fixed-local credentials, safety journals and daemon intent are discovered through the existing exact recovery owner. Codex and other opaque application files remain in the filesystem archive; their own processes must already have closed them. PostgreSQL sources and unsupported external database/config roots refuse rather than being silently omitted. Restore requires the producer's manifest digest, the same logical state/config paths, and a new empty staging target outside live state. It verifies the bound artifacts, extracts the full filesystem archive, then overlays exact native database/config images only inside that private staging tree. Shared/local credential bindings are re-admitted to the actual new file identities. Captured device/inode values are only source-pair evidence, never authority over a new host. An invalid capsule cannot publish a successful restore or mutate live state. The returned `archiveRoot`/`targetPath` and `exactMigration` receipt let the external lifecycle owner perform its existing state/config promotion before running the ordinary database porter. The receipt's exact profile covers the native database/configuration images. `verified: true` confirms artifact validation, not temporal consistency of an arbitrary base archive. The controller must create the base after the Gateway and every embedded or external writer have stopped, and keep them stopped through capture; matching `sourceStateDir` alone does not prove a consistent generation. Keep the complete capsule private; it includes credentials, claims and completion records deliberately absent from portable user backups. An incomplete capture is retained for diagnosis and never grants updater rollback or port-resume authority. This command does not create an active database-port manifest, import PostgreSQL namespaces, start services or enable multiple active Gateways. ### Prepare and port to PostgreSQL While the installation still selects SQLite, run: ```bash openclaw doctor --fix ``` This is the legacy migration gate. Port preflight refuses pending declared migrations, including native task assignments, Matrix inbound dedupe, and legacy memory-index sidecars. Upgrade older shared state with a compatible binary and Doctor before retrying. Agent schema upgrades can run in the port's private copy. A scoped port checks only migrations declared for that family. Fresh Matrix roots without legacy inbound-dedupe rows do not require a completion receipt. Port also refuses reserved-incognito session keys: run `openclaw doctor --fix` on the SQLite installation before retrying; PostgreSQL Doctor leaves such stores unchanged and requires re-adoption from a repaired SQLite source. Provision PostgreSQL and add `database.postgres.connection`, but leave the source engine selections unchanged. Stop the Gateway through its service owner and stop embedded writers using the same state directory. Port never stops or restarts services for you. ```bash openclaw database port --to postgres --dry-run openclaw database port --to postgres ``` The dry run inventories sources and checks read-only prerequisites. It does not create a recovery manifest, copy data, create destination namespaces, or complete checks that need private migration or a privilege probe. `--json` is available. Empty retained retired tables such as `fleet_cells` are skipped with an inspection note. Populated retired tables refuse the port so their rows cannot be silently discarded; preserve the SQLite store and archive the retired data through its owner before retrying. Unrecognized tables still require complete descriptor coverage. The real port includes registered shared-state, agent, Workboard, Logbook, and Team Reports stores, including retained roots and disabled plugin data. Keep matching separately installed plugins available so their schema owners can be loaded. Source files and config, including includes, are captured under `/recovery/database-port//` before publication. Destination stores are privately migrated and verified; config switches last and preserves SecretRefs. Retain the captures until recovery is confirmed. Cold transcript references and file-artifact paths remain in place. Rebuildable memory indexes are omitted from the agent port; canonical memory origins, tombstones, and the embedding cache are retained. After publication releases port ownership, the command acquires fresh offline ownership and rebuilds each ported agent through the selected memory plugin. Agents rebuild concurrently, up to four at a time, bounded by the host's CPU count and free memory; hosts with two or fewer CPUs or under 2 GiB of free memory rebuild one agent at a time. Unchanged cached chunks reuse their embeddings. Per-agent results appear in the command output and `--json`. The rebuild stays offline, so start the Gateway after the command finishes. A rebuild failure warns without failing the completed port and includes `openclaw memory index --agent ` to retry while the Gateway remains stopped. Retained stores outside the active agent configuration require that original configuration before rebuilding. Check Memory Core before relying on recall: ```bash openclaw memory status ``` These commands require offline ownership. See the [memory CLI](/cli/memory) for per-agent selection and other memory plugins. Then start the Gateway through its service owner and run Doctor, status, and health. ### Recover an interrupted port An incomplete operation blocks startup using the active manifest at `/recovery/database-port/manifest.json`. Keep the Gateway stopped and choose one action: ```bash openclaw database port --resume /recovery/database-port/manifest.json openclaw database port --rollback /recovery/database-port/manifest.json ``` Resume revalidates captured evidence and finishes the operation. Rollback restores its original stores and config. These options are mutually exclusive and cannot be combined with `--family`, `--to`, or `--dry-run`. Preserve unreadable or damaged manifests and their captures for diagnosis. A completed, archived manifest is not an undo operation after runtime writes have resumed. ### Understand refusals Port refuses live or unverifiable writer ownership, an ambiguous unscoped mixed selection, pending legacy migrations for the affected families, unsupported stores or source schema, schema drift, mismatched anchors, insufficient namespace ownership/privileges, and changed recovery evidence. PostgreSQL cannot represent NUL-containing text; invalid source data can therefore refuse a port. Duplicate-key voice JSON also refuses rather than silently losing data. Follow the reported repair and retain the original data; never edit metadata or discard an anchor to force admission. ## Backup and restore ### Capture both server data and local state Run this while the Gateway is up or stopped: ```bash openclaw backup create --output ~/Backups/openclaw --verify ``` With the Gateway running, each PostgreSQL store is exported from its own `REPEATABLE READ READ ONLY` snapshot while the Gateway keeps serving; stores are not captured at one shared instant. Sequence allocation facts are read inside that snapshot, so a restored store never reissues a captured row ID. The archive combines local files and SQLite images with each actual PostgreSQL store's matched **format-2 envelope**: original anchor, portable SQLite export image, and manifest containing identity, schema versions, active fragments, allocation facts, sizes, and hashes. The DSN is not in that envelope. The surrounding archive contains config and may contain other credentials; protect it accordingly. Supply external SecretRef values separately during recovery. User backups apply the owning stores' sanitization rules. They are not the exact unsanitized captures used for updater recovery. SQLite and PostgreSQL Git exports apply the same `--exclude-secrets` policy, including credential tables, machine-state keys, generated model-catalog credentials, and identified bundled plugin credential namespaces. Nonsecret plugin state and blobs remain in redacted exports; see [Git backup redaction](/cli/backup#versioned-git-backups) for the credential scopes and restore requirements. Keep Logbook images, cold transcript blobs, workspaces, config includes, and local state with the database backup. Neither a server dump alone nor a copied anchor covers the installation. ### Restore all PostgreSQL stores `openclaw backup verify ` checks the PostgreSQL capsules declared by the backup producer, including plugin stores and retained Matrix roots. The archive records each generated capsule's archive path and manifest hash; verification checks that binding, its artifact hashes, and its matching anchor. `openclaw backup restore --target ` verifies and extracts into a fresh staging directory, then imports every captured PostgreSQL store into a fresh namespace: shared state, agents, Workboard, Logbook, Team Reports, and retained Matrix roots. It publishes matching anchors only after all stores pass their providers' verification. A failed restore discards its new namespaces and cleans the incomplete staging tree. Original namespaces are left untouched. Restore imports and consumes only producer-declared capsules. A copied export in a workspace or another archived directory is ordinary user data: restore preserves it without importing it or deleting its members. Consuming generated capsules after successful import lets a restored installation create another full backup without colliding with the previous exports. Older archives without the capsule inventory can automatically recover only the global and agent snapshots declared by their existing database inventory. Other exports remain unchanged as files. Verification and restore warn when an older archive contains unlisted exports: re-run backup with this version on the source installation to capture plugin PostgreSQL stores with ownership metadata before activating a full recovery. Unmanaged SQLite files also remain opaque. Archive restore validates its captured configuration under offline custody and uses its family selections. Supply the captured configuration's external connection SecretRef before restoring; point that secret at the recovery server when recovering to another server. The live installation's selections do not override the archive. Restore publishes the recovered configuration last, after all stores and their matching anchors pass verification. `openclaw backup sqlite restore` remains SQLite-only. Git restore supports `--global`, `--agent `, and `--store ` for recorded plugin or additional-root identities. Use the identity recorded under `stores/` in the Git repository. Each restore creates a fresh namespace and publishes its matching local anchor after verification. ### Recover a retained agent store An agent's local PostgreSQL anchor and its initialized server namespace form one store. `agents add`, Gateway `agents.create`, and guided creation refuse a retained anchor whose namespace is missing, uninitialized, or belongs to another identity. They leave its anchor, requested workspace, and configuration unchanged instead of creating an empty replacement under the retained identity. 1. Check that `database.postgres.connection` resolves to the intended server and database. Restore connectivity or the correct connection if the store still exists there. 2. If the namespace was lost, stop the Gateway and embedded writers and restore a matching backup: ```bash openclaw backup restore --target ``` Restore uses fresh namespaces and publishes their matching anchors. Activate the restored installation as a matched set; do not pair an old anchor with a newly restored namespace. See [archive restore](#restore-all-postgresql-stores) or [selected-store recovery from Git](#disaster-recovery-from-git). 3. If the history is intentionally gone and a fresh agent is wanted, keep writers stopped and move the exact anchor named in the error, together with any of its `-wal`, `-shm`, or `-journal` sidecars, to a recovery directory outside the state and agent directories. Then rerun the same `openclaw agents add` command. Move only that store's files; preserve the old files until the new agent is verified. This explicitly discards the old identity rather than restoring its history. New agent anchors are published only after PostgreSQL commits the schema and agent identity, so an interrupted first creation can be retried. Older versions could leave an anchor before that initialization completed. Such an anchor cannot be distinguished from lost retained history and uses the same conservative recovery procedure; there is no automatic discard or migration. ### Disaster recovery from Git Prepare explicit Git recovery points while offline, alongside the full archive: ```bash openclaw backup git init --repository ~/Backups/openclaw-git openclaw backup git create --repository ~/Backups/openclaw-git --all --exclude-secrets openclaw backup git log --repository ~/Backups/openclaw-git ``` `--all` captures configured agents and discovered PostgreSQL plugin stores and retained roots with distinct identities. Record the resulting commit and keep the repository private. Git database backups do not replace file/config backups. `openclaw backup git verify` checks PostgreSQL export hashes and the matched anchor locally. It does not connect to PostgreSQL or create a scratch namespace. New Git captures also bind the source store to the scope manifest, so verification and restore reject a capsule copied from another store. Older captures remain readable; keep their complete scope artifacts together when editing Git history. SQLite verification retains its private restore and integrity checks. To recover these scopes: 1. Keep all writers stopped. Provision a compatible PostgreSQL server, a fresh database, runtime role, and required extensions as above. 2. Set the recovery installation's state/config paths and agent directories; supply its external connection secret separately, pointing at the recovery database. Preserve the matching `database.engine` and `database.families` selections, and use a compatible OpenClaw version and matching plugins. Recover the matching non-database files alongside the Git snapshots. A full archive restore already rehydrates its PostgreSQL stores; use Git restore when recovering selected database scopes instead. 3. Restore each Git scope from the **same recorded commit** to a nonexistent local target, outside the backup repository: ```bash openclaw backup git restore --repository ~/Backups/openclaw-git --ref --global --target ./recovered/openclaw.sqlite openclaw backup git restore --repository ~/Backups/openclaw-git --ref --agent main --target ./recovered/main/openclaw-agent.sqlite openclaw backup git restore --repository ~/Backups/openclaw-git --ref --store workboard --target ./recovered/workboard.sqlite ``` Each PostgreSQL restore loads a fresh namespace using the configured connection and publishes a matching local anchor only after verification. It does not overwrite an existing target or restore into a live namespace. 4. Put those verified anchors at the recovery installation's corresponding shared-state, agent, or plugin database paths, replacing only staged historical anchors while offline. Preserve the old installation and namespaces until recovery is confirmed. Restore every required store before starting the installation. 5. Run `openclaw doctor`, start through the service owner, then check `openclaw health`, `openclaw status`, representative sessions, search, and enabled plugins. Review pending approvals and channel relinking needs after rollback; a backup restores historical state. ## Published-stable upgrade qualification The qualification below used [OpenClaw 2026.10.5](https://www.npmjs.com/package/openclaw/v/2026.10.5). Its packaged configuration schema does not support `database.engine=postgres`, so a native 2026.10.5-PostgreSQL → current upgrade is **not applicable**. Users on that version adopt PostgreSQL by updating their SQLite installation first, then using `openclaw database port --to postgres`. Published **2026.11.1** supports PostgreSQL for Workboard only. Its database port command writes `database.engine: "postgres"` while leaving shared state and agent data on SQLite. Current config reads preserve that meaning when no `database.families` overrides exist, shared state contains SQLite state objects, and Workboard has a PostgreSQL anchor. They expose the equivalent current config, `database.engine: "sqlite"` with `database.families.workboard.engine: "postgres"`, and log a warning. No database is moved by a config read, and the source config remains readable without a manual edit. Config writes record that explicit intent through the normal backup flow. A scoped database port records it before moving Workboard data, so replacing its PostgreSQL anchor cannot change the shared-state engine. Later reads no longer need the compatibility inspection. Empty shared-state files, PostgreSQL shared-state anchors, and explicit family overrides retain their configured meaning. This compatibility behavior is separate from the 2026.10.5 upgrade qualification below. The local `PGQUAL-MAJORS-UPGRADE` lane installed that published package in an isolated npm global prefix, onboarded on SQLite, created an agent and persisted session/memory data, and made a verified backup. The installed stable updater activated the packed candidate from `b1d415c2448f` in run `d3b04126-11d3-4f28-9dd0-146f9fe914b5` and exited successfully. The installed build identity was compared with the tarball, since both packages report version `2026.10.5`. The candidate archive SHA256 was `262bcd6929ded71c7066752438b7768deca990e1dddb6457905256158abd7491`. | Cell | Result and evidence | | ----------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | 2026.10.5 PostgreSQL → current | Not applicable: 2026.10.5 has no PostgreSQL configuration schema. | | Published stable SQLite → current | Passed through the installed updater; run `d3b04126-11d3-4f28-9dd0-146f9fe914b5`. | | Current SQLite → PostgreSQL 17 | Passed; port `aae1aaff-ec7e-4dac-afe6-7b46f8589867` moved the populated shared and agent stores and retained recoverable SQLite source captures. | | Foreground Gateway and continuity | Passed: Gateway readiness, Doctor/status, agent listing, the existing session and memory search. Doctor also returned exit 2 with `ok: false` for an unreachable DSN. | | Rollback to published stable SQLite | Passed: restored the verified pre-upgrade backup and matching stable package, then checked the exact build, foreground Gateway, Doctor/status, agent, session and memory. The direct downgrade dry run reported unsupported state/agent schemas; it was not a PostgreSQL downgrade. | | Live model turns | Skipped: no provider key was available. | No provider key was available, so live model turns were skipped. Service activation was covered by the separate signed-Mac/Parallels proof, not by this local run; this lane uses foreground Gateways and does not install a service. The native-client support rows above retain their own qualification scope. ## Updates and schema migrations Use `openclaw update` for supported update admission. The [published-stable qualification](#published-stable-upgrade-qualification) covers SQLite update, PostgreSQL adoption and matched-backup rollback, with foreground Gateway verification. Its service and live-turn limits still apply. Before stopping the previous Gateway or changing data, it checks the target's declared engine and PostgreSQL family support against selected families and retained physical stores. Explicit family overrides also require successful candidate admission of the complete selection; global PostgreSQL support alone is insufficient. A target without that proof is refused. A raw package-manager downgrade does not provide this guard. Agent schema admission recognizes the local PostgreSQL anchor and reads the matching server store's version without migrating it. An older server schema can therefore reach the stopped-writer migration phase; a newer schema or mismatched anchor identity still refuses the update. Update Doctor and `openclaw update repair` select the configured engines before reading maintenance state. Doctor inspects configured PostgreSQL agents without opening them as runtime stores, then applies their forward migrations with writers stopped. Transcript migration detection uses the selected engine's catalog. The local anchors remain identity files; SQLite repair and reclamation leave them untouched. Gateway startup still requires the completed PostgreSQL migration. The delegated update Doctor installs the same one-shot database role as standalone Doctor before maintenance, so its PostgreSQL reads and cleanup can finish. This repair must be present in the candidate runtime; Gateway access remains worker-only. Before mutation, the updater captures exact PostgreSQL rows, sequence allocation state, schema facts, and matching anchors alongside local SQLite state and config. Recovery restores into fresh namespaces and publishes matching anchors. It preserves current update-run history. Independent writes, changed recovery generations, or uncertain custody/commit outcomes prevent automatic restore and retain captures for recovery. This is not an unconditional rollback guarantee. See [update compatibility and recovery](/reference/database-engines#updates-and-target-compatibility). Store owners run consecutive forward schema migrations under a writer transaction. Admission verifies the generated destination schema before publication. Missing migrations, newer schemas, and drift refuse admission; use a compatible binary or restore a matched backup. Never edit version/fingerprint metadata. Doctor's state migrations take their own exact backup before mutation. PostgreSQL schema upgrades run through `openclaw doctor --fix` or the normal update flow with writers stopped. A direct Gateway start on an older schema refuses and directs you to Doctor. With the Gateway and embedded writers stopped, run `openclaw doctor --fix` before restarting. Doctor takes a backup and applies the consecutive migrations needed by each store: - State 32 and agent 34 add nullability-aware PostgreSQL index ordering. - State 33 and agent 35 extend that ordering to partial indexes whose predicates prove a key is non-null. Logbook 3 applies the same correction when its service opens the store. - Agent 36 adds the three memory keyword path indexes described below. The ordering migrations rebuild only changed indexes; agent 36 creates only its three new indexes. SQLite advances version metadata without changing its indexes for these migrations. See the [ordering migration contract](/reference/database-schemas/storage-changes#postgresql-index-ordering-migrations) and [agent schema 36](/reference/database-schemas/agent-schema-history#agent-schema-36). Older binaries require the verified pre-upgrade backup and matching build; reinstalling an older package does not reverse these migrations. After a PostgreSQL major-version upgrade, `REINDEX` the `idx_memory_index_sources_exact_path`, `idx_memory_index_sources_basename`, and `idx_memory_index_sources_stem` indexes because `normalize()` follows PostgreSQL's Unicode tables. First boot, ports, rebuilds, and migrations can take longer on slow disks or a remote server. Connection waits allow 60 seconds. On runtime store connections, OpenClaw sets session-level `lock_timeout` to 60 seconds and `statement_timeout` to 90 seconds. These liveness bounds override role, database, and DSN startup settings. The synchronous bridge has a 120-second deadline. If it expires, the process root terminates the whole I/O service, resetting every connection assigned to that service (roughly a quarter of the process's PostgreSQL connections) with an unknown outcome. Runtime statements time out after 90 seconds, so the bridge deadline covers a wedged service or a black-holed network. Offline port and restore materialization use a direct asynchronous client with a one-hour statement timeout; those statements do not pass through the synchronous bridge. These are not per-operation config options. A timeout is a failure to investigate, not permission to delete recovery state. Inspect server load, locks, network, and storage, and retry only after resolving the reported cause. OpenClaw inherits the operator's `synchronous_commit` and other durability settings. PostgreSQL's default `on` makes acknowledged commits durable across PostgreSQL or operating-system crashes. For SQLite WAL `synchronous=NORMAL`-like throughput, operators can opt in with `ALTER ROLE SET synchronous_commit = off` or the equivalent `ALTER DATABASE` setting. This can lose recently acknowledged commits on a server or operating-system crash, without corruption. Ports and backups always commit durably. ## Search on PostgreSQL Full text uses PostgreSQL's `simple` plus `unaccent` configuration and native ranking. Trigram mode performs literal contains matching with native similarity ranking. Memory vector search is approximate: a stored binary sketch provides a fast Hamming-distance prefilter, followed by exact float32 cosine reranking through pgvector. Model, source and exclusion filters apply before preselection; small candidate-ID scopes use exact search. This recall-tuned behavior is on by default with no extra configuration option. SQLite memory vector search stays exact, so semantic neighbors need not match across engines. Semantic memory requires pgvector 0.7 or newer. The agent schema migration backfills the generated sketches without building an HNSW index; ordinary writes maintain them automatically. Canonical embeddings, zero-vector eligibility and the result shape are unchanged, and backup/port operations rebuild the sketches. Scores are engine-local. Expect different ordering from SQLite BM25 and different Unicode normalization; punctuation runs are replaced by spaces before indexing and querying, so words inside dates, paths, and file names match as on SQLite. Hybrid retrieval keeps the existing text/vector weighting, not cross-engine score equivalence. Memory full-text rank uses unnormalized frequency ranking (`ts_rank`) and maps its rank `r` to `r / (r + 0.0027)` before the shared hybrid merge. This calibration makes keyword relevance contribute meaningfully alongside vectors. It does not include corpus-wide inverse document frequency (IDF), so common-term and mixed-frequency queries remain a ranking limitation compared with SQLite BM25. Memory keyword search retrieves body, exact-path, and broad-path candidates in one statement, retaining each branch's ranking and limit. That statement includes the match-any body fallback, used only when both body and path searches return no matches. Memory transcript visibility retains the same 200-candidate ACL postfilter as SQLite; it does not promise complete recall beyond that candidate window. The separate transcript search API applies its agent/session/role filters before its result cap. See [search modes and limits](/reference/database-engines#search-on-postgresql). Transcript search keeps its agent prefix literal in the prepared query so PostgreSQL retains that selectivity estimate when it chooses a generic plan. Other query inputs stay parameterized; server-wide plan policy and search results are unchanged. Without pgvector, explicitly select keyword-only memory before opening its store: ```json5 { memory: { search: { provider: "none" }, }, } ``` Memory status then reports `provider: "none"` and `searchMode: "fts-only"`. Missing pgvector or an embedding-provider failure does not silently choose this mode. Missing `unaccent` or a required `pg_trgm` refuses admission rather than returning empty search results. Reopen the store after installing the extension. ## Performance evidence The recorded PostgreSQL 17 warm-turn census at `f5de32afc6a5` used 16.67 worker requests, 68.67 round trips and 82.67 SQL statements per traced turn. Its 64-session run completed 320/320 turns at 5.04 turns/s, with p95 16.86 seconds. A later census at `d5aeceb2b89e` reduced traced work to 72.33 SQL statements and 59.33 round trips; the matched SQLite workload used 52 statements. PostgreSQL's transaction and advisory-lock work accounts for part of that difference. These are workload and host measurements, not a latency SLA. A later apparent fourfold concurrency slowdown reproduced on an unchanged control under host load; the remaining roughly 5% difference was confounded by optional cost-summary work. Measure network latency, concurrency and server load for your deployment. The [four-service I/O pool qualification](https://github.com/openclaw/openclaw/pull/182888) measured a synthetic 20-agent installation at 7,714.1 → 1,950.7 MiB idle process-tree RSS and 337 → 12 I/O workers across three process roots. It did not reduce server connections: idle backends were 144 → 146. These are fixture measurements on the original pool candidate, not a memory budget for a deployed Gateway. The accepted follow-up measured about 0.1 ms higher foreground query p95 and a 25.2% higher cold-ready p95 across five paired runs, alongside a lower cold-ready median. The footprint reduction trades some query latency for memory; the pool does not promise faster queries. ## Monitoring and troubleshooting Startup rejects invalid configuration before readiness and reports the fields that need repair. With PostgreSQL selected, invalid Gateway configuration exits with status 78 (`EX_CONFIG`) and a Doctor hint. An unreadable config file instead exits with status 1. For retired settings such as `agents.defaults.heartbeat`, run `openclaw doctor --fix` before starting the Gateway again. During startup, existing-state readers can briefly retry while committed schema admission facts are still being published. This wait lasts at most one second, bounded by the caller's deadline and cancellation. Persistent admission failures remain visible; the retry does not replay application operations. ### Recover missing local client credentials If PostgreSQL state survives but `client/credentials.sqlite` is missing, restore its matching backup or run `openclaw doctor --fix`. Doctor asks before creating replacement local identities. Use `openclaw doctor --fix --yes` to accept that repair without prompting; `--non-interactive` alone leaves the identities unchanged. Clients on this host must pair again after replacement. Remote devices that kept their credentials retain their pairing records in PostgreSQL. Gateway startup does not replace the missing identities itself. This repair does not overwrite an existing mismatched, corrupt, or incomplete credential store. ### Read the Doctor and status store report `openclaw doctor` enumerates physical stores through the same family and plugin resource descriptors used by database port. The report includes shared-state roots, every discovered agent database, Workboard, Logbook, Team Reports, and retained Matrix account/token roots. Quarantine, managed-update, package-activation, and local credentials are labeled **local by design**. Read `default`, `override`, and `effective` as configuration; `observed` comes from the physical SQLite file or PostgreSQL anchor. A mismatch means changing configuration did not migrate existing data. Keep the Gateway stopped and use `openclaw database port --family --to `, or restore the matching configuration. Never delete an anchor to clear a mismatch. `schema`, `expected`, and `pending` compare observed metadata with the installed store owner's descriptor. A version match alone does not prove complete schema integrity. Readable state and agent schema versions remain visible when port validation refuses an older or newer schema. Unreadable files and directories remain in the inventory with unknown contents and a permission-repair step; they are not reported as absent. Missing resolved store files remain listed with initialization guidance. Unversioned local journals are identified separately. Installed server extensions and recorded admitted search extensions are separate facts; `degraded` identifies recorded keyword-only operation and missing vector support. Install a named missing extension as a database administrator, grant `USAGE` on its schema to the runtime role, and reopen the store through its owner. A PostgreSQL anchor can remain readable while the server is down. In that case `observed=postgres` and the namespace remain visible, but connectivity is unreachable and server schema, extensions, and lease information stay unknown. Check the PostgreSQL service and port, network/TLS settings, and `database.postgres.connection` or its SecretRef. Raw connection strings and connection errors are omitted from the report. `custody` and `lease` distinguish a verified live owner, no owner, and unverifiable ownership. These are observations, not permission to take over a store. `backup=unknown` means no matching recorded outcome was observed; it does not assert that no backup exists. The installation backup ledger records backup destinations, which do not establish coverage of an individual physical store. `legacy=pending` names imports that must complete with `openclaw doctor --fix` before porting. If a detector or store is unavailable, `unknown` includes a next step instead of claiming success. Doctor's JSON includes every inspected store in `databaseStores`, even when its finding is below `--severity-min`. Visible store findings also include the same fields in `details`. Status JSON includes `databaseEngines.stores`, alongside the configured `families`. Its `source=admitted` rows consume the owners' existing admission facts, without opening stores or probing PostgreSQL. They can show a recorded engine and schema version while connectivity, backup, or lease state remains unknown. An unopened or unadmitted store remains unknown. Configured agent paths stay listed before admission, without filesystem probes. Status uses the selected Gateway's projection, including remote Gateways; if that projection is unavailable, it directs you to run Doctor on the Gateway host. Doctor's bounded, read-only inspection supplies fresh physical evidence without adding Gateway hot-path probes. For example, this excerpt from a read-only inspection catches an unported SQLite state store after PostgreSQL was selected (paths shortened; other fields omitted): ```text state (~/.openclaw/state/openclaw.sqlite); default=postgres, override=inherit, effective=postgres; observed=sqlite (mismatch); schema=26, expected=26, pending=0; connectivity=local ``` The same observation is machine-readable in the store finding's `details`: ```json { "family": "state", "observed": "sqlite", "mismatch": true, "schema": { "versioning": "versioned", "version": 26, "expectedVersion": 26, "pending": 0 }, "connectivity": "local", "backup": null } ``` The next step is `openclaw database port --family state --to postgres` with the Gateway stopped, or restoring the matching engine configuration. A current SQLite schema does not make the engine mismatch safe to ignore. Use `openclaw health` and actual enabled feature operations alongside this report. Monitor PostgreSQL availability, connections, locks, storage, and backup success through your database operator's normal monitoring. The following are exact error strings or exact stable prefixes (variable details follow a prefix): | Error text | Next step | | ------------------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------ | | `PostgreSQL requires version 16 or newer and UTF8 server/client encoding; upgrade the server or port to a UTF8 database and set client_encoding=UTF8.` | Upgrade PostgreSQL or select a UTF8 database/client connection. | | `PostgreSQL bootstrap cannot use a store-backed SecretRef; use env, file or exec.` | Move the connection secret to an external bootstrap source. | | `Cannot open PostgreSQL connection:` | Check the service's SecretRef environment, DSN/TLS, network, role, and server availability. | | `PostgreSQL search capability unavailable: insufficient CREATE privilege.` | Restore database/schema creation privileges and store ownership; see requirements. | | `DATABASE_SEARCH_CAPABILITY_UNAVAILABLE` | Follow the named extension installation SQL, or explicitly choose keyword-only memory when only vectors are unavailable. | | `Missing PostgreSQL anchor; restore the matching PostgreSQL schema and anchor.` | Restore the complete matched set. Do not create a replacement anchor manually. | | `Invalid PostgreSQL anchor identity. Restore a matching backup.` | Restore the original matching anchor and server data. | | `PostgreSQL database identity mismatch:` | Restore the matching pair; an anchor from another namespace is not interchangeable. | | `PostgreSQL schema drift detected.` | Use the compatible binary or restore matching data; do not edit metadata. | | `PostgreSQL connection lost; transaction rolled back` | Restore connectivity and reopen the failed store through its owner before retrying. | | `PostgreSQL connection lost; outcome unknown` | Reconcile whether the operation committed before repeating an external effect. | ### Outcome unknown after a connection loss Connection loss closes the failed connection. The engine distinguishes a known rolled-back transaction from an uncertain outcome, invalidating cached facts when a commit may have occurred. It does not switch to SQLite or promise transparent replay of every operation. The shared-state worker retires its failed connection so a subsequent operation can reopen the store once PostgreSQL is available. The interrupted operation is not replayed. Recover server connectivity and inspect the operation's recorded result before repeating an external effect. If another store remains unavailable, restart the Gateway through its service owner. The 120-second bridge deadline also has an unknown outcome and resets the whole I/O service, including its other assigned connections. Check the recorded result through the owning feature after connectivity recovers; a lost reply does not prove that the write rolled back. Do not blindly repeat an operation that could send a message, charge an account, or perform another external effect. ### Slow first search after a bulk import Stale PostgreSQL planner statistics can contribute to slow queries after loading many rows. Database port and its post-port memory rebuild attempt `ANALYZE` after committing verified data. If that best-effort refresh fails, the command warns that the data is retained and autovacuum can refresh statistics later. Check for that warning and have the database operator inspect statistics and autovacuum, or run `ANALYZE` on the affected tables. This is not a required delay after every import: the agent 36 exact-path indexes support indexed lookup even before `ANALYZE`. ## Roll back to SQLite Use the current compatible binary, keep the PostgreSQL secret available, and stop all writers. Run a **new reverse port**: ```bash openclaw database port --to sqlite --dry-run openclaw database port --to sqlite ``` For a mixed installation, port one family at a time instead: ```bash openclaw database port --family workboard --to sqlite --dry-run openclaw database port --family workboard --to sqlite ``` The new port carries writes accepted since the earlier migration. Restoring an old SQLite capture would lose those writes. After success, start the Gateway and verify Doctor, health, and the affected features. Former PostgreSQL namespaces are retained, not dropped automatically. Reverse-port before a downgrade to a build that lacks PostgreSQL support.