PGSandbox

PGSandbox is a local-first Rust CLI and MCP stdio server that lets coding agents create, inspect, use, clone, and delete disposable PostgreSQL databases. It is for engineers who want agents to validate migrations, SQL, seed data, and backend bug reproductions against a real isolated database without touching a shared developer database.

Key Features

• Creates one tracked PostgreSQL database and one scoped login role per task.
• Starts and reuses a managed local Postgres cluster under ~/.pgsandbox/ by default, without Docker and without binding localhost:5432.
• Supports explicit external Postgres admin profiles when you intentionally opt in through environment variables or a JSON config file.
• Enforces positive TTLs, max TTL caps, metadata-backed deletion, and optional per-owner active sandbox quotas.
• Runs user SQL through sandbox role credentials, not the admin connection.
• Returns bounded, typed SQL result sets so agents do not dump unbounded rows.
• Describes schemas, computes schema digests, diffs schemas, creates named schema snapshots, and returns EXPLAIN (FORMAT JSON) plans.
• Runs repo migration, seed, and smoke commands with sandbox credentials injected through environment variables or validated app-specific aliases, plus bounded stdin and output controls instead of temporary repo files.
• Returns host-local and Docker-friendly connection variants so containerized local apps can reach a host-managed sandbox without guessing the right host.
• Creates reusable local template artifacts from PGSandbox-owned sandboxes.
• Writes MCP client config for Codex, Cursor, VS Code, and Claude Desktop.
• Masks admin URLs and sandbox credentials in diagnostics and safe summaries.

Table Of Contents

• Tech Stack
• Prerequisites
• Getting Started
• Development From This Repo
• Architecture
• Environment Variables
• Available Scripts
• Testing
• Deployment
• Troubleshooting
• Contributing
• License

Tech Stack

• Language: Rust 2021 edition
• Runtime: Native CLI binary named pgsandbox
• MCP Framework: rmcp stdio server
• Async Runtime: Tokio
• Database Client: tokio-postgres
• TLS: native-tls and postgres-native-tls for Postgres URLs that use TLS options such as sslmode=require
• Configuration: Serde, JSON, TOML, and environment variables
• Serialization: serde, serde_json, serde_yaml_ng, and schemars
• Security Helpers: aes-gcm for encrypted sandbox role passwords, sha2 for digests/checksums, uuid for ids
• Diagnostics and HTTP: reqwest for telemetry delivery, url for connection-string parsing
• Website: Astro 6, TypeScript, Node.js, and npm under site/
• Testing: Cargo unit tests plus opt-in live Postgres integration tests
• Packaging: GitHub release archives, Homebrew tap packaging, and a hosted install script
• Deployment: Native binary distribution for the MCP server; CapRover deployment for the separate static Astro site

Prerequisites

For normal end-user installation:

• macOS or Linux on x86_64 or aarch64
• Local PostgreSQL server binaries for the managed local runtime. setup checks for them, installs PostgreSQL through a supported package manager when available, and starts the managed local cluster.
• Optional PostgreSQL dump tools for clone/template workflows: pg_dump and pg_restore
• One MCP client: Codex, Cursor, VS Code, Claude Desktop, or another client that can launch stdio MCP servers
• Homebrew, curl, or wget if installing a released binary

For repository development:

• Git
• Rust toolchain compatible with the repo. CI uses Rust 1.91.1.
• Node.js 22 or newer. CI uses Node 22; mise.toml pins Node 24.15.0 for local toolchain management.
• npm
• PostgreSQL server binaries available on PATH, in a common package-manager location, or through PGSANDBOX_POSTGRES_BIN_DIR

PGSandbox checks PATH, common package-manager locations such as /opt/homebrew/opt/postgresql/bin, /usr/lib/postgresql/<major>/bin, versioned Homebrew kegs from postgresql@18 through postgresql@13, Postgres.app locations, and explicit bin dir environment variables. Homebrew kegs do not need to be linked globally if PGSandbox can discover the opt path.

Docker is not required. docker-compose.example.yml is only a demo helper for users who intentionally want an external local Postgres profile.

Getting Started

These steps are for a normal user who wants to install the released pgsandbox binary and use it from an MCP client. You do not need to clone this repository, install Rust, or run Cargo for the standard setup.

1. Install PGSandbox

Homebrew is the recommended install path:

brew install LVTD-LLC/tap/pgsandbox

If you do not use Homebrew, install the latest GitHub release binary with the hosted installer:

curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh | sh

If the installer uses ~/.local/bin, make sure that directory is on your PATH before continuing.

Verify the installed binary:

pgsandbox --version

2. Run Setup

Pick the client you use:

pgsandbox setup --client codex
pgsandbox setup --client cursor
pgsandbox setup --client vscode
pgsandbox setup --client claude-desktop

setup does the normal local setup work for you:

• checks for the PostgreSQL server binaries the managed runtime needs
• installs PostgreSQL with a supported package manager when those binaries are missing and one is available
• initializes and starts the managed local Postgres cluster under ~/.pgsandbox/
• writes the MCP client config

For Cursor or VS Code project-local config:

pgsandbox setup --client cursor --scope project
pgsandbox setup --client vscode --scope project

By default, setup does not write PGSANDBOX_ADMIN_DATABASE_URL. That is intentional: the MCP server will use the managed local Postgres cluster under ~/.pgsandbox/.

Only pass --admin-url when you intentionally want to use an explicit external local/private Postgres admin profile:

pgsandbox setup --client codex --admin-url "$PGSANDBOX_ADMIN_DATABASE_URL"

3. Restart Your MCP Client

Restart Codex, Cursor, VS Code, or Claude Desktop after setup. MCP clients cache server metadata and usually do not notice a newly configured server until restart.

In Codex, run:

/mcp

Verify that the pgsandbox server appears.

4. Optional Verification

Run diagnostics:

pgsandbox doctor

Run the disposable end-to-end check:

pgsandbox smoke-test

The smoke test creates a sandbox, runs SQL, validates serialization behavior, and deletes the sandbox before exiting. Its default output is a compact list of pass/fail results. Use pgsandbox smoke-test --verbose to include the full structured SQL result dictionaries for diagnostics.

You can also inspect the managed local runtime directly:

pgsandbox local status
pgsandbox local start

The runtime starts at port 65432 and scans upward for a free port. It should not collide with Docker or another developer Postgres already using 5432.

5. Use A Sandbox

Ask your agent to create a disposable Postgres sandbox for the task. A typical MCP workflow is:

1. Create a sandbox with create_database.
2. Run SQL with run_sql or run a repo command with run_repo_command.
3. Inspect schema with describe_schema or schema_digest.
4. Delete the sandbox with delete_database, or let TTL cleanup remove it later.

Agents that prefer shell commands can run the same tool contracts directly through the CLI:

pgsandbox create-database --name-hint "migration check" --ttl-minutes 30
pgsandbox run-sql --database-id "$DATABASE_ID" --sql "select 1" --readonly
pgsandbox delete-database --database-id "$DATABASE_ID"

