Database Backup & Restore

Strategies for backing up ZapTicket's PostgreSQL database — full dumps, per-tenant exports, automated cron, and verification.

Overview

ZapTicket stores all data in a single PostgreSQL database with tenant isolation enforced at the application layer. Backups should be part of your production operations from day one. This page covers the tools and strategies for reliable database backup and restoration.

Full Database Backup

Use pg_dump to create a full backup of the entire database. This is the simplest approach and captures all tenants, schema, and data.

Full Backup (Custom Format)

# Custom format (compressed, supports parallel restore)
pg_dump -h localhost -U zapticket -d zapticket_prod \
  --format=custom \
  --file=backup_$(date +%Y%m%d_%H%M%S).dump

# Plain SQL format (human-readable, larger)
pg_dump -h localhost -U zapticket -d zapticket_prod \
  --format=plain \
  --file=backup_$(date +%Y%m%d_%H%M%S).sql

💡The custom format (--format=custom) is recommended for production backups. It's compressed, supports selective restore of individual tables, and enables parallel processing.

Restoring from Backup

Full Restore

# Restore from custom format
pg_restore -h localhost -U zapticket -d zapticket_prod \
  --clean --if-exists \
  backup_20240115_120000.dump

# Restore from plain SQL
psql -h localhost -U zapticket -d zapticket_prod \
  < backup_20240115_120000.sql

# Restore to a NEW database (for testing)
createdb -h localhost -U zapticket zapticket_restore_test
pg_restore -h localhost -U zapticket -d zapticket_restore_test \
  backup_20240115_120000.dump

⚠️The --clean flag drops existing objects before restoring. Only use this when you want a full replacement. For restoring to a fresh database, omit --clean.

Per-Tenant Export

Sometimes you need to export data for a single tenant (for data portability, debugging, or compliance). Since all tenant-scoped tables have a TenantId column, you can filter exports:

Per-Tenant Export Script

#!/bin/bash
# export-tenant.sh <tenant_id> <output_file>

TENANT_ID=$1
OUTPUT=$2
DB_HOST="localhost"
DB_USER="zapticket"
DB_NAME="zapticket_prod"

# Export tenant data as INSERT statements
psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "\copy (
  SELECT * FROM "Tenants" WHERE "Id" = '$TENANT_ID'
) TO STDOUT WITH CSV HEADER" > $OUTPUT

psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "\copy (
  SELECT * FROM "Agents" WHERE "TenantId" = '$TENANT_ID'
) TO STDOUT WITH CSV HEADER" >> $OUTPUT

psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "\copy (
  SELECT * FROM "Conversations" WHERE "TenantId" = '$TENANT_ID'
) TO STDOUT WITH CSV HEADER" >> $OUTPUT

psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "\copy (
  SELECT * FROM "Messages" WHERE "TenantId" = '$TENANT_ID'
) TO STDOUT WITH CSV HEADER" >> $OUTPUT

psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "\copy (
  SELECT * FROM "Tickets" WHERE "TenantId" = '$TENANT_ID'
) TO STDOUT WITH CSV HEADER" >> $OUTPUT

echo "Exported tenant $TENANT_ID to $OUTPUT"

Usage

chmod +x export-tenant.sh
./export-tenant.sh ws_abc123 tenant_export.csv

Automated Cron Backup

Set up a cron job to run daily backups with retention:

backup-cron.sh

#!/bin/bash
# /opt/zapticket/backup-cron.sh
# Run via cron: 0 3 * * * /opt/zapticket/backup-cron.sh

set -e

BACKUP_DIR="/opt/zapticket/backups"
DB_HOST="localhost"
DB_USER="zapticket"
DB_NAME="zapticket_prod"
RETENTION_DAYS=30

# Create backup directory
mkdir -p $BACKUP_DIR

# Create timestamped backup
FILENAME="zapticket_$(date +%Y%m%d_%H%M%S).dump"
pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME \
  --format=custom \
  --file="$BACKUP_DIR/$FILENAME"

# Verify backup is not empty
if [ ! -s "$BACKUP_DIR/$FILENAME" ]; then
  echo "ERROR: Backup file is empty!"
  exit 1
fi

# Log success
echo "$(date): Backup created: $FILENAME ($(du -h "$BACKUP_DIR/$FILENAME" | cut -f1))"

# Remove backups older than retention period
find $BACKUP_DIR -name "zapticket_*.dump" -mtime +$RETENTION_DAYS -delete

echo "$(date): Cleaned up backups older than $RETENTION_DAYS days"

Cron Entry

# Run backup at 3 AM daily
0 3 * * * /opt/zapticket/backup-cron.sh >> /var/log/zapticket-backup.log 2>&1

Verification

A backup you've never tested is not a real backup. Regularly verify your backups can be restored:

Backup Verification Script

#!/bin/bash
# verify-backup.sh <backup_file>

BACKUP_FILE=$1
TEST_DB="zapticket_verify_$(date +%s)"
DB_HOST="localhost"
DB_USER="zapticket"

echo "Creating test database: $TEST_DB"
createdb -h $DB_HOST -U $DB_USER $TEST_DB

echo "Restoring backup..."
pg_restore -h $DB_HOST -U $DB_USER -d $TEST_DB $BACKUP_FILE

echo "Verifying data..."
TENANT_COUNT=$(psql -h $DB_HOST -U $DB_USER -d $TEST_DB -t -c "SELECT COUNT(*) FROM "Tenants"")
CONV_COUNT=$(psql -h $DB_HOST -U $DB_USER -d $TEST_DB -t -c "SELECT COUNT(*) FROM "Conversations"")
MSG_COUNT=$(psql -h $DB_HOST -U $DB_USER -d $TEST_DB -t -c "SELECT COUNT(*) FROM "Messages"")

echo "Tenants: $TENANT_COUNT | Conversations: $CONV_COUNT | Messages: $MSG_COUNT"

echo "Cleaning up test database..."
dropdb -h $DB_HOST -U $DB_USER $TEST_DB

echo "Verification complete!"

🚨Run verification on a schedule (at least monthly). A backup strategy without verification gives false confidence — you may discover corruption only when you need to restore.

Best Practices

Frequency — Daily full backups minimum. Hourly for high-traffic production.
Retention — Keep 30 days of daily backups, 12 months of monthly backups.
Off-site storage — Copy backups to a different server or cloud storage (S3, R2, etc.).
Encryption — Encrypt backup files at rest if they contain sensitive data.
Monitoring — Alert if a daily backup doesn't appear or is suspiciously small.
Documentation — Document the restore procedure and test it with the team.