Nothing backs up the production database #147

New Issue

2026-08-23T16:38:59-05:00

bermudalamb commented

2026-08-23 16:38:59 -05:00

There is no backup of the production database anywhere in this repository: no script, no scheduled job, no documented procedure. The only copy of every customer account, order, checkout and taxonomy row is the live Postgres data directory at /volume1/configs/redefined-designs/postgres.

The repository is thorough about a great deal else — migrations that refuse to serve against a mismatched schema, a config validator that refuses to boot on a bad environment, a drift guard on the compose files. The one thing that would let any of those failures be recovered from is absent.

Why it is worth doing now rather than later

Three things make this more pressing than a general "backups are good".

Every item is one of a kind. An items table restored from nothing is not a catalogue that can be re-entered from an invoice. The photographs in /volume1/configs/redefined-designs/uploads are equally unrecoverable and equally unbacked.

The riskiest operation on this stack is about to be performed by hand. Moving production from a Portainer web-editor stack to a git-repository stack (#118, #146) means deleting and recreating the stack. The Postgres data is a bind mount and should survive, but "should" is carrying the entire customer database, and there is currently nothing to fall back to if it does not.

Restores are the half that gets skipped. A backup nobody has restored from is a file of unknown validity. pg_dump output that was silently truncated, or taken while a migration was mid-flight, looks exactly like a good one until the day it matters.

Shape

A pg_dump from the database container on a schedule, written somewhere that is not the same volume as the data — a Synology task is the obvious host for it, but the command and the retention policy belong in this repository so they are reviewable and so the next environment inherits them.

Worth deciding as part of it:

Whether uploads are included. The database alone restores a catalogue whose every image is a broken link. They are a separate concern with a separate size profile, but restoring one without the other is not a restore.
Retention, and where. A backup on the same NAS survives a bad migration and not a failed disk. Somewhere off the box is a materially different guarantee.
Whether the dump is consistent. pg_dump takes a consistent snapshot, so a dump running while the app is serving is fine, but one running while migrate.js is applying DDL is worth thinking about.
How failure is noticed. A cron job that silently stopped a month ago is the common way this goes wrong, and it is indistinguishable from a working one until a restore is attempted.

Acceptance criteria

A documented, scheduled backup of the production database, with the command and retention policy in this repository
A documented restore procedure
The restore procedure has been performed at least once, against a throwaway database, and the result verified
Backup failure is visible without someone going to look
An explicit decision recorded about whether uploads are covered, and by what

There is no backup of the production database anywhere in this repository: no script, no scheduled job, no documented procedure. The only copy of every customer account, order, checkout and taxonomy row is the live Postgres data directory at `/volume1/configs/redefined-designs/postgres`. The repository is thorough about a great deal else — migrations that refuse to serve against a mismatched schema, a config validator that refuses to boot on a bad environment, a drift guard on the compose files. The one thing that would let any of those failures be recovered from is absent. ## Why it is worth doing now rather than later Three things make this more pressing than a general "backups are good". **Every item is one of a kind.** An `items` table restored from nothing is not a catalogue that can be re-entered from an invoice. The photographs in `/volume1/configs/redefined-designs/uploads` are equally unrecoverable and equally unbacked. **The riskiest operation on this stack is about to be performed by hand.** Moving production from a Portainer web-editor stack to a git-repository stack (#118, #146) means deleting and recreating the stack. The Postgres data is a bind mount and should survive, but "should" is carrying the entire customer database, and there is currently nothing to fall back to if it does not. **Restores are the half that gets skipped.** A backup nobody has restored from is a file of unknown validity. `pg_dump` output that was silently truncated, or taken while a migration was mid-flight, looks exactly like a good one until the day it matters. ## Shape A `pg_dump` from the database container on a schedule, written somewhere that is not the same volume as the data — a Synology task is the obvious host for it, but the command and the retention policy belong in this repository so they are reviewable and so the next environment inherits them. Worth deciding as part of it: - **Whether uploads are included.** The database alone restores a catalogue whose every image is a broken link. They are a separate concern with a separate size profile, but restoring one without the other is not a restore. - **Retention, and where.** A backup on the same NAS survives a bad migration and not a failed disk. Somewhere off the box is a materially different guarantee. - **Whether the dump is consistent.** `pg_dump` takes a consistent snapshot, so a dump running while the app is serving is fine, but one running while `migrate.js` is applying DDL is worth thinking about. - **How failure is noticed.** A cron job that silently stopped a month ago is the common way this goes wrong, and it is indistinguishable from a working one until a restore is attempted. ## Acceptance criteria - A documented, scheduled backup of the production database, with the command and retention policy in this repository - A documented restore procedure - The restore procedure has been performed at least once, against a throwaway database, and the result verified - Backup failure is visible without someone going to look - An explicit decision recorded about whether uploads are covered, and by what

bermudalamb commented

2026-08-23 16:41:26 -05:00

Correction to the issue body: "no documented procedure" is wrong, and the issue is narrower and more specific than I first described.

README's production deployment section already documents a backup, as step 1 of every deploy, before anything else happens:

sudo docker exec -t redefined-designs-db-syn pg_dump -U redefined -d redefined \
  > /volume1/configs/redefined-designs/backup-$(date +%Y%m%d-%H%M).sql
ls -lh /volume1/configs/redefined-designs/backup-*.sql   # size must be plausible

It even checks the file size afterwards, which is the failure mode a naive pg_dump > redirect most often hides. So the command exists, it is written down, and it runs at the moment of highest risk.

What is actually missing is narrower:

It only happens when someone deploys. Between deploys there is nothing. A quiet week means the newest copy of the customer database is a week old, and the gap is not bounded by anything.

It is manual. It runs when a person remembers, as part of a checklist, on a machine they are already logged into. The step most likely to be skipped is the one before the interesting part.

It writes to the same volume as the data it protects. /volume1/configs/redefined-designs/backup-*.sql sits beside /volume1/configs/redefined-designs/postgres. That survives a bad migration, a bad deploy and a dropped table. It does not survive the disk, and it does not survive anything that removes the directory.

Nothing prunes it. backup-$(date).sql accumulates one uncompressed dump per deploy, forever, on the volume the application also writes uploads to.

Uploads are still not covered at all. That half of the original issue stands unchanged — a restored database whose every image is a broken link is not a restored shop.

No restore has been performed. Also unchanged, and still the half that decides whether any of the above is worth anything.

So the acceptance criteria should read as scheduled, offsite-or-at-least-off-volume, pruned, and proven by a restore — rather than "a documented backup", which already exists and works. The deploy-time dump is a good thing to keep; it is a snapshot before a risky operation, which is a different job from a backup regime and should not be conflated with one.

Correction to the issue body: "no documented procedure" is wrong, and the issue is narrower and more specific than I first described. README's production deployment section already documents a backup, as step 1 of every deploy, before anything else happens: ```bash sudo docker exec -t redefined-designs-db-syn pg_dump -U redefined -d redefined \ > /volume1/configs/redefined-designs/backup-$(date +%Y%m%d-%H%M).sql ls -lh /volume1/configs/redefined-designs/backup-*.sql # size must be plausible ``` It even checks the file size afterwards, which is the failure mode a naive `pg_dump >` redirect most often hides. So the command exists, it is written down, and it runs at the moment of highest risk. What is actually missing is narrower: **It only happens when someone deploys.** Between deploys there is nothing. A quiet week means the newest copy of the customer database is a week old, and the gap is not bounded by anything. **It is manual.** It runs when a person remembers, as part of a checklist, on a machine they are already logged into. The step most likely to be skipped is the one before the interesting part. **It writes to the same volume as the data it protects.** `/volume1/configs/redefined-designs/backup-*.sql` sits beside `/volume1/configs/redefined-designs/postgres`. That survives a bad migration, a bad deploy and a dropped table. It does not survive the disk, and it does not survive anything that removes the directory. **Nothing prunes it.** `backup-$(date).sql` accumulates one uncompressed dump per deploy, forever, on the volume the application also writes uploads to. **Uploads are still not covered at all.** That half of the original issue stands unchanged — a restored database whose every image is a broken link is not a restored shop. **No restore has been performed.** Also unchanged, and still the half that decides whether any of the above is worth anything. So the acceptance criteria should read as *scheduled, offsite-or-at-least-off-volume, pruned, and proven by a restore* — rather than "a documented backup", which already exists and works. The deploy-time dump is a good thing to keep; it is a snapshot before a risky operation, which is a different job from a backup regime and should not be conflated with one.

bermudalamb commented

2026-08-23 17:11:31 -05:00

Design

Decisions

Q: What should schedule and manage the dumps — a purpose-built image, a shell loop in postgres:16, or Synology Task Scheduler?

A: A purpose-built image, as a service in docker-compose.prod.yml. A command: loop looks like fewer moving parts and is not: it means hand-rolling retention, compression and pruning, and a loop's clock resets on every container restart, so "daily" silently becomes "daily-ish, from whenever the NAS last rebooted". Synology Task Scheduler has the best notification story of the three, but it puts the command in the NAS UI where nothing can review or version it — exactly the property #118 has just finished removing from the compose files, and re-creating it for backups would be a strange trade.

Q: How should a backup that has silently stopped working become visible?

A: A container healthcheck on staleness. It needs no credentials, no network and no second service, and it appears in Portainer's stack view beside the app rather than somewhere separate to remember to look. Email would actively reach out, but the backup container has no mailer, and handing SMTP secrets to a second service to tell you about the first is a lot of surface for one signal.

Q: Should uploads be backed up too?

A: Yes. The photographs of one-of-a-kind items are as unrecoverable as the rows describing them, and a database restored without them is a catalogue of broken links.

Shape: two services, not one

Both sit in docker-compose.prod.yml. The database dumper depends_on the existing pg_isready healthcheck, so nothing tries to dump a server that is not accepting connections yet — the constraint that prompted this design.

Two services rather than one because they are different jobs on different cadences. The database is small, changes constantly, and wants a logical dump. Uploads are large, append-mostly, and want an archive. Forcing both through one tool serves one of them badly.

The consistency window this creates, and why it is acceptable here. Running separately means a restore pairs a database from one moment with images from another, allowing two mismatches. An image with no row is harmless — an orphaned file nobody references. A row with no image is a broken thumbnail on one recent item, and the original is recoverable, because an admin who added an item that recently still has the photograph. Neither is data loss. Paying for a synchronised snapshot to avoid a recoverable broken thumbnail is not worth the complexity.

The staleness healthcheck

Applied uniformly to both as a compose-level healthcheck: rather than relying on whatever each image happens to offer, so the two report the same way:

healthcheck:
  # Unhealthy when nothing has been written inside the window. A regime that
  # stopped a month ago is indistinguishable from a working one until a restore
  # is attempted, and this is the cheapest thing that tells them apart.
  test: ["CMD-SHELL", "find /backups -name '*.sql.gz' -mmin -1560 | grep -q ."]
  interval: 1h
  retries: 3

The window is the interval plus grace — 26 hours for a daily job — so a dump that runs slightly late is not reported as failed.

Retention

Daily for a week, weekly for a month, monthly for half a year, pruned automatically. The dumps are small enough that this costs little. The uploads archive is not, so it takes a longer cadence and a shorter tail.

Where the artifacts land, and the honest limit

A bind mount on the host, so they survive container deletion, stack deletion, and the git-stack conversion.

They do not survive the disk. /volume1/configs/redefined-designs/backups is on the same volume as /volume1/configs/redefined-designs/postgres, and calling that a backup overstates it: it protects against a bad migration, a dropped table and a bad deploy, and against nothing physical. Getting a copy off the volume is a Synology-side decision — Hyper Backup to another volume, an external disk, or offsite — that cannot be expressed in this repository. The compose services are the mechanism; the off-volume copy is the guarantee, and this issue is not finished until both exist.

What this deliberately does not cover

A backup service inside the stack runs while the stack runs. It cannot protect the stack's own teardown — deleting the Portainer stack deletes the backup container with everything else. The manual pg_dump in README's deploy steps therefore stays exactly where it is. A routine regime and a snapshot before a risky operation are different jobs, and neither replaces the other.

Restore

The half that decides whether any of the above is worth anything. A documented procedure, and a restore actually performed against a throwaway database with the result verified — not "the file is a plausible size", but a schema matching what the app expects and a row count matching what was there.

Revised acceptance criteria

A scheduled, compressed pg_dump service in docker-compose.prod.yml, with automatic retention
An uploads archive service alongside it, on its own cadence
Both report unhealthy when their newest artifact is older than their interval plus grace
Artifacts land on a host bind mount that survives stack deletion
An off-volume copy exists, by whatever Synology-side mechanism is chosen, and is recorded here
A documented restore procedure, performed at least once against a throwaway database and verified
README's deploy-time dump stays, with a note saying why the scheduled regime does not replace it

## Design ### Decisions **Q: What should schedule and manage the dumps — a purpose-built image, a shell loop in `postgres:16`, or Synology Task Scheduler?** A: A purpose-built image, as a service in `docker-compose.prod.yml`. A `command:` loop looks like fewer moving parts and is not: it means hand-rolling retention, compression and pruning, and a loop's clock resets on every container restart, so "daily" silently becomes "daily-ish, from whenever the NAS last rebooted". Synology Task Scheduler has the best notification story of the three, but it puts the command in the NAS UI where nothing can review or version it — exactly the property #118 has just finished removing from the compose files, and re-creating it for backups would be a strange trade. **Q: How should a backup that has silently stopped working become visible?** A: A container healthcheck on staleness. It needs no credentials, no network and no second service, and it appears in Portainer's stack view beside the app rather than somewhere separate to remember to look. Email would actively reach out, but the backup container has no mailer, and handing SMTP secrets to a second service to tell you about the first is a lot of surface for one signal. **Q: Should uploads be backed up too?** A: Yes. The photographs of one-of-a-kind items are as unrecoverable as the rows describing them, and a database restored without them is a catalogue of broken links. ### Shape: two services, not one Both sit in `docker-compose.prod.yml`. The database dumper `depends_on` the existing `pg_isready` healthcheck, so nothing tries to dump a server that is not accepting connections yet — the constraint that prompted this design. Two services rather than one because they are different jobs on different cadences. The database is small, changes constantly, and wants a logical dump. Uploads are large, append-mostly, and want an archive. Forcing both through one tool serves one of them badly. **The consistency window this creates, and why it is acceptable here.** Running separately means a restore pairs a database from one moment with images from another, allowing two mismatches. An image with no row is harmless — an orphaned file nobody references. A row with no image is a broken thumbnail on one recent item, and the original is recoverable, because an admin who added an item that recently still has the photograph. Neither is data loss. Paying for a synchronised snapshot to avoid a recoverable broken thumbnail is not worth the complexity. ### The staleness healthcheck Applied uniformly to both as a compose-level `healthcheck:` rather than relying on whatever each image happens to offer, so the two report the same way: ```yaml healthcheck: # Unhealthy when nothing has been written inside the window. A regime that # stopped a month ago is indistinguishable from a working one until a restore # is attempted, and this is the cheapest thing that tells them apart. test: ["CMD-SHELL", "find /backups -name '*.sql.gz' -mmin -1560 | grep -q ."] interval: 1h retries: 3 ``` The window is the interval plus grace — 26 hours for a daily job — so a dump that runs slightly late is not reported as failed. ### Retention Daily for a week, weekly for a month, monthly for half a year, pruned automatically. The dumps are small enough that this costs little. The uploads archive is not, so it takes a longer cadence and a shorter tail. ### Where the artifacts land, and the honest limit A bind mount on the host, so they survive container deletion, stack deletion, and the git-stack conversion. They do **not** survive the disk. `/volume1/configs/redefined-designs/backups` is on the same volume as `/volume1/configs/redefined-designs/postgres`, and calling that a backup overstates it: it protects against a bad migration, a dropped table and a bad deploy, and against nothing physical. Getting a copy off the volume is a Synology-side decision — Hyper Backup to another volume, an external disk, or offsite — that cannot be expressed in this repository. **The compose services are the mechanism; the off-volume copy is the guarantee, and this issue is not finished until both exist.** ### What this deliberately does not cover A backup service inside the stack runs while the stack runs. It cannot protect the stack's own teardown — deleting the Portainer stack deletes the backup container with everything else. The manual `pg_dump` in README's deploy steps therefore stays exactly where it is. A routine regime and a snapshot before a risky operation are different jobs, and neither replaces the other. ### Restore The half that decides whether any of the above is worth anything. A documented procedure, and a restore actually performed against a throwaway database with the result verified — not "the file is a plausible size", but a schema matching what the app expects and a row count matching what was there. ### Revised acceptance criteria - A scheduled, compressed `pg_dump` service in `docker-compose.prod.yml`, with automatic retention - An uploads archive service alongside it, on its own cadence - Both report unhealthy when their newest artifact is older than their interval plus grace - Artifacts land on a host bind mount that survives stack deletion - An off-volume copy exists, by whatever Synology-side mechanism is chosen, and is recorded here - A documented restore procedure, performed at least once against a throwaway database and verified - README's deploy-time dump stays, with a note saying why the scheduled regime does not replace it

bermudalamb self-assigned this 2026-08-24 09:08:33 -05:00

bermudalamb added this to the Code Quality and Hardening 2 project 2026-08-24 09:08:38 -05:00

bermudalamb referenced this issue from a commit

2026-08-24 11:29:29 -05:00

feat(ops): schedule database and uploads backups, and document the restore (#147)

bermudalamb referenced this issue

2026-08-24 11:30:07 -05:00

feat(ops): schedule database and uploads backups, and document the restore (#147) #156

bermudalamb added reference feature/147-scheduled-backups

2026-08-24 11:30:14 -05:00

bermudalamb moved this to Review in Code Quality and Hardening 2 on 2026-08-24 11:30:21 -05:00

bermudalamb referenced this issue from a commit