Every MCP tool has a CLI command. Use hyphenated command names for normal shell usage, or call the exact MCP tool name through tool:

pgsandbox schema-digest --database-id "$DATABASE_ID"
pgsandbox tool schema_digest --input "{\"databaseId\":\"$DATABASE_ID\"}"
pgsandbox run-repo-command --database-id "$DATABASE_ID" --repo-path "$PWD" -- npm run migrate

--input and --input-file accept the same camelCase JSON objects documented for MCP tools, so the CLI can reach the full tool surface without adding a custom flag for every field.

If your application runs inside Docker while PGSandbox runs on the host, call get_connection_string with includeCredentials: true and pass connectionStrings.localContainer to the app service as DATABASE_URL. Keep the raw value out of chat, logs, issues, PR comments, and task trackers. Docker Desktop supports the returned host.docker.internal host automatically. On Linux Docker, add this to the service:

extra_hosts:
  - "host.docker.internal:host-gateway"

For run_repo_command, set connectionMode: "localContainer" and add app aliases such as databaseUrlEnvNames: ["DATABASE_URI"]; PGSandbox injects the Docker-friendly URL without returning the raw value in command metadata.

For direct CLI troubleshooting, this command starts the MCP server over stdio:

pgsandbox mcp

You normally do not run it yourself; your MCP client launches it.

Development From This Repo

Use this section when contributing to PGSandbox, testing unreleased changes, or pointing an MCP client at a local development build. Normal users should use the packaged setup in Getting Started.

1. Clone The Repository

git clone https://github.com/LVTD-LLC/pgsandbox.git
cd pgsandbox

2. Install Toolchains

If you use mise, the repo already declares tool versions:

mise install

Without mise, install Rust and Node manually:

rustup toolchain install stable
node --version
npm --version

The site requires Node >=22.12.0.

3. Install JavaScript Dependencies

The root package is mostly a command runner for Cargo and packaging scripts. The website has its own package under site/.

npm ci
npm --prefix site ci --include=dev

4. Build And Check The CLI

cargo build
cargo run -- doctor
cargo run -- smoke-test

The development binary is created at:

target/debug/pgsandbox

For an optimized release build:

cargo build --release

5. Configure An MCP Client For Local Development

For development, point the MCP client at the binary in this checkout so it does not accidentally launch a separately installed release:

cargo build
cargo run -- setup --client codex --command "$(pwd)/target/debug/pgsandbox"

Other supported clients:

cargo run -- setup --client cursor --scope project --command "$(pwd)/target/debug/pgsandbox"
cargo run -- setup --client vscode --scope project --command "$(pwd)/target/debug/pgsandbox"
cargo run -- setup --client claude-desktop --command "$(pwd)/target/debug/pgsandbox"
cargo run -- setup --client all --command "$(pwd)/target/debug/pgsandbox"

Use --dry-run to inspect the config without writing it:

cargo run -- setup --client codex --dry-run --command "$(pwd)/target/debug/pgsandbox"

Restart the MCP client after setup. MCP clients cache tool metadata.

6. Verify MCP Server Startup Manually

The binary starts the stdio server by default:

cargo run

Equivalent explicit form:

cargo run -- mcp

You usually do not run this command directly because the MCP client owns the stdio transport. Use it only when checking startup failures.

7. Run The Documentation Site

The Astro website lives in site/.

npm --prefix site run dev

Build the site:

npm run site:build

No build-time environment variables are required for the site.

Architecture

Directory Structure

.
|-- rust-src/
|   |-- main.rs              Binary entrypoint
|   |-- cli.rs               CLI dispatch, setup, doctor, local runtime commands, smoke test
|   |-- mcp.rs               MCP server, public tool registration, response envelopes
|   |-- config.rs            Env and JSON config loading, profile validation
|   |-- local.rs             Managed local Postgres init/start/stop/status
|   |-- postgres.rs          Sandbox lifecycle, SQL, schema, repo workflow, templates
|   |-- names.rs             Identifier generation and SQL quoting helpers
|   |-- doctor.rs            Diagnostics and connection-string masking
|   |-- setup.rs             MCP client config writers
|   |-- telemetry.rs         Anonymous usage telemetry
|   `-- lib.rs               Library exports and package version
|-- docs/
|   |-- architecture.md      Resource model and backend notes
|   |-- mcp-tools.md         MCP tool contracts
|   |-- install.md           Install and setup guide
|   |-- homebrew.md          Homebrew packaging notes
|   |-- agent-workflows.md   Copyable agent workflow examples
|   `-- open-questions.md    Product and architecture questions
|-- tests/
|   |-- dogfood_reliability.rs
|   |-- extensions.rs
|   `-- run_sql_serialization.rs
|-- scripts/
|   |-- install.sh
|   |-- package-homebrew.sh
|   |-- package-release.sh
|   `-- update-homebrew-formula.sh
|-- site/                    Astro documentation and marketing site
|-- .github/workflows/       CI, site deploy, and Homebrew tap update workflows
|-- Cargo.toml               Rust package metadata
|-- package.json             Root npm command wrappers
|-- docker-compose.example.yml
|-- .env.example
`-- README.md

Runtime Shape

PGSandbox is one native binary with multiple command modes:

User or MCP client
        |
        v
pgsandbox CLI
        |
        +-- mcp stdio server
        +-- direct CLI tool commands
        +-- setup config writer
        +-- doctor diagnostics
        +-- local Postgres runtime manager
        `-- smoke-test verifier

The MCP server talks to one selected Postgres profile at a time unless the caller explicitly requests all-version listing or cleanup. The default profile is a managed local Postgres cluster. External profiles are opt-in.

Agent / MCP client
        |
        v
PGSandbox stdio MCP mode
        |
        v
Managed local cluster or explicit Postgres admin profile
        |
        v
Tracked sandbox databases and scoped sandbox roles

Entry Points

• rust-src/main.rs starts Tokio and calls pgsandbox::cli::run.
• rust-src/cli.rs defaults to MCP stdio when no command is provided.
• rust-src/mcp.rs exposes the public MCP tools.
• rust-src/postgres.rs owns lifecycle behavior and database interactions.

CLI commands:

pgsandbox
pgsandbox mcp
pgsandbox setup [options]
pgsandbox doctor [options]
pgsandbox create-database [options]
pgsandbox run-sql [options]
pgsandbox delete-database [options]
pgsandbox tool <mcp_tool_name> --input '{...}'
pgsandbox local init [options]
pgsandbox local start [options]
pgsandbox local stop [options]
pgsandbox local status [options]
pgsandbox smoke-test [options]
pgsandbox uninstall [options]
pgsandbox --version
pgsandbox --help

Managed Local Runtime

When neither PGSANDBOX_ADMIN_DATABASE_URL nor PGSANDBOX_CONFIG is set, PGSandbox initializes and starts a local Postgres cluster:

