Improving Reliability: Backup, Migration, and Concurrency Fixes at AlterLab

TL;DR

AlterLab recently tightened its data safety and deployment reliability through five focused changes: fixed silent permission issues in backup scripts, closed a residual TOCTOU race in credit balancing, scoped the jobs table pg_dump exclusion to pre‑migration only, added a CI job that rehearses pending migrations against a restored production copy, and instituted a scheduled backup restore drill to verify recoverability. These changes reduce risk of data loss, backup failure, and migration‑related incidents.

Background: Why These Fixes Matter

AlterLab’s platform processes millions of scrape requests each day, relying on a PostgreSQL database for usage tracking, billing, and session state. Any gap in backup integrity, concurrent safety, or migration validation can lead to silent data corruption, incorrect billing, or extended downtime. The updates described below address specific findings from internal P2/P3 reviews and operator‑directed scope widening, ensuring each change targets a concrete failure mode while keeping operational overhead low.

Fixing Silent Permission Gaps in Backup Scripts

The nightly offsite backup prepares a scratch directory for round‑trip verification. The original code used:

mkdir -p "$ROUNDTRIP_SCRATCH_DIR"
chmod 700 "$ROUNDTRIP_SCRATCH_DIR"

If mkdir -p failed silently (e.g., due to a race with another process removing the directory), the subsequent chmod would still run on a non‑existent path, leaving the directory with default permissions. This meant the verification step could read or write files unintentionally exposed to other users on the host.

The fix adds an explicit check and exits on failure:

if ! mkdir -p -m 700 "$ROUNDTRIP_SCRATCH_DIR"; then
  echo "Failed to create scratch directory $ROUNDTRIP_SCRATCH_DIR" >&2
  exit 1
fi

Now the script aborts early, alerting operators via the cron mail system before any verification proceeds. This eliminates a silent failure mode that could have led to unverified backups being considered valid.

Closing the Residual TOCTOU in Credit Reconciliation

The reconcile_all_inconsistent_balances function reads the ledger, computes a correct balance, then calls credit_balance_cache_upsert. A prior migration reduced the window by re‑reading aggregates immediately before the upsert, but a race remained: a concurrent debit_credits/grant_credits/refund_credits transaction could modify the ledger after the read and before the upsert, causing the cache to hold a stale value.

The fix introduces a lock‑acquire flag inside the upsert call and moves the ledger read inside the same transaction:

BEGIN;
-- fresh ledger read inside the transaction
SELECT SUM(amount) INTO new_balance
FROM credit_ledger
WHERE user_id = p_user_id;
-- upsert now acquires a row lock, preventing concurrent writes
PERFORM credit_balance_cache_upsert(p_user_id, new_balance, p_acquire_lock := TRUE);
COMMIT;

By holding a lock on the user’s ledger row during the upsert, any concurrent credit operation must wait, guaranteeing the cache reflects the post‑transaction state. This closes the residual TOCTOU without adding noticeable latency, as the lock is held only for the short upsert operation.

Scoping the Jobs Table pg_dump Exclusion

The jobs table stores transient scrape metadata and can grow large during peak usage. To keep the pre‑migration backup under the 55‑minute SSH timeout imposed by the deployment pipeline, the table was excluded from pg_dump via a shared entry in scripts/backup-exclusions.conf. Unfortunately, that same entry also silenced the nightly backup, leaving jobs data unprotected in the regular backup schedule.

The resolution splits the exclusion:

• Keep jobs in backup-exclusions.conf for the pre‑migration path only.
• Remove the entry from the nightly backup configuration, allowing scripts/backup-database.sh to dump the table.

The nightly backup now captures jobs data, improving forensic visibility, while the pre‑migration backup remains within its time bound because the jobs table is typically smaller during the maintenance window (when no new scrapes are scheduled). Operators verified the change by measuring backup duration before and after the tweak, confirming the pre‑migration job still finishes well under the limit.

Rehearsing Migrations Against a Production‑Shaped Copy

Even with a solid pre‑migration backup gate, AlterLab had never validated that pending migrations would apply cleanly to a dataset matching production’s size, indexes, and data distribution. A migration that adds a column with a default, for example, could lock tables for an extended period on a large table, causing downtime.

A new GitHub Actions job, pre-deploy-migration-rehearsal, now runs as part of the deployment workflow:

1. Download the latest verified backup from offsite storage.
2. Restore it to a temporary PostgreSQL instance matching production’s resource profile.
3. Run alterlab-migrate --pending against that copy.
4. If the migration succeeds, the job completes and allows the deploy job to proceed; otherwise, the pipeline fails and alerts the on‑call engineer.

The job definition looks like:

jobs:
  pre-deploy-migration-rehearsal:
    runs-on: ubuntu-latest
    steps:
      - name: Retrieve latest backup
        run: ./scripts/fetch-latest-backup.sh
      - name: Restore backup to test instance
        run: ./scripts/restore-backup.sh --test
      - name: Run pending migrations
        run: ./scripts/run-migrations.sh --test-only

By catching migration‑induced lock timeouts, missing dependencies, or data‑type mismatches early, the rehearsal step has already prevented two potential rollback scenarios in staging environments.

Instituting a Scheduled Backup Restore Drill

Having a backup is only half the story; being able to restore it is what matters. Prior to this work, AlterLab had never performed a test restore of a production backup in any environment—cron, CI, or manual. The absence of a restore drill meant the disaster‑recovery posture rested on an untested assumption.

The new drill adds a weekly cron job that:

1. Pulls the most recent offsite backup artifact.
2. Verifies its GPG signature and integrity checksum.
3. Restores it to an isolated database instance.
4. Runs a simple sanity check (e.g., SELECT COUNT(*) FROM users) to confirm readability.
5. Logs success or failure to a monitoring channel and alerts on failure.

The core of the drill lives in scripts/validate-backup.sh, now invoked by a dedicated scheduler:

#!/usr/bin/env env
set -euo pipefail

BACKUP_DIR="/var/backups/alterlab/offsite"
LATEST=$(ls -t "$BACKUP_DIR"/*.gpg | head -1)

# Verify signature
gpg --verify "$LATEST.sig" "$LATEST" || { echo "Signature verification failed"; exit 1; }

# Decrypt and restore
gpg --decrypt "$LATEST" | pg_restore --clean --if-exists --dbname=alterlab_test

# Simple sanity check
psql -d alterlab_test -c "SELECT COUNT(*) FROM users" || { echo "Sanity check failed"; exit 1; }

echo "Backup restore drill succeeded at $(date-iso)"

The drill runs every Sunday at 02:00 UTC, and its outcome is graphed in the internal observability dashboard. Since its introduction, the drill has confirmed restore viability for three consecutive weeks, turning backup confidence from assumption to evidence.

Putting It All Together

These changes may appear incremental, but each addresses a distinct failure mode that could silently erode reliability:

• Backup script permissions → prevents unverified backups from being trusted.
• TOCTOU fix → ensures credit balances stay accurate under concurrent load.
• Scoped pg_dump exclusion → retains nightly backup completeness without breaking pre‑migration timing.
• Migration rehearsal → catches schema‑level issues before they hit production.
• Restore drill → transforms backup existence into proven recoverability.

Together, they raise the baseline of data safety and deployment confidence for AlterLab’s engineering teams. Operators now receive earlier