# Production backups

Tidepool's own database backup runs on the same host on the same pattern; it is documented in tidepool's `DEPLOY.md` ("Backup and restore").

## Schedule

| Unit | Time (UTC) | Installed script | Output |
|---|---|---|---|
| `tidepool-pg-backup.timer` | 02:17 | `/usr/local/sbin/tidepool-pg-backup` | `/opt/tidepool/backups/tidepool-*.dump` |
| `coves-pg-backup.timer` | 02:47 | `/usr/local/sbin/coves-pg-backup` from `scripts/pg-backup.sh` | `/opt/coves/backups/coves-<stamp>.dump` |
| `coves-pds-backup.timer` | 03:07 | `/usr/local/sbin/coves-pds-backup` from `scripts/pds-backup.sh` | `/opt/coves/backups/pds-<stamp>.tar.gz` |

The AppView Postgres backup is `pg_dump -Fc`, verified with a full read (`pg_restore -f /dev/null`) before it gets its final name.

The PDS backup copies every SQLite database through the online backup API, checks each copy with `PRAGMA integrity_check`, and adds the per-actor signing `key` files. The PDS is the source of truth for every hosted community and native account; nothing else can rebuild it. The run fails if any account in `account.sqlite` lacks its store or signing key in the snapshot. Blobs are in object storage (`PDS_BLOBSTORE_S3_BUCKET`), not in this archive.

The `moderation_*` tables in the AppView database are admin decisions, not index state. They have no foreign key to posts or comments and cannot be rebuilt from the firehose. Any truncate, reindex or partial-restore procedure must keep them.

## Retention and failures

Retention is 14 days (`RETENTION_DAYS`). Files are root-only (`umask 077`) and the directory is mode 700.

A run that fails while writing the dump or archive keeps its partial under a fixed name that each failed retry overwrites: `coves-last-failed.dump.partial` or `pds-last-failed.tar.gz.partial`. It is never deleted. Only a later successful run warns about it, once it is older than a day. A PDS run that fails before writing the archive leaves nothing behind. A `.pds-stage.*` directory left by a killed PDS run holds plaintext database copies and signing keys; later successful runs warn about it once it is older than a day.

A failed run retries every 5 minutes, up to 4 starts in 2 hours, then stays failed until the next night. A run missed while the host was down fires at boot (`Persistent=true`).

Output goes to the journal (`journalctl -u coves-pg-backup.service`). A failed run shows in `systemctl --failed`. Nothing alerts on failure.

## Not backed up

- Secrets in `.env` files: `/opt/coves/.env` (`ENCRYPTION_KEY`, OAuth keys, the PDS PLC rotation key and admin password, S3 keys), `/opt/tidepool/.env` (`BRIDGE_KEK`), and the aggregators' `.env` files. A dump restored without `ENCRYPTION_KEY` has community and aggregator credentials as unreadable ciphertext. Keep these in a password manager, not on this host.
- Off-host copies. The dumps sit on the same disk array as the data. They cover a dropped database or a bad migration, not loss of the host.
- The S3 blob bucket. It has one copy, in object storage.

## Install or update

The scripts are installed outside `/opt/coves`, because deploys `git pull` that checkout and a changed or untracked file there blocks the pull. After changing either script, re-run the `install` lines.

```bash
sudo apt-get install -y sqlite3
sudo install -d -m 700 /opt/coves/backups   # the scripts do not create it
cd /opt/coves
sudo install -m 755 scripts/pg-backup.sh  /usr/local/sbin/coves-pg-backup
sudo install -m 755 scripts/pds-backup.sh /usr/local/sbin/coves-pds-backup
sudo cp scripts/systemd/coves-{pg,pds}-backup.{service,timer} /etc/systemd/system/
sudo systemctl daemon-reload
sudo systemctl enable --now coves-pg-backup.timer coves-pds-backup.timer
sudo systemctl start coves-pg-backup.service coves-pds-backup.service   # first backups now
systemctl list-timers 'coves-*' 'tidepool-*'
```

## Restore AppView Postgres

Restore into a freshly created database, never over the live one. `pg_restore --clean` drops only objects in the archive, so tables or columns from a later migration survive and goose then fails on them. `--exit-on-error --single-transaction` aborts the whole restore on any error.

