# 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 the server through `/upload`, bypassing the command-length limit.
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`.

If the installation is long and exceeds the `timeout` limit (600 seconds maximum), run it as a background job via `systemd-run` — the job gets its own cgroup and is not interrupted by the `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), force-release the lock and retry.",
      "recoveryAction": "DELETE /v1/infra/servers/:id/lock",
      "autoExpiresInSeconds": 42,
      "note": "The backend lock auto-expires after ~15 minutes. The Black Hole agent also holds its own exec mutex (\u226410 min) that releases when the running command finishes or times out. If force-releasing the backend lock STILL yields EXEC_BUSY, the agent exec mutex has leaked (a detached background process is holding it open) — call POST /v1/infra/servers/:id/unstick to force-release the lock AND bounce the agent tunnel (its reconnect handler group-kills the stuck exec, freeing the mutex) with no VM reboot."
    }
  }
}
```

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, and a stuck one is released manually through `DELETE /v1/infra/servers/:id/lock`.

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

**Large files 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`. That is why the dump and any large file travel through `/upload` rather than `exec`. The `/upload` route itself accepts up to 500 MB per file.

**Long work becomes a background job.** The `exec` `timeout` ceiling is 600 seconds, and once it expires the agent terminates the whole process group with `SIGKILL`, with no pause for a graceful shutdown. A command that did not fit into `timeout` is refused with `EXEC_TIMEOUT`. Anything longer — the restore itself included — therefore runs as a background job through `systemd-run`.

**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.

**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)