• root: ~/.pgsandbox by default
• default profile: local
• data directory: ~/.pgsandbox/postgres/data
• private runtime config: ~/.pgsandbox/local-postgres.json
• default port search start: 65432
• Unix socket directory on Unix: short PGSandbox-owned path under /tmp/pgsandbox-sockets/
• admin user: pgsandbox_admin

Versioned local profiles use separate state:

Postgres 18 profile: local-pg18
Config:              ~/.pgsandbox/local-postgres-18.json
Data:                ~/.pgsandbox/postgres/versions/18/data
Log:                 ~/.pgsandbox/postgres/versions/18/postgres.log

Start a specific installed version:

pgsandbox local start --postgres-version 18

PGSandbox probes common local install paths for installed Postgres 18, 17, 16, 15, 14, and 13 binaries. Explicit PGSANDBOX_POSTGRES_<MAJOR>_BIN_DIR settings can still target any numeric major version that reports matching server binaries.

In MCP tools, agents should usually omit profile and pass only postgresVersion:

{ "postgresVersion": "18" }

Supplying both profile and postgresVersion is reserved for exact profile targeting. A mismatch returns a structured version_mismatch error.

Profiles

A profile describes an admin connection PGSandbox may use for lifecycle and metadata operations. Profiles can be:

• managed local profiles created by PGSandbox
• explicit local Postgres URLs
• explicit private remote Postgres URLs, only with external-host opt-in
• versioned profiles carrying postgresVersion metadata

Admin connections are used for:

• metadata table setup
• role creation
• database creation
• database deletion
• cleanup
• audit events

User SQL and repo commands use sandbox role credentials generated for the specific database. Requested sandbox extensions are installed through the same sandbox role connection after the database is created, not through the admin connection.

Resource Model

Each sandbox gets:

• a UUID databaseId
• one database
• one login role
• one generated role password
• one owner string, if supplied
• one purpose/name hint, if supplied
• JSON labels, if supplied
• optional installed extensions requested at creation time
• a createdAt timestamp
• an expiresAt timestamp
• a deletedAt timestamp after deletion

Generated database and role names use the configured prefix, a slugified name hint, and a short random id while staying within Postgres's 63 byte identifier limit:

pgsandbox_<hint>_<short_id>
pgsandbox_<hint>_<short_id>_role

All generated SQL identifiers and literals go through helpers in rust-src/names.rs.

Metadata And Audit Tables

PGSandbox stores lifecycle metadata in the admin database for the selected profile.

pgsandbox_databases:

Column	Purpose
database_id	Stable sandbox id returned to agents
profile_name	Profile that owns the sandbox
database_name	Generated Postgres database name
role_name	Generated Postgres login role
role_password	Encrypted sandbox role password
owner	Optional agent/session/user owner
purpose	Optional name hint or task purpose
labels	JSON metadata for repo, branch, task, suite, etc.
created_at	Creation timestamp
expires_at	TTL deadline
deleted_at	Deletion marker

pgsandbox_events:

Column	Purpose
event_id	Event UUID
profile_name	Profile for the event
database_id	Sandbox id
database_name	Sandbox database name
role_name	Sandbox role name when applicable
event_type	Lifecycle event such as create_database or cleanup_expired
details	Small JSON details
created_at	Event timestamp

Destructive operations must find a live metadata row before dropping a database or role. PGSandbox does not drop arbitrary databases by name.

MCP Response Envelope

Every public MCP tool returns JSON text using a compact envelope:

{
  "ok": true,
  "summary": "Tool completed successfully.",
  "warnings": [],
  "errors": [],
  "detailHandles": [],
  "result": {}
}

Workflow tools can also include changedObjects. Tool failures use the same shape with ok: false, stable error code, a broad category, a human message, and a hint. Expected categories include:

• validation
• database_not_found
• version_mismatch
• restore_incompatible
• sql_analysis
• sql_syntax
• constraint_violation
• readonly_violation
• template_not_found
• timeout

Postgres errors include SQLSTATE when available.

Public MCP Tools

Tool	Purpose
list_profiles	List configured profiles and discovered local Postgres versions.
ensure_postgres	Install missing local Postgres server binaries with a supported package manager when available, then start a managed local profile.
doctor	Return MCP-safe diagnostics and profile health.
list_extensions	List available extensions for a profile and installed extensions for an existing sandbox.
create_database	Create one isolated sandbox database and role, optionally installing requested extensions.
clone_database	Clone an existing source database into a new sandbox with pg_dump/pg_restore, optionally installing target extensions and skipping source-only extension entries.
delete_database	Delete a metadata-owned sandbox database and role.
get_connection_string	Return redacted direct/container connection variants by default, or raw credentials when explicitly requested.
run_sql	Run SQL against a sandbox with bounded result rows.
describe_schema	Return relation, column, constraint, index, view, materialized view, foreign table, and extension metadata.
schema_digest	Return a compact checksummed schema summary.
schema_diff	Compare a previous digest with the current schema.
explain_query	Return EXPLAIN (FORMAT JSON) for one safe plannable statement.
create_schema_snapshot	Store a named local schema checkpoint.
list_schema_snapshots	List named schema checkpoints for a sandbox.
diff_schema_snapshot	Compare a stored snapshot with the current schema.
delete_schema_snapshot	Delete a local schema snapshot artifact.
prepare_for_repo	Write secret-free repo workflow metadata to .pgsandbox/project.json.
run_repo_command	Run an explicit repo command with sandbox DB env vars.
validate_schema_change	Capture before/after schema digests around a repo command.
seed_database	Run an explicit seed command against a sandbox.
create_template_from_sandbox	Export a sandbox to a reusable local template artifact.
create_sandbox_from_template	Restore a template into a new tracked sandbox.
list_templates	List local template artifacts for a profile.
delete_template	Delete a template dump and metadata.
list_databases	List active metadata-owned sandboxes.
cleanup_expired	Delete expired metadata-owned sandboxes, or dry-run the selection.

See docs/mcp-tools.md for full tool inputs and outputs.

Extension Discovery And Installation

Use list_extensions before creating or cloning when an app depends on Postgres extensions:

{ "postgresVersion": "18" }

For an existing sandbox, pass databaseId or databaseName to also return installed extension names and versions:

{ "databaseName": "pgsandbox_example_abc123" }

The CLI exposes the same discovery path and prints JSON:

pgsandbox list-extensions --postgres-version 18
pgsandbox list-extensions --database-name pgsandbox_example_abc123

create_database and clone_database accept an optional extensions list:

{
  "nameHint": "trigram search repro",
  "extensions": ["pg_trgm", "uuid-ossp"]
}

PGSandbox trims names, normalizes them to lowercase, deduplicates them, and allows only letters, numbers, underscores, and hyphens. The installed names are returned as installedExtensions.

Extension availability depends on the selected target profile's Postgres installation. PGSandbox checks pg_available_extensions inside the target sandbox before running CREATE EXTENSION IF NOT EXISTS; unavailable or invalid names return invalid_extensions and the new sandbox is rolled back. For clone_database, requested extensions are installed in the empty target sandbox before pg_restore runs.

