> ## Documentation Index
> Fetch the complete documentation index at: https://docs.archil.com/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL

> Run PostgreSQL in a sandbox with its data directory on an Archil disk, and connect to it from your machine with psql.

This guide runs [PostgreSQL](https://www.postgresql.org/) in a [persistent sandbox](/compute/sandboxes/introduction) with its data directory on an Archil disk. Postgres runs as a supervised service, listens on a public hostname, and requires a password for every network connection. You connect from your own machine with `psql`, write some rows, stop the sandbox, and read them back.

Postgres doesn't need to know anything about Archil. It reads and writes files in its data directory as usual, and Archil keeps them durable in object storage, so the database outlives any one sandbox.

## Prerequisites

Export your Archil API key and a region where sandboxes are available (`aws-us-east-1`, `aws-us-west-2`, or `aws-eu-west-1`):

```bash theme={null}
export ARCHIL_API_KEY=key-...
export ARCHIL_REGION=aws-us-east-1
```

Then install the SDK:

<CodeGroup>
  ```bash TypeScript theme={null}
  npm init -y && npm pkg set type=module
  npm install disk tsx
  ```

  ```bash Python theme={null}
  pip install archil
  ```
</CodeGroup>

On your machine you also need `psql` and [`socat`](http://www.dest-unreach.org/socat/), which carries the connection over TLS to the sandbox. Any recent `psql` works.

<CodeGroup>
  ```bash macOS theme={null}
  brew install libpq socat
  export PATH="$(brew --prefix libpq)/bin:$PATH"
  ```

  ```bash Debian/Ubuntu theme={null}
  sudo apt-get install -y postgresql-client socat
  ```
</CodeGroup>

Finally, generate a password for the `postgres` user. The setup script reads it from this variable, and so does `psql` later:

```bash theme={null}
export POSTGRES_PASSWORD=$(openssl rand -hex 24)
```

## Write the setup script

This script runs inside the sandbox. The Archil disk is mounted by root, but Postgres refuses to run as root and requires its data directory to belong to the `postgres` user with mode `0700`, so the script hands the directory over first. On an empty disk it then initializes a cluster with a password for the `postgres` user, creates a self-signed certificate so connections are encrypted all the way to the server, and only accepts network connections that use SSL and a password. Finally, it registers Postgres as a service, which restarts if it crashes and starts again whenever the sandbox boots.

```bash setup.sh theme={null}
#!/bin/bash
set -euo pipefail

PGDATA=/mnt/archil/pgdata
mkdir -p "$PGDATA"
chown postgres:postgres "$PGDATA"
chmod 700 "$PGDATA"

if [ ! -s "$PGDATA/PG_VERSION" ]; then
  echo "$POSTGRES_PASSWORD" > /tmp/pwfile
  chown postgres /tmp/pwfile
  gosu postgres initdb --pgdata="$PGDATA" --username=postgres --pwfile=/tmp/pwfile \
    --auth-local=peer --auth-host=scram-sha-256
  rm /tmp/pwfile

  openssl req -new -x509 -days 3650 -nodes -subj "/CN=postgres" \
    -keyout "$PGDATA/server.key" -out "$PGDATA/server.crt" 2>/dev/null
  chown postgres:postgres "$PGDATA/server.key" "$PGDATA/server.crt"
  chmod 600 "$PGDATA/server.key"

  echo "listen_addresses = '*'" >> "$PGDATA/postgresql.conf"
  echo "ssl = on" >> "$PGDATA/postgresql.conf"
  echo "hostssl all all all scram-sha-256" >> "$PGDATA/pg_hba.conf"
fi

archil-sandbox services create postgres --tcp-port 5432 -- \
  gosu postgres /usr/lib/postgresql/17/bin/postgres -D "$PGDATA"
timeout 60 bash -c 'until gosu postgres pg_isready --quiet; do sleep 1; done'
```

Because the script only initializes an empty data directory, you can run it on a new sandbox that mounts an existing `postgres-data` disk, and it just starts the server on the data that's already there.

## Start the database

The next script creates a disk, creates a sandbox from the official `postgres:17` image with the disk mounted at `/mnt/archil`, uploads `setup.sh`, and runs it with your password. Then it exposes port 5432 publicly and prints the hostname.

<CodeGroup>
  ```typescript create.ts theme={null}
  import { readFile } from "node:fs/promises";
  import * as archil from "disk";

  const { disk } = await archil.createDisk({ name: "postgres-data" });
  const sandbox = await archil.createSandbox({
    name: "postgres",
    baseImage: "postgres:17",
    vcpuCount: 2,
    memSizeMiB: 4096,
    mounts: [{ disk, path: "/mnt/archil" }],
  });

  await sandbox.files.uploadFile(await readFile("setup.sh"), "/root/setup.sh", { mode: 0o755 });
  const setup = await sandbox.exec("/root/setup.sh", {
    env: { POSTGRES_PASSWORD: process.env.POSTGRES_PASSWORD! },
  });
  if (setup.exitCode !== 0) throw new Error(setup.stderr);

  console.log(await sandbox.exposePort(5432));
  ```

  ```python create.py theme={null}
  import os

  import archil

  disk = archil.create_disk(name="postgres-data").disk
  sandbox = archil.create_sandbox(
      name="postgres",
      base_image="postgres:17",
      vcpu_count=2,
      mem_size_mib=4096,
      mounts=[archil.SandboxMountSpec(disk=disk, path="/mnt/archil")],
  )

  sandbox.files.upload_file("setup.sh", "/root/setup.sh", mode=0o755)
  setup = sandbox.exec("/root/setup.sh", env={"POSTGRES_PASSWORD": os.environ["POSTGRES_PASSWORD"]})
  if setup.exit_code != 0:
      raise RuntimeError(setup.stderr)

  print(sandbox.expose_port(5432))
  ```
</CodeGroup>

<CodeGroup>
  ```bash TypeScript theme={null}
  npx tsx create.ts
  ```

  ```bash Python theme={null}
  python create.py
  ```
</CodeGroup>

```text theme={null}
5432-agquud7y7fzstcgaybnhkhoov4.prod-aws-us-west-2-green.archil.app
```

<Warning>
  An exposed port is reachable by anyone who knows the hostname. The setup script requires SSL and a password for every
  network connection; keep the password long and random, as generated above, and never expose a Postgres user that has
  a weak password or none.
</Warning>

## Connect from your machine

Archil serves public ports over TLS on port 443 with a publicly trusted certificate, and forwards the decrypted TCP
stream to the sandbox. `psql` can't open that kind of TLS connection on its own, so start a local `socat` tunnel that
listens on port 15432, wraps each connection in TLS, and verifies Archil's certificate. Use the hostname that
`create` printed:

```bash theme={null}
export PG_HOST=5432-agquud7y7fzstcgaybnhkhoov4.prod-aws-us-west-2-green.archil.app
socat TCP-LISTEN:15432,fork,reuseaddr OPENSSL:$PG_HOST:443,verify=1 &
```

`socat` may print a warning that it doesn't check certificate revocation lists; the tunnel still verifies the
certificate. Now connect through the tunnel with your password. `sslmode=require` makes Postgres encrypt the stream
inside the tunnel, too, so it stays encrypted all the way to the server:

```bash theme={null}
export PGPASSWORD=$POSTGRES_PASSWORD
psql "host=localhost port=15432 user=postgres sslmode=require" <<'SQL'
CREATE TABLE notes (id serial PRIMARY KEY, body text NOT NULL);
INSERT INTO notes (body) VALUES ('hello from my laptop'), ('stored on an Archil disk');
SELECT id, body FROM notes;
SHOW data_directory;
SQL
```

```text theme={null}
CREATE TABLE
INSERT 0 2
 id |           body
----+--------------------------
  1 | hello from my laptop
  2 | stored on an Archil disk
(2 rows)

   data_directory
--------------------
 /mnt/archil/pgdata
(1 row)
```

## Restart it

Stop the sandbox and start it again. Stopping sends `SIGTERM` to Postgres, which shuts down cleanly. Starting boots a
fresh VM, which mounts the disk and starts the `postgres` service; the script waits until Postgres accepts connections.

<CodeGroup>
  ```typescript restart.ts theme={null}
  import * as archil from "disk";

  const sandbox = (await archil.listSandboxes()).find((s) => s.name === "postgres")!;
  await sandbox.stop();
  console.log("sandbox is", sandbox.status);

  await sandbox.start();
  await sandbox.exec("timeout 60 bash -c 'until gosu postgres pg_isready --quiet; do sleep 1; done'");
  console.log("sandbox is", sandbox.status);
  ```

  ```python restart.py theme={null}
  import archil

  sandbox = next(s for s in archil.list_sandboxes() if s.name == "postgres")
  sandbox.stop()
  print("sandbox is", archil.get_sandbox(sandbox.id).status)

  sandbox.start()
  sandbox.exec("timeout 60 bash -c 'until gosu postgres pg_isready --quiet; do sleep 1; done'")
  print("sandbox is", archil.get_sandbox(sandbox.id).status)
  ```
</CodeGroup>

<CodeGroup>
  ```bash TypeScript theme={null}
  npx tsx restart.ts
  ```

  ```bash Python theme={null}
  python restart.py
  ```
</CodeGroup>

```text theme={null}
sandbox is stopped
sandbox is running
```

Query the table again through the same tunnel:

```bash theme={null}
psql "host=localhost port=15432 user=postgres sslmode=require" -c "SELECT id, body FROM notes;"
```

```text theme={null}
 id |           body
----+--------------------------
  1 | hello from my laptop
  2 | stored on an Archil disk
(2 rows)
```

The rows were on the Archil disk the whole time.

<Note>
  You don't have to start a stopped sandbox yourself: a connection to its public port boots it, usually within a
  couple of seconds. Postgres starts listening a moment before it's ready, though, so that first connection can fail
  with `the database system is starting up`. Connect again and it succeeds.
</Note>

## Benchmark it (optional)

The `postgres:17` image includes `pgbench`. This script creates a 10-scale benchmark database (about 160 MB) and runs a 30-second read-write test with 8 clients inside the sandbox:

<CodeGroup>
  ```typescript bench.ts theme={null}
  import * as archil from "disk";

  const sandbox = (await archil.listSandboxes()).find((s) => s.name === "postgres")!;
  const bench = await sandbox.exec(
    "gosu postgres createdb bench && gosu postgres pgbench -i -s 10 -q bench && gosu postgres pgbench -c 8 -j 2 -T 30 bench",
  );
  console.log(bench.stdout);
  ```

  ```python bench.py theme={null}
  import archil

  sandbox = next(s for s in archil.list_sandboxes() if s.name == "postgres")
  bench = sandbox.exec(
      "gosu postgres createdb bench && gosu postgres pgbench -i -s 10 -q bench && gosu postgres pgbench -c 8 -j 2 -T 30 bench",
  )
  print(bench.stdout)
  ```
</CodeGroup>

When the run finishes, look at the `tps` (transactions per second) and `latency average` lines of pgbench's summary. Every commit waits for `fsync` on the Archil disk, so Postgres keeps its usual durability guarantees.

## Clean up

Stop the tunnel, then stop and delete the sandbox and delete the disk:

```bash theme={null}
kill %1
```

<CodeGroup>
  ```typescript cleanup.ts theme={null}
  import * as archil from "disk";

  const sandbox = (await archil.listSandboxes()).find((s) => s.name === "postgres");
  await sandbox?.stop();
  await sandbox?.delete();
  const [disk] = await archil.listDisks({ name: "postgres-data" });
  await disk?.delete();
  ```

  ```python cleanup.py theme={null}
  import archil

  for sandbox in archil.list_sandboxes():
      if sandbox.name == "postgres":
          sandbox.stop()
          sandbox.delete()
  for disk in archil.list_disks(name="postgres-data"):
      disk.delete()
  ```
</CodeGroup>

## Next steps

<CardGroup cols={2}>
  <Card title="Persistent sandboxes" icon="box" href="/compute/sandboxes/introduction">
    Lifecycle, services, network access, and forking in detail.
  </Card>

  <Card title="MySQL" icon="dolphin" href="/guides/apps/mysql">
    Run MySQL the same way, with its data directory on an Archil disk.
  </Card>

  <Card title="Sharing disks" icon="share-nodes" href="/concepts/sharing-disks">
    Mount modes, and reading a disk from several machines.
  </Card>

  <Card title="Python SDK" icon="python" href="/sdks/python">
    Full reference for sandboxes, disks, and serverless execution.
  </Card>
</CardGroup>


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.