# Load a database dump onto a server

**Difficulty:** medium | **Scopes:** vibe:infra | **Stack:** cURL / JavaScript, psql / pg_restore

Restore a PostgreSQL (or other database) dump into a database reachable from a BLACKHOLE server. A dump is a file holding an export of the database contents, produced by `pg_dump`. BLACKHOLE is a server behind a secure tunnel that receives [`exec`](/docs/infra/deploy/exec), [`upload`](/docs/infra/deploy/upload), and [`logs`](/docs/infra/deploy/logs) requests.

## What you need

- A Vibecode API key with the `vibe:infra` scope
- A BLACKHOLE server from which the target database is reachable
- The dump file on your workstation
- `python3` for the cURL examples or Node.js 18+ for the JavaScript ones — they encode the upload request body
- A database client of the same major version as the target database (installed onto the server in step 3)
- Database access: host, user, database name and password

In every example, substitute your own values: `$VIBE_URL` is the base URL `https://vibecode.bitrix24.com`, `$SERVER_ID` is the server identifier, `$VIBE_API_KEY` is your API key. Database-connection placeholders are `DB_HOST`, `DB_USER`, `DB_NAME`.

## How the solution works

1. Prepare a data directory outside `/opt/app` — that one is wiped on a clean deploy.
2. Put the dump on a standalone BLACKHOLE server through `/upload`, bypassing the command-length limit. For a Galaxy app, include the file in the deploy archive through the `source` field of `POST /v1/infra/servers/:id/deploy`.
3. Install a client of the same major version as the target database.
4. Restore as a background job with the application stopped — in a single transaction.
5. Check the row counters.

## Step 1. A data directory outside /opt/app

The `/opt/app` directory is wiped on a clean deploy. That is how a deploy behaves when the application content travels inside the JSON body: there `cleanDeploy` defaults to `true`. When the archive is sent as a separate file, the default is the opposite. The `/opt/data` directory and the installed runtimes survive a repeat deploy, so put the dump and working files under `/opt/data`:

### cURL

```bash
curl -sS --fail-with-body -X POST "$VIBE_URL/v1/infra/servers/$SERVER_ID/exec" \
  -H "X-Api-Key: $VIBE_API_KEY" -H "Content-Type: application/json" \
  -d '{"command": "mkdir -p /opt/data"}'
```

### JavaScript

```javascript
const VIBE_URL = process.env.VIBE_URL ?? 'https://vibecode.bitrix24.com'
const VIBE_API_KEY = process.env.VIBE_API_KEY
const SERVER_ID = process.env.SERVER_ID
const headers = { 'X-Api-Key': VIBE_API_KEY, 'Content-Type': 'application/json' }

const res = await fetch(`${VIBE_URL}/v1/infra/servers/${SERVER_ID}/exec`, {
  method: 'POST',
  headers,
  body: JSON.stringify({ command: 'mkdir -p /opt/data' }),
})
const body = await res.json()
if (!body.success) throw new Error(body.error?.message ?? 'the directory was not created')
```

The `--fail-with-body` flag on `curl` is mandatory: without it a refusal such as `EXEC_BUSY` is printed to the output while the exit code stays zero, and the next step of the scenario runs blind.

## Step 2. Upload the dump via /upload

Megabytes of base64 do not fit into a shell-command argument because of the `ARG_MAX` limit — build the request body in a file and send it with `--data-binary`. The script encodes `db.dump` into base64 and writes it to `upload.json`, then `curl` sends that file:

```bash
python3 - <<'EOF'
import base64, json
data = base64.b64encode(open('db.dump', 'rb').read()).decode()
json.dump({'content': data, 'path': '/opt/data/db.dump', 'mode': '0644'}, open('upload.json', 'w'))
EOF

curl -sS --fail-with-body -X POST "$VIBE_URL/v1/infra/servers/$SERVER_ID/upload" \
  -H "X-Api-Key: $VIBE_API_KEY" -H "Content-Type: application/json" \
  --data-binary @upload.json
```

### JavaScript