For clone sources that include observability or environment-specific extensions that the sandbox role should not create, clone_database skips pg_stat_statements source extension entries by default. Pass excludeSourceExtensions to skip additional source extensions while still restoring the rest of the schema:

{
  "sourceDatabaseUrl": "postgres://...",
  "schemaOnly": true,
  "excludeSourceExtensions": ["auto_explain"]
}

Use extensions for target extensions that should exist in the sandbox, and excludeSourceExtensions for source extension entries that should be omitted from the restore archive.

Some extensions require server-level setup such as installed extension packages or shared_preload_libraries. Those failures return extension_setup_required; use profile setup docs or a recipe for that profile instead of adding a one-off MCP tool for the extension.

SQL Execution

run_sql resolves the selected sandbox, obtains its sandbox role connection string, and connects as that role. It does not execute user SQL through the admin connection.

Result behavior:

• default rowLimit: 100
• rowLimit: 0: valid zero-row preview
• hard row limit cap: 1000
• negative rowLimit values return invalid_row_limit
• returns returnedRowCount
• returns affectedRowCount for DML/DDL command tags when available
• reports totalRowCountKnown
• reports truncated
• returns ordered resultSets for multi-statement SQL with 1-based statementIndex values. Top-level rows and row metadata summarize the last row-returning statement, or the last statement when no statement returned rows. The row limit applies independently to each row-returning result set.
• preserves int8 and numeric values as JSON strings
• serializes json/jsonb as nested JSON
• serializes common Postgres arrays as JSON arrays
• returns unsupported non-null types as an object with the original type and a cast-to-text hint
• keeps SQL NULL as JSON null

With readonly: true, PGSandbox runs SQL in a read-only transaction, rejects transaction-control escape hatches, and rolls the transaction back after execution. Mutating statements are returned as structured readonly_violation errors; harmless settings that Postgres permits inside the transaction, such as SET search_path, may still run.

Repo Workflow Tools

Repo workflow tools are intentionally conservative:

• They require an explicit repoPath.
• They execute argv arrays directly without an implicit shell.
• They reject shell wrappers and indirect launchers such as bash -lc, sh -c, env, sudo, and nsenter.
• They inject sandbox credentials into the child process environment.
• They return bounded stdout/stderr with truncation flags.
• They redact the injected database URL and password from captured output.
• They do not permanently rewrite application configuration.

Injected database environment variables include:

DATABASE_URL
PGSANDBOX_DATABASE_URL
PGHOST
PGPORT
PGDATABASE
PGUSER
PGPASSWORD

When databaseId is present, run_repo_command resolves the owning profile/Postgres version from sandbox metadata. Repo-inferred versions are not allowed to override that id; an explicitly supplied conflicting profile/version still fails normally.

Use stdin with a direct interpreter to run a multi-line smoke without writing a temporary file into the repo:

{
  "repoPath": "/absolute/path/to/repo",
  "databaseId": "sandbox-id",
  "command": ["python", "-"],
  "stdin": "print('run app smoke here')\n"
}

stdin accepts up to 65,536 UTF-8 bytes and rejects NUL characters. The normal no-shell command validation remains in force.

Dockerized apps and repos with nonstandard settings can select the connection variant and validated aliases directly:

{
  "repoPath": "/absolute/path/to/repo",
  "databaseId": "sandbox-id",
  "command": ["docker", "compose", "run", "--rm", "web", "python", "manage.py", "check"],
  "connectionMode": "localContainer",
  "databaseUrlEnvNames": ["DATABASE_URI"]
}

Noisy commands can request stripAnsi, stdoutLimit, stderrLimit, tailLines, and suppressDockerLifecycle. Limits remain bounded to 8,000 bytes per stream. run_repo_command reports changedObjects: null with changedObjectsUnsupportedReason because writes made by a child process cannot be observed reliably; use run_sql, schema_digest, or validate_schema_change when side effects must be measured.

Good command examples:

["npm", "run", "migrate"]

["psql", "-v", "ON_ERROR_STOP=1", "-f", "migrations/schema.sql"]

["./scripts/seed.sh"]

prepare_for_repo writes .pgsandbox/project.json without secrets. It can store migrationCommand, seedCommand, databaseUrlEnv, postgresVersion, and preparedAt. It can infer a Postgres major version from Compose files or a devcontainer image such as postgres:16, postgis/postgis:16-3.4, or timescale/timescaledb:pg16.

Schema Snapshots And Templates

Schema snapshots are JSON metadata artifacts under:

~/.pgsandbox/schema-snapshots/<profile>/<database-id>/<snapshot-name>.json

They store object counts, fingerprints, profile, sandbox id, owner/purpose, labels, Postgres version, digest version, notes, and creation time. They are manual checkpoints, not automatically refreshed truth.

Templates are dump plus metadata artifacts under:

~/.pgsandbox/templates/<profile>/<template-name>.dump
~/.pgsandbox/templates/<profile>/<template-name>.json

Templates can only be created from PGSandbox-owned sandboxes and restored into new PGSandbox-owned sandboxes. They are useful for local seeded-state loops, regression fixtures, and repeatable agent QA. They are not copy-on-write forks or a production-data import workflow.

Clone Workflow

clone_database uses a portable dump/restore path:

1. Preflight source and target Postgres major versions, and capture a best-effort source size estimate.
2. Create an empty tracked target sandbox.
3. Install any requested target extensions using the sandbox role.
4. Run pg_dump against the source database.
5. Skip source extension archive entries from pg_stat_statements and any requested excludeSourceExtensions.
6. Run pg_restore into the target sandbox using the sandbox role.
7. Delete the target sandbox if restore fails or times out.

Newer-to-older clone paths fail before target creation with restore_incompatible. The dump/restore phase defaults to a 240 second timeout so PGSandbox can return command_timeout with the created sandbox id, source size estimate when available, and cleanup status before common MCP clients exhaust their own call budget. Cloning and template tools require pg_dump and pg_restore; ordinary create/query/delete flows do not.

Telemetry

Telemetry is enabled by default and sends anonymous, personless usage events to PostHog. It records command/tool names, version, OS/architecture, success, elapsed time, and small booleans/counts.

Telemetry must not include:

• Postgres URLs
• connection strings
• database names or ids
• SQL text
• owner values
• label keys or values
• full local paths
• raw error messages

Telemetry never blocks CLI or MCP tool results. See Environment Variables for opt-out settings.

Safety Boundaries

PGSandbox is designed as local/private infrastructure:

• It installs PostgreSQL packages only during explicit setup, ensure-postgres, or local start --install-missing runs when a supported package manager is available.
• It does not require Docker.
• It does not stop Docker containers.
• It does not bind localhost:5432 by default.
• It does not silently configure external admin URLs.
• It does not expose a public network admin surface.
• It does not delete databases missing from pgsandbox_databases.
• It does not log full connection strings in diagnostics.
• It does not return raw sandbox credentials unless a caller explicitly asks for them.

