Backups and restore

In short

A self-hosted SpeculaPro installation has two things you can’t afford to lose:

1. The PostgreSQL database. It holds every customer record, transaction, rule version, alert, case, report and audit entry. It is your regulatory evidence.
2. The encryption keys. Without them, the personal data in the database (and in every backup of it) can’t be read. See Encryption keys.

Evidence files added to cases are in the database too, encrypted with the workspace’s key: there is no separate file store to back up, a database backup includes them, and restoring them needs the same keys. They can make the database much larger than its row counts suggest (a case holds up to 50 files of up to 10 MB each), so watch backup size and duration once investigators start adding files.

Everything else (the binary, the configuration) can be rebuilt. Redis/Valkey holds no evidence, but there is no command to rebuild its running totals in a running installation (see below).

A backup without its keys is useless, and keys stored with the backup are a breach

Back up the database and the keys separately, with different people or systems holding each. Someone who steals a database backup alone sees encrypted personal data. Someone who steals a backup and the keys sees everything.

What to back up

What	Back up?	Why
PostgreSQL database	Yes, continuously	The system of record and your regulatory evidence, including the evidence files on cases and every exported report
SP_SECURITY_MASTER_KEYS (every version ever used)	Yes, separately and offline	Needed to read personal data in the database and in old backups
SP_SECURITY_BLIND_INDEX_KEY	Yes, separately and offline	Needed for searching and matching; never changes
Database role passwords and other secrets	Yes, in your secret manager	Needed to reconnect after a rebuild
Configuration (SP_* settings, other than secrets)	Yes, in version control or your deployment tooling	Lets you rebuild a server quickly
Redis / Valkey	Optional, but keep persistence (AOF) on	Holds only derived data: no evidence is lost without it. But there is no rebuild command for a running installation (only a workspace move rebuilds a workspace’s totals, in the target region), so if it is lost the rules’ running totals start again from empty and long-window rules under-count for up to 180 days. An AOF file or snapshot gets them back. See Redis / Valkey
Kafka / Redpanda topics	No (see below)	A transport, not a store of record
ClickHouse (the optional analytics store)	Optional	A derived copy: speculapro analytics rebuild --from postgres writes it out again. Back it up only to avoid a long rebuild, and remember those backups hold personal data that the retention purge doesn’t reach. After restoring PostgreSQL, rebuild it so the two agree
The speculapro binary	Keep the release you run	So you can roll back an upgrade

More than one region

In a multi-region deployment, back up every cell on its own, to storage in its own region. Never ship one cell’s backups to another region: a backup is the data. The directory has its own small database; it can be backed up anywhere and is rebuilt from the cells with speculapro cell sync.

PostgreSQL

Use continuous backup with point-in-time recovery (PITR): a full base backup plus a continuous archive of the write-ahead log (WAL). With PITR you can restore the database to any moment, for example to the minute before an operator’s mistake, not just to last night.

• With Pigsty: Pigsty sets up pgBackRest for this. Configure a backup repository, ideally on separate storage or a separate site, and a retention period. See the Pigsty documentation for the exact settings.
• Without Pigsty: use pgBackRest, Barman or WAL-G, or your platform’s managed backup if your Postgres is hosted. Any of these can do base backups plus WAL archiving.

Recommendations:

• At least one copy off the database server, and ideally off site.
• Encrypt backup storage at rest. Personal data is already encrypted by SpeculaPro, but the rest of the data (transactions, cases, notes) is not.
• Set backup retention to cover at least your regulatory record-keeping period, or archive older backups so you can still restore them. Confirm the required period with current FIC and Bank of Ghana guidance.
• Monitor backup jobs and alert when one fails. See Monitoring.

Logical dumps are a supplement, not a plan

pg_dump is useful for copying a database or a single table, but it can’t do point-in-time recovery and is slow to restore at scale. Use it alongside PITR, not instead of it.

Kafka / Redpanda

Kafka carries events between SpeculaPro’s components. Once the persister has written them to PostgreSQL, the database holds the record. Kafka therefore doesn’t need a traditional backup, but retention matters:

• Keep topics long enough to replay after an outage, for example a consumer that was down for a weekend. Several days is a common starting point.
• Size retention so a full disk can’t happen during a long outage.
• On a cluster, use a replication factor of 3 so the loss of one broker loses nothing. See Kafka / Redpanda.

Accepted transactions live only in Kafka until the persister writes them

When the ingest API answers accepted, the transaction is safely on Kafka (written to all in-sync replicas), but it reaches PostgreSQL only when the persister writes it. If the persister is stopped for longer than the topic’s retention, unwritten transactions are deleted by Kafka and lost. Keep <prefix>txn.ingested retention comfortably longer than the longest persister outage you could have: a long weekend at least, a week is safer. Watch the persister’s consumer lag so an outage never goes unnoticed.

How to run a restore drill

A backup you have never restored is a hope, not a backup. Run this drill at least once a year, after major upgrades, and whenever your backup setup changes.

1. Prepare an isolated environment. A separate server or network with no route to production, so the restored system can’t send notifications or receive real traffic.
2. Restore the database from your backup tooling to a chosen point in time, for example yesterday at 12:00.
3. Fetch the keys from their offline backup, not from production. This is the part that proves the key backup works.
4. Configure SpeculaPro in the isolated environment with the restored database and the backed-up keys. Set SP_ENV to something other than development so the keys are actually required.
5. Start it and check the health endpoint reports ok (see Monitoring).
6. Check the data:
   • Run speculapro migrate status and confirm every migration is applied.
   • Sign in as a test administrator.
   • Confirm records from just before the restore point are present.
   • Confirm personal data can be read by a user with permission to view it. If it can’t, a key version is missing from your backup.
7. Record the drill: date, restore point, time taken, problems found, and who took part. Regulators and auditors may ask for this.
8. Destroy the environment securely when you’re done. It holds real customer data.

Time the drill

Note how long the restore took from start to a working system. That is your realistic recovery time. If it is longer than your business can tolerate, you’ve learned that safely.

Related

• Encryption keys
• Key rotation drill
• PostgreSQL
• Production checklist
• Upgrading