```javascript
import { readFile } from 'node:fs/promises'

const dump = await readFile('db.dump')
const res = await fetch(`${VIBE_URL}/v1/infra/servers/${SERVER_ID}/upload`, {
  method: 'POST',
  headers,
  body: JSON.stringify({
    content: dump.toString('base64'),
    path: '/opt/data/db.dump',
    mode: '0644',
  }),
})
const body = await res.json()
if (!body.success) throw new Error(body.error?.message ?? 'the dump was not uploaded')
console.log(`uploaded ${body.data.size} bytes to ${body.data.path}`)
```

The response confirms the written path and the size:

```json
{
  "success": true,
  "data": {
    "path": "/opt/data/db.dump",
    "size": 48317204,
    "extracted": false
  }
}
```

The `size` field is the file size on the server in bytes, `extracted` shows whether the agent unpacked an archive. Compare `size` with the size of the local file: a mismatch means the upload was cut off.

An alternative is the `url` field instead of `content`: the agent downloads the file from that URL on its own, without base64.

## Step 3. Tools of the same major version as the target database

The restore fails if the client's major version does not match the target database's version. A dump from `pg_dump` version 17 into a version 16 database fails with an `unsupported version` error: archive format 1.16 is not readable, and the dump contains the metacommands `SET transaction_timeout` and `\restrict`, which do not exist in version 16. Install the matching major version of the client once — the `/usr` directory survives the deploy, so a reinstall on redeploys is not needed:

### cURL

```bash
curl -sS --fail-with-body -X POST "$VIBE_URL/v1/infra/servers/$SERVER_ID/exec" \
  -H "X-Api-Key: $VIBE_API_KEY" -H "Content-Type: application/json" \
  -d '{"command": "apt-get install -y postgresql-client-17"}'
```

### JavaScript

```javascript
const res = await fetch(`${VIBE_URL}/v1/infra/servers/${SERVER_ID}/exec`, {
  method: 'POST',
  headers,
  body: JSON.stringify({ command: 'apt-get install -y postgresql-client-17' }),
})
const body = await res.json()
if (!body.success) throw new Error(body.error?.message ?? 'the client was not installed')
// A non-zero exit code is NOT a route refusal: success stays true.
if (body.data.exitCode !== 0) throw new Error(body.data.stderr || 'apt-get finished with an error')
```

The route response is successful even when the command itself failed. The marker of the command's success is `data.exitCode`, not `success`.

Synchronous and streaming exec have a `timeout` ceiling of 600 seconds. On `STANDALONE`, [`async=true`](/docs/infra/deploy/exec) supports up to 86400 seconds: keep the operation ID and poll its result; after a lost acknowledgement, find the run in the server operation list with `kind=exec` before any repeat. The recipe below uses a background job via `systemd-run` — it gets its own cgroup and survives the synchronous `exec` timeout:

```bash
curl -sS -X POST "$VIBE_URL/v1/infra/servers/$SERVER_ID/exec" \
  -H "X-Api-Key: $VIBE_API_KEY" -H "Content-Type: application/json" \
  -d '{"command": "systemctl reset-failed install-pg 2>/dev/null; systemd-run --unit=install-pg /bin/bash -c \"apt-get install -y postgresql-client-17\"", "timeout": 30}'

curl -sS -H "X-Api-Key: $VIBE_API_KEY" \
  "$VIBE_URL/v1/infra/servers/$SERVER_ID/logs?service=install-pg&lines=50"
```

## Step 4. Restore — as a background job, with the application stopped

Restoring a large dump takes minutes, and on the `exec` timeout the agent terminates the entire process group with a `SIGKILL` signal, with no pause for a graceful shutdown. That is why the restore always runs as a background job. Put it into a separate script and upload it via `/upload` with `mode: "0755"` — the same way as the dump in step 2:

```bash
#!/bin/bash
set -euo pipefail
set -a; source /opt/data/restore.env; set +a   # a file holding PGPASSWORD=…, mode 0600.
                                              # set -a exports the variable to the child psql
PSQL=(psql -h DB_HOST -p 5432 -U DB_USER -d DB_NAME)

systemctl stop app               # the application's queries hold locks — TRUNCATE/COPY would queue
trap 'rm -f /opt/data/restore.env; systemctl start app' EXIT  # the application comes back even if the
                                 # restore fails, and the password file does not stay behind

# The cleanup and the load run as ONE transaction: any error rolls back the
# TRUNCATE along with it and the old data stays in place. A separate TRUNCATE
# before pg_restore would leave the database empty when the load breaks.
{
  echo 'TRUNCATE TABLE t1, t2 RESTART IDENTITY CASCADE;'
  pg_restore --data-only --no-owner --no-privileges /opt/data/db.dump
} | "${PSQL[@]}" --single-transaction -v ON_ERROR_STOP=1

echo "RESTORE COMPLETE"
```