Hosted database platform work is a future product direction, but it needs a deliberate auth, tenancy, quota, billing, and security design before a public network admin surface is added.

Website

The site/ directory is an Astro site. It contains docs pages, changelog rendering, blog content, sitemap/robots routes, and a CapRover deployment workflow. It is separate from the MCP runtime and does not run the MCP server.

Environment Variables

Configuration Precedence

PGSandbox loads runtime configuration in this order:

1. If PGSANDBOX_CONFIG is set, load the JSON config file it points to.
2. Else if PGSANDBOX_ADMIN_DATABASE_URL is set, create a single explicit profile from environment variables.
3. Else start or reuse the managed local profile under PGSANDBOX_HOME or ~/.pgsandbox.

Telemetry opt-out environment variables are applied after config loading.

Core Runtime Variables

Variable	Required	Description	Default
PGSANDBOX_CONFIG	No	Path to JSON multi-profile config. Takes precedence over single-profile env setup.	unset
PGSANDBOX_ADMIN_DATABASE_URL	No	Explicit Postgres admin URL for single-profile mode. Use only when intentionally bypassing managed local.	unset
PGSANDBOX_DEFAULT_PROFILE	No	Name for the single env profile. With managed local it must remain local.	default with admin URL, local without
PGSANDBOX_HOME	No	Local state root for managed Postgres, templates, and snapshots.	~/.pgsandbox
PGSANDBOX_DATABASE_PREFIX	No	Prefix for generated database and role names.	pgsandbox
PGSANDBOX_DEFAULT_TTL_MINUTES	No	Default sandbox TTL for the env profile. Must be positive.	240
PGSANDBOX_MAX_TTL_MINUTES	No	Max allowed TTL for the env profile. Must be positive and >= default TTL.	1440
PGSANDBOX_MAX_ACTIVE_DATABASES_PER_OWNER	No	Optional per-owner active sandbox quota for the env profile.	unlimited
PGSANDBOX_POSTGRES_VERSION	No	Default managed local Postgres major version to select.	discovered/default

Local Postgres Binary Discovery

Variable	Description
PGSANDBOX_POSTGRES_BIN_DIR	Directory containing initdb, pg_ctl, and postgres.
PGSANDBOX_POSTGRES_<MAJOR>_BIN_DIR	Version-specific binary directory, such as PGSANDBOX_POSTGRES_18_BIN_DIR.

Discovery order favors explicit version-specific settings, then other configured bin dirs, common package-manager locations, local PATH entries, and finally direct PATH command resolution. Common-path version discovery includes installed Postgres 18, 17, 16, 15, 14, and 13 binaries.

External Admin URL Policy

By default, explicit profile admin URLs must be local:

• localhost
• 127.0.0.1
• ::1
• URLs without a host, such as Unix socket style libpq URLs

Remote/private hosts require opt-in.

Variable	Description	Example
PGSANDBOX_ALLOW_EXTERNAL_ADMIN_URL	Allows a non-local admin URL in single-profile env mode.	true
PGSANDBOX_ALLOWED_ADMIN_HOSTS	Comma-separated allowlist for admin URL hosts.	db.internal.example,postgres.internal

Telemetry Variables

Variable	Effect
PGSANDBOX_TELEMETRY=false	Disable telemetry.
PGSANDBOX_NO_TELEMETRY=1	Disable telemetry.
PGSANDBOX_DISABLE_TELEMETRY=1	Disable telemetry.
DO_NOT_TRACK=1	Disable telemetry.

JSON config can also disable telemetry:

{
  "defaultProfile": "external-pg17",
  "profiles": [
    {
      "name": "external-pg17",
      "adminUrl": "postgres://postgres:postgres@localhost:6543/postgres"
    }
  ],
  "telemetry": {
    "enabled": false
  }
}

Install Script Variables

These variables are consumed by scripts/install.sh:

Variable	Description	Default
PGSANDBOX_REPO	GitHub repo to download releases from.	LVTD-LLC/pgsandbox
PGSANDBOX_GITHUB_BASE_URL	GitHub web base URL.	https://github.com
PGSANDBOX_GITHUB_API_URL	GitHub API base URL.	https://api.github.com
PGSANDBOX_INSTALL_DIR	Directory for the installed binary.	~/.local/bin or /usr/local/bin
PGSANDBOX_VERSION	Release version to install.	latest release
PGSANDBOX_TARGET	Release target triple.	detected OS/arch/libc
PGSANDBOX_SKIP_CHECKSUM	Skip checksum verification when set to 1.	0

Pin the current manifest version explicitly:

curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh \
  | PGSANDBOX_VERSION=0.4.9 sh

Site Variables

The Astro site has no required build-time environment variables. Production deployment uses GitHub Actions secrets:

Secret	Purpose
CAPROVER_PGSANDBOX_SITE_URL	CapRover instance URL
CAPROVER_PGSANDBOX_SITE_APP	CapRover app name
CAPROVER_PGSANDBOX_SITE_TOKEN	CapRover app token

Example .env

The root .env.example documents a simple local/default setup:

# By default, PGSandbox uses a managed local cluster under ~/.pgsandbox.
# Set this only when intentionally using an external Postgres admin profile.
# PGSANDBOX_ADMIN_DATABASE_URL=postgres://postgres:postgres@localhost:6543/postgres
# PGSANDBOX_HOME=/path/to/pgsandbox-home
PGSANDBOX_DATABASE_PREFIX=pgsandbox
PGSANDBOX_DEFAULT_TTL_MINUTES=240
PGSANDBOX_MAX_TTL_MINUTES=1440

# Optional alternative to single-profile env configuration.
# PGSANDBOX_CONFIG=./pgsandbox.config.json

# Optional telemetry opt-out.
# PGSANDBOX_TELEMETRY=false

Multi-Profile JSON Config

Use PGSANDBOX_CONFIG for multiple external profiles or version-specific profiles:

{
  "defaultProfile": "external-pg17",
  "profiles": [
    {
      "name": "external-pg17",
      "adminUrl": "postgres://postgres:postgres@localhost:6543/postgres",
      "postgresVersion": "17",
      "databasePrefix": "pgsandbox",
      "defaultTtlMinutes": 240,
      "maxTtlMinutes": 1440,
      "maxActiveDatabasesPerOwner": 3
    },
    {
      "name": "external-pg16",
      "adminUrl": "postgres://postgres:postgres@localhost:6544/postgres",
      "postgresVersion": "16"
    }
  ],
  "telemetry": {
    "enabled": false
  }
}

Use it:

export PGSANDBOX_CONFIG="$PWD/pgsandbox.config.json"
pgsandbox doctor

For a private remote host, opt in explicitly:

{
  "defaultProfile": "private-dev",
  "profiles": [
    {
      "name": "private-dev",
      "adminUrl": "postgres://postgres:[email protected]/postgres?sslmode=require",
      "postgresVersion": "17",
      "allowedAdminHosts": ["db.internal.example"]
    }
  ]
}

