# Backup and restore

> What to back up in a QueryProxy install, how to copy a SQLite database safely, how to replicate it with Litestream, and how to keep and restore the APP_KEY.

- Documentation: Guides
- Updated: 2026-10-25
- Source: https://queryproxy.com/docs/backup-restore/
- Language: en-US
- Author: Muhammet Şafak

---
A QueryProxy backup has two halves that are useless apart: the **data** (the
application database and the result files) and the **`APP_KEY`** that decrypts
the stored connection credentials, chat secrets, masking keys and TLS
certificates inside it. Back up both, and keep them in different places.

## What to back up

| What | Where it lives | Notes |
| :-- | :-- | :-- |
| Application database | `database.sqlite` on the data volume, or the MySQL, MariaDB or PostgreSQL database that `DB_*` points at | Users, teams, connections (encrypted), requests, masking rules, audit log |
| `APP_KEY` | The `APP_KEY` variable, `APP_KEY_FILE`, or the generated `.app_key` on the data volume | Without it the encrypted columns cannot be read. See [the key](#the-app_key) |
| Result files | The `QUERYPROXY_RESULT_DISK` disk; by default `storage/app/private/results` on the data volume | Masked NDJSON files. They are deleted after `QUERYPROXY_RESULT_TTL_DAYS`, so many installs skip them |
| Your environment | The `-e` flags, `--env-file` or compose file | Needed to start the same configuration again |

The data volume (`/var/www/html/storage/app`) holds the SQLite database, the
generated key file and the result files. The TLS files under `storage/app/tls`
are written at run time from encrypted values in the database, so they come back
with the database.

> **The backup holds the key next to the data it protects** when the key lives
> on the volume. Write the archive readable by its owner only (`umask 077`) and
> store it as carefully as the key itself. Better, supply `APP_KEY` from outside
> the volume; `queryproxy:doctor` warns when the key file shares a volume with
> the SQLite database.

## Copy a SQLite database

The simplest backup is the one in the [README](https://github.com/QueryProxy/QueryProxy#upgrading):
stop the container and archive the volume. Nothing is writing while the
container is stopped, so the copy is consistent.

To copy the database while the container runs, use SQLite's own `.backup`
command from a machine that has the `sqlite3` command-line tool (the QueryProxy
image does not ship it) and can read the volume:

```bash
sqlite3 /path/to/database.sqlite ".backup '/backups/queryproxy-$(date +%F).sqlite'"
```

`.backup` uses SQLite's online backup mechanism. SQLite documents that other
connections may keep writing during an online backup, and that a write from
another process usually makes the copy restart; under constant heavy writing a
copy may not finish. A QueryProxy install writes little, so this rarely
matters, but check that the command exits with status 0. Do not copy
`database.sqlite` with `cp` or `tar` while QueryProxy runs: the file may be
mid-write, and its journal or WAL side files (`-journal`, `-wal`, `-shm`, the
last two once Litestream has switched the database to WAL) can hold changes the
main file does not.

For MySQL, MariaDB and PostgreSQL, use the database's own tools (`mysqldump`,
`pg_dump`, or snapshots), as you would for any application database.

## Continuous replication with Litestream

If you want a restore point that is minutes old instead of a nightly copy,
[Litestream](https://litestream.io/) replicates a SQLite database to object
storage such as S3. It is not part of QueryProxy and the QueryProxy test suite
does not exercise it, so rehearse a restore in a staging copy before you rely on
it.

Litestream works only with SQLite's WAL journaling mode and switches the
database to it if the application has not. It takes short write locks when it
checkpoints. Run one Litestream process per database and replicate to a path no
other installation uses: its documentation warns that several applications
replicating into the same bucket and path can leave you unable to restore.

A minimal configuration:

```yaml
dbs:
  - path: /var/www/html/storage/app/database.sqlite
    replica:
      type: s3
      bucket: my-queryproxy-backups
      path: queryproxy
      region: eu-central-1
```

Litestream reads `AWS_ACCESS_KEY_ID` and `AWS_SECRET_ACCESS_KEY` from the
environment. Run it as a sidecar that mounts the same data volume. Its restore
command takes the replica URL and an output path:

```bash
litestream restore -o /restore/database.sqlite s3://my-queryproxy-backups/queryproxy
```

Litestream replicates the database only. The `APP_KEY` and the result files are
not in it.

## The APP_KEY

Treat the key as the most valuable secret of the install. It encrypts every
target database credential, the chat signing secrets, the per-team masking keys
and the stored TLS certificates. A lost key cannot be recovered, and a container
started without its original key generates a new one and cannot read any of it.

An escrow procedure that works:

1. **Generate it once, explicitly**, and keep it out of the data volume:
   `docker run --rm queryproxy/queryproxy php artisan key:generate --show`.
2. **Store it in a secret manager** or a sealed envelope that a second person can
   open: at least two custodians, in two places, neither of them the backup
   storage.
3. **Supply it with `APP_KEY` or `APP_KEY_FILE`** (a Docker or Kubernetes secret),
   see [Configuration](/docs/configuration/#secrets-from-files).
4. **Record rotations.** When you rotate, move the old key into
   `APP_PREVIOUS_KEYS` (comma-separated) so older ciphertext still decrypts, and
   store both. A backup taken before a rotation needs the key that was current
   then.
5. **Test it.** After every rotation or restore, `queryproxy:doctor` reports
   whether the key can decrypt stored credentials (check `app_key`).

## Restore

1. Stop the container and remove it (`docker stop queryproxy && docker rm queryproxy`).
2. Put the data back. For an archive made as in the README:

   > [!WARNING]
   > The second command deletes everything in the `queryproxy-data` volume before
   > it extracts the backup. Check that the archive is the one you want first.

   ```bash
   docker run --rm -v queryproxy-data:/data -v "$PWD":/backup alpine \
     sh -c 'find /data -mindepth 1 -delete \
       && tar xzf /backup/queryproxy-data.tgz -C /data'
   ```

   For SQLite, first stop the Litestream sidecar if you run one: a running
   sidecar could replicate the restored file over the existing replica.
   Then delete `database.sqlite-wal`, `database.sqlite-shm` and
   `database.sqlite-journal` next to the old database; SQLite would apply a
   leftover WAL to the restored file and corrupt it. After that, place a
   `.backup` copy at the `DB_DATABASE` path as `database.sqlite` on the volume,
   or run `litestream restore -o <path>` to a path where no file exists yet
   (remove the old database file first). For
   MySQL, MariaDB and PostgreSQL, restore the database with its own tools.
3. Start the **same version** the backup came from, with the same environment
   file and the **same `APP_KEY`** (plus `APP_PREVIOUS_KEYS` if you rotated).
4. Check the result:

   ```bash
   docker exec queryproxy php artisan queryproxy:doctor
   docker exec queryproxy php artisan audit:verify
   ```

   `app_key` must be `ok` and `audit:verify` must report an intact chain. A
   break in a restored copy means the copy itself is damaged (for example a
   plain file copy taken while QueryProxy was writing); try the previous
   backup. See [Audit chain and SIEM export](/docs/audit-export/).
5. Only then upgrade, if that was the plan.

A restored install may hold `pending` requests whose chat buttons belong to
messages that are hours or days old. The buttons of such a message can have
expired; the reminder posts a fresh card for a request that is still pending.
The requests themselves never expire.

## Next steps

- [Upgrading and rolling back](/docs/upgrading/)
- [Troubleshooting](/docs/troubleshooting/)
- [Threat model](/docs/threat-model/)