Set `DUMP` on the block's second line, then run it. It stops at the first failed step. The dump must pass a full read before the AppView is stopped or the database dropped, and the AppView starts only after the restore succeeds: it applies migrations on startup, so it must not boot against an empty database. The script goes through `-c`, not stdin, because `pg_restore` reads the dump from stdin. It is one single-quoted string, so it must not contain a single quote.

```bash
sudo bash -euo pipefail -c '
DUMP=/opt/coves/backups/coves-<stamp>.dump
POSTGRES=coves-prod-postgres
cd /opt/coves

DB_USER="$(docker exec "$POSTGRES" printenv POSTGRES_USER)"
DB_NAME="$(docker exec "$POSTGRES" printenv POSTGRES_DB)"

test -s "$DUMP"
docker exec -i "$POSTGRES" pg_restore -f /dev/null < "$DUMP"

docker compose -f docker-compose.prod.yml stop appview
docker exec "$POSTGRES" dropdb -U "$DB_USER" "$DB_NAME"
docker exec "$POSTGRES" createdb -U "$DB_USER" "$DB_NAME"
docker exec -i "$POSTGRES" pg_restore --exit-on-error --single-transaction \
  --no-owner --no-acl -U "$DB_USER" -d "$DB_NAME" < "$DUMP"
docker compose -f docker-compose.prod.yml start appview
'
```

The AppView resumes Jetstream from the cursors in the dump. Anything indexed after the dump is lost unless Jetstream can still replay it.

To check a dump without touching production, restore it into a throwaway container:

```bash
DUMP=/opt/coves/backups/coves-<stamp>.dump    # root-only: read it with sudo
sudo docker run -d --name coves-restore-drill --network none \
  -e POSTGRES_PASSWORD=drill postgres:15
# TCP, not the socket: during first-boot init the socket answers for a
# temporary server that is about to restart.
until sudo docker exec coves-restore-drill pg_isready -h 127.0.0.1 -U postgres; do sleep 1; done
sudo cat "$DUMP" | sudo docker exec -i coves-restore-drill pg_restore --exit-on-error --no-owner --no-acl -U postgres -d postgres
sudo docker exec coves-restore-drill psql -U postgres -At \
  -c 'SELECT count(*) FROM posts' -c 'SELECT count(*) FROM communities'
# -v: the image's anonymous data volume holds a full copy of production data.
sudo docker rm -f -v coves-restore-drill
```

## Restore the PDS

This procedure has never been rehearsed.

First record the live sequencer's high-water mark, unless the live database is the thing that is broken:

```bash
sudo sqlite3 -readonly /var/lib/docker/volumes/coves-prod-pds-data/_data/sequencer.sqlite \
  'SELECT max(seq) FROM repo_seq'
```

Set `ARCHIVE` on the block's second line, then run it. It stops at the first failed step. The PDS must be stopped and the archive must extract with an `account.sqlite` before the live data is touched, and the live data is moved aside, not deleted. As above, the script goes through `-c` so no command inside it can swallow the rest.

```bash
sudo bash -euo pipefail -c '
ARCHIVE=/opt/coves/backups/pds-<stamp>.tar.gz
VOLUME=/var/lib/docker/volumes/coves-prod-pds-data
STAMP="$(date -u +%Y%m%dT%H%M%SZ)"

cd /opt/coves
docker compose -f docker-compose.prod.yml stop pds
test "$(docker inspect -f "{{.State.Running}}" coves-prod-pds)" = false

NEW="$(mktemp -d "${VOLUME}/_data.restore-${STAMP}.XXXXXX")"
tar -C "$NEW" -xzf "$ARCHIVE"
test -f "${NEW}/account.sqlite"
# mktemp creates the directory mode 700; match the live directory.
chown --reference="${VOLUME}/_data" "$NEW"
chmod --reference="${VOLUME}/_data" "$NEW"

mv "${VOLUME}/_data" "${VOLUME}/_data.before-restore-${STAMP}"
mv "$NEW" "${VOLUME}/_data"
docker compose -f docker-compose.prod.yml start pds
'
```

Anything the PDS accepted after the archive was taken is lost. The restored PDS also rewinds in two ways that relays and Jetstream notice. Its sequencer restarts below the cursor they already hold, so new events can be skipped until `seq` passes the high-water mark recorded above. Every repo's `rev` goes back, so relays may treat those repos as out of sync. After the PDS is up, ask each relay that crawls it to recrawl (`com.atproto.sync.requestCrawl` with this PDS's hostname).