Available Scripts

Run root scripts from the repository root.

Command	Description
npm run check	Run cargo check.
npm test	Run cargo test.
npm run build	Run cargo build --release.
npm run typecheck	Alias for cargo check.
npm run start	Run cargo run --.
npm run package:homebrew	Build release binary and create dist/pgsandbox-<version>.tar.gz for the Homebrew formula flow.
npm run package:release	Build target-specific release archive and checksum file in dist/.
npm run uninstall:pgsandbox -- --dry-run	Preview a local uninstall/reset of PGSandbox binaries, MCP client entries, and managed local state.
npm run site:build	Run the Astro site build through the root package.
npm run site:check-changelog	Verify site changelog fallback content.
npm run site:check-blog-content	Validate blog content conventions.
npm run site:check-blog-tables	Validate generated/rendered blog tables after site build.

Direct Cargo commands:

Command	Description
cargo fmt -- --check	Check Rust formatting.
cargo clippy --all-targets -- -D warnings	Run Clippy with warnings denied.
cargo check	Typecheck the Rust package.
cargo test	Run unit tests and skipped-by-default integration tests.
cargo build	Build debug binary.
cargo build --release	Build optimized release binary.
cargo run -- --help	Print CLI help.
cargo run -- --version	Print package version.

CLI commands after installation:

Command	Description
pgsandbox	Start stdio MCP server.
pgsandbox mcp	Start stdio MCP server explicitly.
pgsandbox setup --client codex	Prepare managed local Postgres and write user-scoped Codex MCP config.
pgsandbox setup --client cursor --scope project	Prepare managed local Postgres and write project .cursor/mcp.json.
pgsandbox setup --client vscode --scope project	Prepare managed local Postgres and write project .vscode/mcp.json.
pgsandbox setup --client claude-desktop	Prepare managed local Postgres and write Claude Desktop user config.
pgsandbox setup --client all	Prepare managed local Postgres and write supported user-scoped configs.
pgsandbox setup --client codex --dry-run	Print intended config without writing or preparing local Postgres.
pgsandbox doctor	Check config and Postgres connectivity.
pgsandbox doctor --postgres-version 18	Check a requested managed local major version.
pgsandbox create-database --name-hint task --ttl-minutes 30	Create a tracked sandbox through the CLI.
pgsandbox run-sql --database-id <id> --sql "select 1" --readonly	Run bounded SQL through the sandbox role.
pgsandbox delete-database --database-id <id>	Delete a metadata-owned sandbox.
pgsandbox tool <mcp_tool_name> --input '{...}'	Run any MCP tool contract from the shell with camelCase JSON input.
pgsandbox ensure-postgres --postgres-version 13	Install missing local Postgres 13 binaries with a supported package manager when available, then start local-pg13.
pgsandbox upgrade	Upgrade Homebrew/install-script installs, rerun setup for all clients, and run doctor.
pgsandbox upgrade --setup codex	Upgrade and only refresh the Codex config.
pgsandbox uninstall --dry-run	Preview removal of PGSandbox binaries, MCP client entries, and managed local state.
pgsandbox local init	Initialize managed local Postgres without starting it.
pgsandbox local start	Initialize if needed and start managed local Postgres.
pgsandbox local status	Show managed local status.
pgsandbox local stop	Stop managed local Postgres.
pgsandbox local start --postgres-version 18 --install-missing	Start versioned local profile local-pg18, installing binaries with a supported package manager when available.
pgsandbox smoke-test	Create, query, and delete a sandbox with concise result lines.
pgsandbox smoke-test --verbose	Include full structured SQL result dictionaries in the smoke output.
pgsandbox smoke-test --postgres-version 18	Smoke test a specific local major version.

Site commands:

Command	Description
npm --prefix site run dev	Start Astro dev server.
npm --prefix site run check	Run astro check.
npm --prefix site run build	Run astro check && astro build.
npm --prefix site run preview	Preview built Astro output.

Testing

Standard Test Suite

Run the expected local checks:

npm run check
npm test
npm run build

For full CI parity:

cargo fmt -- --check
cargo clippy --all-targets -- -D warnings
npm run check
npm test
npm run build
npm run site:check-changelog
npm run site:check-blog-content
npm --prefix site ci --include=dev
npm run site:build
npm run site:check-blog-tables

Test Layout

Rust unit tests live beside the code they cover:

rust-src/config.rs     Config loading, env handling, profile validation
rust-src/local.rs      Managed local runtime paths, ports, binary discovery
rust-src/mcp.rs        Envelope normalization and tool error shaping
rust-src/names.rs      Identifier generation and SQL quoting
rust-src/postgres.rs   SQL execution, schema digest/diff, workflow behavior
rust-src/setup.rs      MCP client config writing

Integration-style tests live under tests/:

tests/dogfood_reliability.rs
tests/extensions.rs
tests/run_sql_serialization.rs

These tests are compiled by cargo test, but live database scenarios are skipped unless the relevant environment variable is set.

Live Postgres Tests

Run the dogfood reliability suite against real disposable sandboxes:

PGSANDBOX_DOGFOOD_E2E=1 cargo test --test dogfood_reliability -- --nocapture

Run the PG18 schema snapshot regression when Postgres 18 binaries are installed:

PGSANDBOX_DOGFOOD_PG18_E2E=1 \
  cargo test --test dogfood_reliability \
  pg18_schema_snapshot_minimal_schema_returns_without_timeout_when_enabled \
  -- --nocapture

Run the SQL serialization E2E test:

PGSANDBOX_RUN_SQL_SERIALIZATION_E2E=1 \
  cargo test --test run_sql_serialization -- --nocapture

Run the readonly transaction contract E2E test:

PGSANDBOX_RUN_SQL_READONLY_E2E=1 \
  cargo test --test run_sql_serialization \
  run_sql_readonly_contract_matches_postgres_transaction_when_enabled \
  -- --nocapture

Run the extension discovery and installation E2E test. It uses pg_trgm by default; override the extension name when a target profile exposes a different extension package:

PGSANDBOX_EXTENSION_E2E=1 \
  cargo test --test extensions -- --nocapture

PGSANDBOX_EXTENSION_E2E=1 PGSANDBOX_EXTENSION_E2E_NAME=citext \
  cargo test --test extensions -- --nocapture

Each live test creates a sandbox and attempts cleanup at the end. If cleanup fails, the test prints the failure so you can remove the sandbox with the MCP tool or with pgsandbox smoke-test/manual diagnostics. Live tests use the normal PGSandbox config path: the managed local runtime when no explicit config is set, or the configured PGSANDBOX_ADMIN_DATABASE_URL/PGSANDBOX_CONFIG profile when present.

Manual Runtime Checks

Useful local runtime checks:

pgsandbox local status
pgsandbox doctor
pgsandbox smoke-test

For a versioned local runtime:

pgsandbox local status --postgres-version 18
pgsandbox doctor --postgres-version 18
pgsandbox smoke-test --postgres-version 18

