PostgreSQL

This app installs the official PostgreSQL 17.5.0 toolchain on the host and fronts it as typed methods. The bundle is a relocatable build of PostgreSQL 17.5.0 (from conda-forge, compiled from the upstream PostgreSQL sources) carrying the complete client + server suite: psql, initdb, pg_ctl, postgres, createdb, dropdb, pg_isready, pg_dump, pg_restore, pg_dumpall, vacuumdb, pg_basebackup. Every binary is sha-pinned and staged at install; a tiny pg dispatcher in the bundle routes each method to the right tool.

Two ways to use it

A) Talk to an existing PostgreSQL server. Point the query methods at any reachable server with a libpq uri (or PG* env) — no local server needed: - postgres.query / postgres.query_csv — run SQL, get an aligned table or CSV. - postgres.command — backslash introspection (\dt, \d table, \du, \l, …). - postgres.list — list databases. postgres.version / postgres.psql_help — client version and full --help.

B) Run a database locally on this machine. The app can provision and manage its own cluster — useful for an agent that needs a throwaway or embedded Postgres:

1. Configure (one-time): postgres.initdb { "datadir": "/path/to/pgdata" } — creates the cluster (superuser postgres, local trust auth). 2. Start: postgres.start { "datadir": "/path/to/pgdata", "port": "5599" } — boots the server on 127.0.0.1:5599 (+ a Unix socket in the datadir), waits until ready, logs to <datadir>/postgres.log. 3. Create a database: postgres.createdb { "port": "5599", "dbname": "appdb" }. 4. Use it: postgres.query with uri = "host=127.0.0.1 port=5599 user=postgres dbname=appdb". 5. Health / teardown: postgres.ready { "port": "5599" }, postgres.status { "datadir": "..." }, postgres.stop { "datadir": "..." }.

Configuration

PostgreSQL is configuration-rich; the knobs this app exposes:

• datadir — where the cluster lives. Pick a writable absolute path (e.g. $HOME/.pilot/pgdata or a tmp dir). One cluster can hold many databases.
• port — TCP port for the local server (default convention 5599). The server also listens on a Unix socket inside datadir.
• Auth — local clusters are initialized with trust (no password) for convenience; for any networked use, set a password (postgres.exec → psql ... -c "ALTER ROLE postgres PASSWORD '...'") and supply it via the uri or PGPASSWORD.
• Non-root for the server — postgres.initdb/postgres.start run the PostgreSQL server, which refuses to run as the OS root user (a PostgreSQL safety rule). On a normal host the pilot daemon runs as your user, so this just works; only fully-root environments (e.g. some containers) need a non-root user. The query methods against a remote server are unaffected and run anywhere.
• uri — a full libpq connection string, either postgresql://user:secret@host:5432/dbname?sslmode=require or host=... port=... dbname=... user=... sslmode=....
• PG* env — PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE, PGSSLMODE, PGOPTIONS, PGCONNECT_TIMEOUT, PGAPPNAME, PGCLIENTENCODING are passed through to the child, so postgres.exec can connect with no inline credentials.
• Anything else — full postgresql.conf/server flags are reachable via postgres.exec (e.g. pg_ctl ... -o "-c shared_buffers=256MB"), and per-session settings via SQL SET.

Good to know

• Output returns verbatim where it is already clean; on a non-zero exit (SQL error, server down) the reply is {stdout, stderr, exit} so the caller sees everything the tool produced.
• Runs on macOS and Linux (arm64 + amd64); binaries are fetched from the Pilot artifact registry and sha-pinned on install. Free and open source under the PostgreSQL License.
• postgres.help lists every method with its latency class; this is the self-describing discovery contract.

## psql --help ``` psql is the PostgreSQL interactive terminal.

Usage: psql [OPTION]... [DBNAME [USERNAME]]

General options: -c, --command=COMMAND run only single command (SQL or internal) and exit -d, --dbname=DBNAME database name to connect to -f, --file=FILENAME execute commands from file, then exit -l, --list list available databases, then exit -v, --set=, --variable=NAME=VALUE set psql variable NAME to VALUE (e.g., -v ON_ERROR_STOP=1) -V, --version output version information, then exit -X, --no-psqlrc do not read startup file (~/.psqlrc) -1 ("one"), --single-transaction execute as a single transaction (if non-interactive) -?, --help[=options] show this help, then exit --help=commands list backslash commands, then exit --help=variables list special variables, then exit

Input and output options: -a, --echo-all echo all input from script -b, --echo-errors echo failed commands -e, --echo-queries echo commands sent to server -E, --echo-hidden display queries that internal commands generate -L, --log-file=FILENAME send session log to file -n, --no-readline disable enhanced command line editing (readline) -o, --output=FILENAME send query results to file (or |pipe) -q, --quiet run quietly (no messages, only query output) -s, --single-step single-step mode (confirm each query) -S, --single-line single-line mode (end of line terminates SQL command)

Output format options: -A, --no-align unaligned table output mode --csv CSV (Comma-Separated Values) table output mode -F, --field-separator=STRING field separator for unaligned output (default: "|") -H, --html HTML table output mode -P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command) -R, --record-separator=STRING record separator for unaligned output (default: newline) -t, --tuples-only print rows only -T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border) -x, --expanded turn on expanded table output -z, --field-separator-zero set field separator for unaligned output to zero byte -0, --record-separator-zero set record separator for unaligned output to zero byte

Connection options: -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port -U, --username=USERNAME database user name -w, --no-password never prompt for password -W, --password force password prompt (should happen automatically)

For more information, type "\?" (for internal commands) or "\help" (for SQL commands) from within psql, or consult the psql section in the PostgreSQL documentation.

Report bugs to <pgsql-bugs@lists.postgresql.org>. PostgreSQL home page: <https://www.postgresql.org/> ```