The script reads the database password from the `/opt/data/restore.env` file with mode `0600` — that file is put in place by the same `/upload` as the dump. A `~/.pgpass` file works too. The password never travels in the command text — the agent logs the first ~200 characters of the command, and the secret would land in the log. The application is stopped for the duration of the restore: its queries hold locks, and without stopping it `TRUNCATE` and `COPY` would queue.

Two `pg_restore --data-only` nuances that surface on related tables:

- **Table order is alphabetical, not dependency-based.** Data is loaded in alphabetical order of table names, so a child table can go before its parent and fail on a foreign-key violation. The fix is a reordered object list: `pg_restore --list db.dump > toc.list`, rearrange the data lines so parent tables go first, and restore with `pg_restore -L toc.list …`.
- **Exclude the migrations schema from the restore.** Tools that talk to the database from code — Drizzle, Prisma and similar — keep their own migrations table in a separate schema, for example `drizzle.__drizzle_migrations`. The dump carries its data, while the application may have already inserted a fresh row there on startup, and then `COPY` fails on a duplicate key. Exclude the whole schema: `pg_restore -N drizzle …`. The migrations table is managed by the application itself.

The `systemctl stop app` in the script above also closes another trap: a unit with `Restart=on-failure` would restart a crashed application in the window between `TRUNCATE` and the start of `COPY`, and it would manage to write its rows — one second is enough. If the application can be started externally during a long restore (for example, a parallel `/deploy`), strengthen the stop to `systemctl mask app`. Lifting the mask then moves into the `trap` as well, otherwise the masked unit will not start: `trap 'systemctl unmask app; systemctl start app' EXIT`.

Launch and monitoring. The `systemd-run` unit gives the job its own cgroup, so it survives the `exec` timeout. Before relaunching under the same name, reset the previous state with `systemctl reset-failed`:

### cURL

```bash
curl -sS --fail-with-body -X POST "$VIBE_URL/v1/infra/servers/$SERVER_ID/exec" \
  -H "X-Api-Key: $VIBE_API_KEY" -H "Content-Type: application/json" \
  -d '{"command": "systemctl reset-failed restore-db 2>/dev/null; systemd-run --unit=restore-db /bin/bash /opt/data/restore.sh", "timeout": 30}'

curl -sS --fail-with-body -H "X-Api-Key: $VIBE_API_KEY" \
  "$VIBE_URL/v1/infra/servers/$SERVER_ID/logs?service=restore-db&lines=50"
```

### JavaScript

```javascript
const start = await fetch(`${VIBE_URL}/v1/infra/servers/${SERVER_ID}/exec`, {
  method: 'POST',
  headers,
  body: JSON.stringify({
    command: 'systemctl reset-failed restore-db 2>/dev/null; systemd-run --unit=restore-db /bin/bash /opt/data/restore.sh',
    timeout: 30,
  }),
}).then(r => r.json())
if (!start.success) throw new Error(start.error?.message ?? 'the job was not started')

// The job runs in the background — follow its progress through the unit log.
const logs = await fetch(
  `${VIBE_URL}/v1/infra/servers/${SERVER_ID}/logs?service=restore-db&lines=50`,
  { headers: { 'X-Api-Key': VIBE_API_KEY } },
).then(r => r.json())
console.log(logs.data.logs)
```

The script above feeds the `TRUNCATE` and the `pg_restore` output as one stream into `psql --single-transaction -v ON_ERROR_STOP=1`, so the cleanup and the load form a single transaction. That guards against two outcomes at once. The first is an interrupted load on already-emptied tables: the major client-version mismatch from step 3 aborts `pg_restore`, and with a separate `TRUNCATE` the database would be left empty with nothing to roll back to. The second is "the job ran for hours yet loaded 0 rows": without `ON_ERROR_STOP` the load continues past the first error and finishes seemingly fine.

The `trap 'rm -f /opt/data/restore.env; systemctl start app' EXIT` line brings the application back on any exit from the script, including an abnormal one. Without it a failed restore leaves the application stopped.