Packaging Checks

npm run package:homebrew
npm run package:release

Generated release archives and checksum files are build artifacts. Do not edit them by hand.

Deployment

PGSandbox has two separate deployable artifacts:

1. The native pgsandbox CLI/MCP binary.
2. The static Astro website under site/.

The MCP server is intended to run locally or on a private trusted machine as a stdio process launched by an MCP client. It is not a public web service in this repository.

Install Released Binary With Homebrew

This is the same recommended packaged path shown in Getting Started.

Recommended user flow:

brew install LVTD-LLC/tap/pgsandbox
pgsandbox setup --client codex
pgsandbox doctor
pgsandbox smoke-test

The formula lives in LVTD-LLC/homebrew-tap, which Homebrew addresses as LVTD-LLC/tap.

Restart the MCP client after setup. In Codex, run /mcp after restart to verify that the pgsandbox server is available.

Install Released Binary With The Script

curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh | sh
pgsandbox setup --client codex
pgsandbox doctor

The script downloads a platform-specific release archive, verifies checksums when the release includes pgsandbox-<version>-checksums.txt, and installs to ~/.local/bin by default.

Install the current manifest version explicitly:

curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh \
  | PGSANDBOX_VERSION=0.4.9 sh

Install to a custom directory:

curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh \
  | PGSANDBOX_INSTALL_DIR=/usr/local/bin sh
pgsandbox setup --client codex --command /usr/local/bin/pgsandbox

Install From Source For Development

Use source installs when contributing, testing a local checkout, or validating an unreleased tag. Normal users should prefer Homebrew or the GitHub release installer.

From a checkout:

cargo install --path . --force
pgsandbox setup --client codex
pgsandbox doctor

From GitHub:

cargo install --git https://github.com/LVTD-LLC/pgsandbox --tag v0.4.9 --force
pgsandbox setup --client codex
pgsandbox doctor

Update An Existing Installation

For Homebrew and GitHub install-script installs, the shortest path is:

pgsandbox upgrade

upgrade updates the installed binary, reruns setup --client all, runs doctor, and reminds you to restart MCP clients. It supports the same release targets as the GitHub installer: macOS and Linux on x86_64 or aarch64. Homebrew installs are upgraded through Homebrew. GitHub install-script installs rerun the hosted installer into the current binary directory. --version is only supported for GitHub install-script installs because Homebrew upgrades use the tap formula version.

To update only one client config, skip post-upgrade steps, or pin a GitHub installer release:

pgsandbox upgrade --setup codex
pgsandbox upgrade --no-setup
pgsandbox upgrade --no-doctor
pgsandbox upgrade --version 0.4.9

Homebrew:

brew update
brew upgrade LVTD-LLC/tap/pgsandbox
pgsandbox --version
pgsandbox setup --client codex
pgsandbox doctor

Install script:

curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh | sh
pgsandbox --version
pgsandbox setup --client codex
pgsandbox doctor

Source install for development:

cargo install --path . --force
pgsandbox setup --client codex
pgsandbox doctor

Rerun setup when the binary path, explicit admin URL, selected client, or scope changes. Restart the MCP client after updating.

MCP Client Rollout

pgsandbox setup writes config while preserving unrelated servers.

Codex user config:

pgsandbox setup --client codex

Cursor project config:

pgsandbox setup --client cursor --scope project

VS Code project config:

pgsandbox setup --client vscode --scope project

Claude Desktop user config:

pgsandbox setup --client claude-desktop

A generated Codex config entry looks like:

[mcp_servers.pgsandbox]
command = "pgsandbox"
args = ["mcp"]

If you intentionally configure an external Postgres admin URL, setup writes it into the client config environment so desktop clients do not depend on shell startup files:

pgsandbox setup \
  --client codex \
  --admin-url "$PGSANDBOX_ADMIN_DATABASE_URL"

Do not use --admin-url for the managed local default.

Release Packaging For Maintainers

1. Update versions in Cargo.toml and package.json.
2. Run the full test suite.
3. Build release archives.
4. Publish a GitHub release with the generated artifacts.
5. Let the Homebrew tap workflow open a PR.
6. Merge the tap PR before telling Homebrew users to upgrade.

Commands:

cargo test
npm run package:homebrew
npm run package:release

npm run package:homebrew creates:

dist/pgsandbox-<version>.tar.gz

npm run package:release creates:

dist/pgsandbox-<version>-<target>.tar.gz
dist/pgsandbox-<version>-checksums.txt

When a GitHub release is published, .github/workflows/update-homebrew-tap.yml downloads or builds the Homebrew archive, computes SHA-256, checks out LVTD-LLC/homebrew-tap, updates Formula/pgsandbox.rb, and opens or updates a PR.

The workflow requires the HOMEBREW_TAP_PAT repository secret with write access to the tap repository.

Static Site Deployment

The site deploy workflow runs on pushes to main that affect site files, CHANGELOG.md, the changelog checker, or the deploy workflow itself. It can also be run manually with workflow_dispatch.

Workflow:

1. Install site dependencies with Node 22.
2. Run the changelog fallback check.
3. Build the Astro site.
4. Package site/ without node_modules.
5. Deploy the archive to CapRover.

Local site build:

npm --prefix site ci --include=dev
npm --prefix site run build

Production deployment requires these GitHub secrets:

CAPROVER_PGSANDBOX_SITE_URL
CAPROVER_PGSANDBOX_SITE_APP
CAPROVER_PGSANDBOX_SITE_TOKEN

Docker Demo Postgres

docker-compose.example.yml starts a normal Postgres service on 5432 for users who want to test explicit external profile mode:

docker compose -f docker-compose.example.yml up -d
export PGSANDBOX_ADMIN_DATABASE_URL="postgres://postgres:postgres@localhost:5432/postgres"
pgsandbox doctor
pgsandbox smoke-test

This is optional. Runtime code must not require Docker.

Troubleshooting

could not find local Postgres binaries

Rerun setup first. It checks the local runtime, installs PostgreSQL through a supported package manager when available, starts the managed local cluster, and writes MCP config:

pgsandbox setup --client codex

If setup cannot install PostgreSQL automatically, install the server binaries with your system package manager and point PGSandbox at their bin directory:

export PGSANDBOX_POSTGRES_BIN_DIR="/path/to/postgres/bin"
pgsandbox setup --client codex
pgsandbox doctor

For a requested major version, let PGSandbox install the package when your system package manager still provides it:

pgsandbox ensure-postgres --postgres-version 14
pgsandbox setup --client codex --postgres-version 14

For an older major such as Postgres 13:

pgsandbox ensure-postgres --postgres-version 13
pgsandbox setup --client codex --postgres-version 13

From MCP, agents can call ensure_postgres before creating a sandbox:

{ "postgresVersion": "13", "installMissing": true }

