Backups

Back up and restore the database, the encryption key and the bucket.

What to Back UpCopy Anchor Link

• Database: PostgreSQL database, including the job queue tables (job_queue, job_schedules). It includes the bucket settings with their encrypted secret
• Files: S3 bucket contents
• Configuration: .env file
• Encryption key: See Encryption Key

Encryption KeyCopy Anchor Link

Every stored file and the saved bucket secret are encrypted under this key. Without it, a restored database and bucket cannot be decrypted, and the key cannot be recovered.

Where the key is:

After a rotation the key file holds every key version. Back up the whole file: a database backup can only be read with the versions that were in use when it was taken. See Key Rotation.

How to keep it:

1. Copy the key once, after setup, and again after each key rotation. It does not change otherwise.
2. Store it apart from the database backups, for example in a password manager. A backup that holds both the database and the key gives full access to the files.
3. To restore, put the key back at the same location with the same value before starting the containers. The server refuses to start with a different key.

Before UpdatesCopy Anchor Link

update.sh dumps the database to backups/pre-update-<time>.dump before each update and keeps the last 3. These dumps are for rolling back an update, not a replacement for regular backups. See Updating.

Backup ScriptCopy Anchor Link

TMA Cloud includes a backup and restore script, db-backup-restore.sh. setup.sh puts it in the install directory and update.sh keeps it current. In a repository checkout it is scripts/db-backup-restore.sh. It handles full PostgreSQL backups and restores through Docker or the PostgreSQL client tools on the host.

Run it from the install directory with ./db-backup-restore.sh; the examples below use that form.

Without a local copy, run it in the install directory:

curl -fsSL https://raw.githubusercontent.com/TMA-Cloud/TMA/main/scripts/db-backup-restore.sh | bash -s -- backup

The words after -- are the command and its arguments, for example bash -s -- restore backups/<file>.dump. The script uses the current directory as the install directory; set TMA_DIR to use another one. A restore asks for the database name on the terminal, because the script itself arrives on standard input. Without a terminal, the restore is cancelled.

How It WorksCopy Anchor Link

• Uses pg_dump with custom format (compressed binary, supports selective and parallel restore)
• Takes a --serializable-deferrable snapshot (consistent read without blocking writes)
• Verifies the dump with pg_restore --list immediately after creation. The dump is written as <name>.dump.part and renamed only after this check, so a failed dump leaves no file behind
• Computes a SHA-256 checksum and writes a .meta sidecar file
• Records per-table row counts at backup time
• Auto-prunes its own old backups based on BACKUP_RETAIN_COUNT (default 10). The pre-update-*.dump files from update.sh are not counted or removed
• Writes dumps with mode 0600 in a 0700 backups/ directory

Container DetectionCopy Anchor Link

The script finds the PostgreSQL container in this order:

1. DB_CONTAINER (explicit override)
2. The postgres service of the Docker Compose project in the script's directory

Otherwise it uses the PostgreSQL client tools on the host and connects to the host and port in DATABASE_URL. It never picks a container by name or image, because a restore replaces the database.

Database BackupCopy Anchor Link

Create a BackupCopy Anchor Link

./db-backup-restore.sh backup

Output is saved to backups/<database>_<TIMESTAMP>.dump, named after the database in DATABASE_URL, with a .meta file alongside it.

Example OutputCopy Anchor Link

── Full Database Backup ──
[INFO]  Connected to 'tma_cloud_storage' via Docker container 'tma-cloud-postgres'
[INFO]  Database   : tma_cloud_storage
[INFO]  Collecting table statistics …
[INFO]  Running pg_dump (custom format, serializable snapshot) …
[INFO]  Verifying backup integrity (pg_restore --list) …
[OK]    Backup contains 145 TOC entries.
[INFO]  Computing SHA-256 checksum …

── Backup Complete ──
[OK]    File       : backups/tma_cloud_storage_20260115T020000Z.dump
[OK]    Size       : 12M
[OK]    Duration   : 4s
[OK]    TOC entries: 145
[OK]    SHA-256    : a1b2c3d4e5f6a7b8c9d0e1f2a3b4c5d6e7f8a9b0c1d2e3f4a5b6c7d8e9f0a1b2