## Step 5. Check the counters

After the restore, verify the row counts in the tables. Here the database password is passed through the `env` field, not in the command text:

### cURL

```bash
curl -sS --fail-with-body -X POST "$VIBE_URL/v1/infra/servers/$SERVER_ID/exec" \
  -H "X-Api-Key: $VIBE_API_KEY" -H "Content-Type: application/json" \
  -d '{"command": "psql -h DB_HOST -U DB_USER -d DB_NAME -tAc \"SELECT count(*) FROM t1\"", "env": {"PGPASSWORD": "…"}, "timeout": 60}'
```

### JavaScript

```javascript
const check = await fetch(`${VIBE_URL}/v1/infra/servers/${SERVER_ID}/exec`, {
  method: 'POST',
  headers,
  body: JSON.stringify({
    command: 'psql -h DB_HOST -U DB_USER -d DB_NAME -tAc "SELECT count(*) FROM t1"',
    env: { PGPASSWORD: process.env.DB_PASSWORD },
    timeout: 60,
  }),
}).then(r => r.json())
if (!check.success) throw new Error(check.error?.message ?? 'the counter was not read')
if (check.data.exitCode !== 0) throw new Error(check.data.stderr)
console.log(`rows in t1: ${check.data.stdout.trim()}`)
```

## Limitations

**One response for the whole command.** A synchronous `/exec` call returns the exit code and both output streams in one response.

```json
{
  "success": true,
  "data": {
    "exitCode": 0,
    "stdout": "RESTORE COMPLETE\n",
    "stderr": "",
    "duration": 4213,
    "truncated": false
  }
}
```

**The channel holds one command.** While the previous synchronous command is still running, the channel is busy.

```json
{
  "success": false,
  "error": {
    "code": "EXEC_BUSY",
    "message": "Another operation is running on this server",
    "retryable": true,
    "retryAfter": 10,
    "hint": {
      "reason": "A 'deploy' operation currently holds the lock on this server.",
      "recovery": "If the previous operation crashed or its deploy task is stuck (e.g. the backend restarted, or the server was deleted and recreated), call POST /v1/infra/servers/:id/unstick and retry: it refuses with 409 OPERATION_IN_PROGRESS whenever a lock is still OBSERVED — a live one, or one whose owner cannot be proven dead. That guard covers the PLATFORM lock only. With no lock observed the call proceeds, and it works by bouncing the agent tunnel: the reconnect handler group-kills whatever the agent is running, including a detached background job. That is precisely why it cures a leaked agent mutex, and precisely why it is not safe to fire while your own long command may still be alive. It clears an observed lock on its own only when that lock is an exec mutex that outlived its own TTL. DELETE /v1/infra/servers/:id/lock releases the backend lock UNCONDITIONALLY and is a deliberate SECOND step. An ABSENT error.hint.holder does not authorize it: that read is fail-open, so a missing holder is also what you get when the platform could not read one. Absence buys a normal retry after Retry-After — nothing more. Force-release only after you have confirmed the server is idle INDEPENDENTLY (GET /v1/infra/servers/:id plus the logs); otherwise keep retrying or escalate.",
      "recoveryAction": "POST /v1/infra/servers/:id/unstick",
      "autoExpiresInSeconds": 42,
      "note": "The backend lock auto-expires after ~15 minutes. The Black Hole agent also holds its own synchronous exec mutex (≤10 min), or a durable exec admission gate for the whole job. First read GET /v1/infra/servers/:id/operations?kind=exec and reconcile durable operations. While a durable operation is running, poll its operationId; an unknown outcome needs support and positive proof that the job stopped. POST /v1/infra/servers/:id/unstick does not clear a durable admission gate. Only when no durable job remains active and a leaked synchronous mutex is independently confirmed should you use that guarded recovery. The order lives in hint.recovery: unstick comes BEFORE any unconditional release, never after it."
    }
  }
}
```

First read `GET /v1/infra/servers/:id/operations?kind=exec` and the previous operation outcome. On `STANDALONE`, a running durable job keeps the execution channel busy after `202` releases the platform lock. Poll its operation; an unknown outcome needs support and independently confirmed full termination. Missing `autoExpiresInSeconds` does not prove a leaked mutex. Tunnel recovery and lock deletion do not clear durable admission. Galaxy commands run synchronously with a 600-second limit and share the host channel across all applications and deploys; your own operation list may not show another application’s job, so an empty list does not prove the host is idle.