Automatic PostgreSQL package installs use Homebrew on macOS; apt-get, dnf, yum, zypper, or pacman on Linux; and WinGet or Chocolatey on Windows. Versioned packages still depend on what that package manager currently publishes. If an automatic install fails, the MCP error includes bounded installer output and the next action: install manually and set the matching PGSANDBOX_POSTGRES_<MAJOR>_BIN_DIR, or choose a newer clone target when a matching older target is unavailable.

If your package manager does not provide that major version, point PGSandbox at existing server binaries:

export PGSANDBOX_POSTGRES_13_BIN_DIR="/opt/homebrew/opt/postgresql@13/bin"
pgsandbox setup --client codex --postgres-version 13

Requested Postgres Version Is Missing

List discovered local versions:

pgsandbox doctor

From MCP, call list_profiles with:

{ "includeDiscoveredLocal": true }

Then install the missing version, set PGSANDBOX_POSTGRES_<MAJOR>_BIN_DIR, or choose an available version.

Existing Postgres On Port 5432

This is expected and should not be a problem. The managed local runtime starts at 65432 and scans upward.

pgsandbox local start
pgsandbox local status

If you intentionally want to use the service on 5432, set an explicit admin URL or JSON profile.

Stale MCP Config Uses An Old Admin URL

If doctor reports that it is using an admin URL from an MCP config but you want managed local, rerun setup without --admin-url:

pgsandbox setup --client codex

Restart the MCP client and rerun:

pgsandbox doctor

pgsandbox --version Shows A Node.js Stack Trace

An old npm/global install may be earlier in PATH.

which -a pgsandbox
/opt/homebrew/bin/pgsandbox --version

Remove the stale install or point the MCP client at the native binary:

npm uninstall -g pgsandbox
hash -r 2>/dev/null || rehash
pgsandbox setup --client codex --command /opt/homebrew/bin/pgsandbox

clone_database Or Template Restore Fails

Install pg_dump and pg_restore from PostgreSQL client tools. Ordinary create/query/delete flows do not require them.

pg_dump --version
pg_restore --version

Also check source and target Postgres major versions. Newer-to-older clone paths are rejected before creating the target sandbox.

Large clone operations time out inside PGSandbox before common MCP client call budgets expire. The structured timeout includes the created sandbox id/name, best-effort source size estimate, and whether cleanup already deleted the sandbox. Retry with a larger timeoutSeconds, schemaOnly: true, or a smaller source/template path.

If clone restore fails on a source-only extension such as an observability extension, retry with excludeSourceExtensions. pg_stat_statements is skipped by default because sandbox roles commonly cannot create it.

run_repo_command Rejects A Command

Repo commands are executed directly and cannot invoke shells or launchers. Change this:

["bash", "-lc", "npm run migrate && npm run seed"]

To direct commands or an executable repo script:

["npm", "run", "migrate"]

["./scripts/seed.sh"]

For a one-off multi-line Python script, keep the direct-command boundary and send the script over stdin:

{
  "command": ["python", "-"],
  "stdin": "print('smoke')\n"
}

Repo Workflow Has No Migration Command

Pass a command directly:

{
  "repoPath": "/absolute/path/to/repo",
  "databaseId": "sandbox-id",
  "command": ["npm", "run", "migrate"]
}

Or store a secret-free default:

{
  "repoPath": "/absolute/path/to/repo",
  "migrationCommand": ["npm", "run", "migrate"]
}

prepare_for_repo writes .pgsandbox/project.json.

SQL Results Are Truncated

run_sql defaults to 100 rows, accepts rowLimit: 0 for a zero-row preview, rejects negative rowLimit values with invalid_row_limit, and caps rowLimit at 1000.

{
  "databaseId": "sandbox-id",
  "sql": "select * from large_table order by id",
  "readonly": true,
  "rowLimit": 1000
}

Use SQL filters, aggregates, or pagination for larger inspection tasks.

Readonly SQL Fails

With readonly: true, PGSandbox runs SQL in a read-only transaction and rolls it back after execution. Mutating statements such as INSERT or CREATE TEMP TABLE fail with readonly_violation; harmless settings that Postgres permits inside the transaction, such as SET search_path, may still run. If mutation is intentional, omit readonly or set it to false.

Sandbox Was Not Found

Unscoped databaseId lookup searches configured profiles and running managed local profiles. If the sandbox cannot be resolved:

1. Call list_databases with includeAllVersions: true.
2. Retry the operation with the returned profile or postgresVersion.
3. Check whether the sandbox expired or was deleted.

Expired Sandboxes Remain

Cleanup is explicit unless you run it from a scheduler or call the MCP tool. list_databases excludes expired sandboxes by default. Pass includeExpired: true to inspect them; every returned database includes expired, ttlStatus, and signed expiresInSeconds fields.

Profile-scoped dry run:

{ "dryRun": true }

All running versions:

{ "includeAllVersions": true, "dryRun": true }

Only sandboxes matching the current owner and task labels:

{
  "includeAllVersions": true,
  "dryRun": true,
  "owner": "codex-run-1",
  "labels": { "task": "PGS-043" }
}

Then run without dryRun to delete selected expired sandboxes.

Local State Permissions

If PGSandbox cannot write under ~/.pgsandbox, fix ownership or use another state root:

export PGSANDBOX_HOME="$HOME/.local/state/pgsandbox"
pgsandbox local start

Old Unix Socket Path After Upgrade

TCP connections continue to work. If a local Unix-socket consumer needs the new short socket path under /tmp/pgsandbox-sockets/, restart the local runtime:

pgsandbox local stop
pgsandbox local start

External Admin URL Is Refused

Non-local admin URLs require explicit opt-in:

export PGSANDBOX_ALLOW_EXTERNAL_ADMIN_URL=true

Or use a host allowlist:

export PGSANDBOX_ALLOWED_ADMIN_HOSTS="db.internal.example"

For JSON config, use allowExternalAdminUrl or allowedAdminHosts.

Site Build Fails

Install site dependencies and run Astro checks directly:

npm --prefix site ci --include=dev
npm --prefix site run check
npm --prefix site run build

If blog table checks fail, build first and then run:

npm run site:check-blog-tables

Contributing

Before editing, check the worktree:

git status --short

Keep changes scoped to the relevant module:

• Config loading changes belong in rust-src/config.rs with tests there.
• Identifier generation and quoting changes belong in rust-src/names.rs.
• SQL execution, cleanup, TTL, schema, template, and response-shape changes belong in rust-src/postgres.rs.
• MCP tool surface changes belong in rust-src/mcp.rs and should be reflected in docs/mcp-tools.md.
• MCP client config writing changes belong in rust-src/setup.rs.
• Managed local runtime changes belong in rust-src/local.rs.
• User-facing setup, config, command, and packaging changes should update this README and any relevant files in docs/.

Run at least:

npm run check
npm test
npm run build

For behavior that needs live Postgres, add the smallest practical integration path and document whether it uses the managed local runtime or an explicit PGSANDBOX_ADMIN_DATABASE_URL.

License

MIT. See LICENSE.