Metadata FileCopy Anchor Link

Each backup has a .meta file with:

timestamp=20260115T020000Z
database=tma_cloud_storage
user=postgres
mode=docker
format=custom
compression=--compress=6
toc_entries=145
file_size=12M
duration_seconds=4
sha256=a1b2c3d4e5f6a7b8c9d0e1f2a3b4c5d6e7f8a9b0c1d2e3f4a5b6c7d8e9f0a1b2

# Table row counts at backup time
public.users=5
public.files=218
public.audit_log=1340
public.job_queue=64

Database RestoreCopy Anchor Link

Restore from BackupCopy Anchor Link

./db-backup-restore.sh restore backups/tma_cloud_storage_20260115T020000Z.dump

The restore process:

1. Checks the backup file before touching the database: its SHA-256 checksum, and that pg_restore can read it. A file it cannot read is refused with a message.
2. Asks you to confirm by typing the database name.
3. Stops the app and worker services when it runs against the Compose project, so nothing writes during the restore. With a host or DB_CONTAINER database, stop the API and the worker yourself first.
4. Restores into a new scratch database (tma_restore_<TIMESTAMP>) in --single-transaction mode. Your current database is not touched yet.
5. Only when that worked, renames your current database aside and renames the restored one into its place. It then drops the old one. If the rename fails, the old database is put back.
6. Runs ANALYZE to update query planner statistics.
7. Reports table and row counts so you can check the result.
8. Clears the Redis cache of the Compose project with FLUSHDB on the database in REDIS_URL, because cached rows from before the restore may no longer exist. Redis holds only cache entries and event messages. With a host or DB_CONTAINER database, run FLUSHDB yourself or wait 10 minutes for the entries to expire.
9. Starts the services it stopped. It does this even when the restore fails.

If the restore fails at any step, the script says so and your database keeps its data. It also drops the scratch database. While it runs, the server needs enough free disk space for two copies of the database.

The restored data opens only with the encryption key file from the time of the backup or a later one, since rotation keeps older key versions.

Verify a BackupCopy Anchor Link

./db-backup-restore.sh verify backups/tma_cloud_storage_20260115T020000Z.dump

Checks the SHA-256 checksum against the .meta file and inspects the dump TOC without restoring.

List BackupsCopy Anchor Link

./db-backup-restore.sh list

Lists all .dump files in the backups/ directory with file size and date.

ConfigurationCopy Anchor Link

The script reads these keys from .env in its directory (or the repository root for scripts/db-backup-restore.sh, or TMA_DIR). It reads them as plain values and never runs .env as a shell script. Variables already set in the environment take precedence. The password reaches pg_dump and docker exec through the environment, so it does not appear in the process list.

The script splits DATABASE_URL into user, password, host, port, and database, and percent-decodes them. With the host's client tools, these URL parameters are passed on as libpq environment variables: sslmode, sslrootcert, sslcert, sslkey, sslcrl, sslnegotiation, connect_timeout, application_name, options, and target_session_attrs. Inside a container it connects over the local socket as the user in the URL.

File BackupsCopy Anchor Link

The backup script only covers the PostgreSQL database. It does not back up uploaded files or folders.

You must back up file storage separately:

S3-compatibleCopy Anchor Link

Use your storage vendor's replication or snapshot tools. The application stores object keys in the database; the bucket holds the blobs. Both must be backed up together to stay in sync.

Backup ScheduleCopy Anchor Link

Recommended cron schedule for automated backups:

# Daily database backup at 02:00 UTC
0 2 * * * cd /path/to/tma-cloud && ./db-backup-restore.sh backup >> /var/log/tma-backup.log 2>&1

Related TopicsCopy Anchor Link

• Database Schema - Table structure
• Environment Variables - Full variable reference
• CLI Commands - All available commands
• Storage Management - Storage overview