The `retryable` and `retryAfter` fields are the machine signal: a retry is worthwhile, the pause is in seconds (never more than 10 — it is the poll interval, not the lock lifetime) and also arrives in the `Retry-After` header. The lock auto-expires in about 15 minutes; a stuck one goes first to the guarded `POST /v1/infra/servers/:id/unstick` — while a lock is observed it answers `409`, and it clears one by itself only when that lock is an exec mutex past its own TTL — and the unconditional `DELETE /v1/infra/servers/:id/lock` stays the last step and only once the server is independently confirmed idle.

The full list of error codes — [Errors](/docs/errors).

**Large files on a standalone server go through `/upload`.** Megabytes of base64 do not fit into a command argument because of the `ARG_MAX` limit, and a command longer than 10000 characters is refused with `COMMAND_TOO_LONG`. On a standalone server, send large payloads and scripts via [`/upload`](/docs/infra/deploy/upload). For a Galaxy host, `/upload` returns `GALAXY_HOST_NOT_A_DEPLOY_TARGET`. For a Galaxy app, it returns `GALAXY_APP_USE_GALAXY_ROUTE`. Include app files in the deploy archive through the `source` field of `POST /v1/infra/servers/:id/deploy`.

**Send a dump larger than 72 MB as a link.** The `content` field of this step is base64 inside the request body, and the body is capped at 96 MB, which is about 72 MB of the dump itself. A body over the cap is refused with `413 INLINE_SOURCE_TOO_LARGE`, and re-sending the same request will not help. Host the dump where the server can fetch it over HTTPS and pass `url` instead of `content` — over a link the route accepts up to 500 MB. Shrinking the dump works too: `pg_dump -Fc` compresses the output.

**Choose a mode for long work.** Synchronous and streaming `exec` have a `timeout` ceiling of 600 seconds. Durable `async=true` on `STANDALONE` supports up to 86400 seconds and retains the result; poll the same operation and reconcile a lost acknowledgement before any repeat. At the configured timeout, the agent terminates the process group with `SIGKILL`, with no pause for a graceful shutdown. A synchronous command that exceeds `timeout` reports `EXEC_TIMEOUT`; it may already have performed part of the work. A durable operation can end with `status: "unknown"` after its deadline. Neither outcome proves that the command never started or fully stopped: read the same operation, check state and logs, and independently confirm termination before considering a repeat. This recipe runs the restore as a background job through `systemd-run`; that is an alternative to durable exec.

**Data lives outside `/opt/app`.** `/opt/app` is wiped on a clean deploy, so the dump and the working files live in `/opt/data`. Runtimes installed into `/usr` survive a redeploy — the client does not need reinstalling.

**Do not declare `/opt/data` as the application's data directory.** The `dataDirs` field in the deploy body hands a directory to the account the application runs under, and whoever owns a directory can delete or replace any file in it — including `restore.env` with the password and `restore.sh` itself, which you launch as `root`. For this recipe that is a direct path to swapping out the script being executed. If the application needs a state directory of its own, declare a separate one, `/opt/data/state` for example. For the same reason `dataDirsRecursive` is not accepted for `/opt/data`.

**The password never goes in the command text.** The agent logs the first ~200 characters of the command. The database password therefore travels in the `PGPASSWORD` environment variable, a `~/.pgpass` file, or the `env` field — never in the command text.

**Rollback on error.** The restore runs as a single transaction: on an error it rolls back together with the table cleanup. A separate cleanup before the load would leave the database empty on a failed restore.

## Full code

The script goes through all five steps: it prepares the directory, puts the dump and the restore script in place, starts the background job and waits for it to finish by reading the log. This is the only runnable artifact on the page — the step examples above show individual calls.

**The script empties the tables of the target database.** Before the first run, make sure `DB_NAME` points at the database you intend to overwrite and that you have a separate backup.

```javascript
// restore.mjs — upload a dump onto a BLACKHOLE server and restore the database
// The .mjs extension is mandatory: the script uses top-level await, and a .js file
// without "type": "module" is read by Node as CommonJS and fails during parsing.
import { readFile } from 'node:fs/promises'

const VIBE_URL = process.env.VIBE_URL ?? 'https://vibecode.bitrix24.com'
const VIBE_API_KEY = process.env.VIBE_API_KEY
const SERVER_ID = process.env.SERVER_ID
const DB_PASSWORD = process.env.DB_PASSWORD
if (!VIBE_API_KEY || !SERVER_ID || !DB_PASSWORD) {
  throw new Error('set VIBE_API_KEY, SERVER_ID and DB_PASSWORD')
}

const BASE = `${VIBE_URL}/v1/infra/servers/${SERVER_ID}`
const headers = { 'X-Api-Key': VIBE_API_KEY, 'Content-Type': 'application/json' }
const sleep = ms => new Promise(resolve => setTimeout(resolve, ms))

// The route answers with success even for a command that failed: the marker of the
// command's own success is exitCode, not success. Both are checked.
async function call(path, init) {
  const res = await fetch(`${BASE}${path}`, init)
  const body = await res.json().catch(() => null)
  if (!body?.success) throw new Error(body?.error?.message ?? `request refused (${res.status})`)
  return body.data
}

async function exec(command, timeout = 60) {
  const data = await call('/exec', {
    method: 'POST',
    headers,
    body: JSON.stringify({ command, timeout }),
  })
  if (data.exitCode !== 0) throw new Error(data.stderr || `command exited with code ${data.exitCode}`)
  return data.stdout
}

async function upload(localPath, remotePath, mode) {
  const bytes = await readFile(localPath)
  const data = await call('/upload', {
    method: 'POST',
    headers,
    body: JSON.stringify({ content: bytes.toString('base64'), path: remotePath, mode }),
  })
  // The size on the server is compared with the local one: a mismatch means the upload was cut off.
  if (data.size !== bytes.length) {
    throw new Error(`${remotePath}: uploaded ${data.size} of ${bytes.length} bytes`)
  }
  return data
}

// 1. A directory outside /opt/app — that one is wiped on a clean deploy
await exec('mkdir -p /opt/data')

// 2-3. The dump, the restore script and the password file. The password goes as a
// separate file with mode 0600, not in the command text: the agent logs the first
// ~200 characters of the command, and the secret would land in the log.
await upload('./db.dump', '/opt/data/db.dump', '0644')
await upload('./restore.sh', '/opt/data/restore.sh', '0755')
await call('/upload', {
  method: 'POST',
  headers,
  body: JSON.stringify({
    content: Buffer.from(`PGPASSWORD='${DB_PASSWORD.replace(/'/g, `'\\''`)}'\n`).toString('base64'),
    path: '/opt/data/restore.env',
    mode: '0600',
  }),
})

// 4. Background job: its own cgroup survives the exec timeout.
// reset-failed is mandatory — otherwise a relaunch under the same name is refused.
await call('/exec', {
  method: 'POST',
  headers,
  body: JSON.stringify({
    command: 'systemctl reset-failed restore-db 2>/dev/null; '
      + 'systemd-run --unit=restore-db /bin/bash /opt/data/restore.sh',
    timeout: 30,
  }),
})

// 5. Wait for the completion marker in the unit log
for (let i = 0; i < 120; i++) {
  await sleep(15_000)
  const { logs } = await call('/logs?service=restore-db&lines=50', {
    headers: { 'X-Api-Key': VIBE_API_KEY },
  })
  // The route returns logs as an ARRAY of lines, and journalctl prefixes each
  // one with a timestamp and the unit name. So the marker is matched inside the
  // joined text, not against a whole array element.
  const text = logs.join('\n')
  if (text.includes('RESTORE COMPLETE')) {
    console.log('restore finished')
    break
  }
  if (/^.*(FATAL|ERROR:)/m.test(text)) throw new Error(`the restore broke down:\n${text}`)
  if (i === 119) throw new Error('the completion marker did not appear within 30 minutes')
}

const rows = await exec('psql -h DB_HOST -U DB_USER -d DB_NAME -tAc "SELECT count(*) FROM t1"')
console.log(`rows in t1: ${rows.trim()}`)
```

## See also

- [A fast release cycle](/docs/infra/deploy/fast-cycle)
- [Running commands on a server](/docs/infra/deploy/exec)
- [Uploading files to a server](/docs/infra/deploy/upload)
- [Reading server logs](/docs/infra/deploy/logs)