How to Backup and Restore the Database

Local Database Backup

The backup-local-db.sh script creates a full PostgreSQL dump of the local database.

# Run from scripts directory
cd projects/bbu-rfid/resources/scripts
./backup-local-db.sh

# Or specify output file
./backup-local-db.sh ./my-backup.dump

Output: acsis-backup-YYYYMMDD-HHMMSS.dump (custom format, compressed)

Prerequisites:

• PostgreSQL client tools (pg_dump)
• Local Aspire app running (database available)

What's backed up:

• All schemas (prism, catalog, transport, bbu, etc.)
• All tables and data
• Views, functions, indexes

Note: The port in the script may need updating based on your Aspire configuration. Check Aspire dashboard for current PostgreSQL port.

Restore to Azure

The restore-to-azure.sh script restores a backup to Azure PostgreSQL.

# Set Azure credentials (or provide as arguments)
export AZURE_PG_HOST='psql-xxx.postgres.database.azure.com'
export AZURE_PG_USER='admin-username'
export AZURE_PG_PASSWORD='your-password'

# Run restore
./restore-to-azure.sh ./acsis-backup-20241210-120000.dump

What happens:

1. Tests Azure PostgreSQL connectivity
2. Creates acsis database if it doesn't exist
3. Prompts for confirmation (destructive operation)
4. Restores using pg_restore with --clean --if-exists

Important flags used:

• --no-owner - Don't try to set object ownership
• --no-privileges - Don't restore privileges
• --clean --if-exists - Drop objects before recreating

Manual Backup

# Get connection info from Aspire dashboard
# Port varies based on what Aspire assigns

pg_dump \
  -h localhost \
  -p PORT \
  -U postgres \
  -d acsis \
  -Fc \
  -f backup.dump

Manual Restore

PGPASSWORD='password' pg_restore \
  -h hostname \
  -p 5432 \
  -U username \
  -d acsis \
  --no-owner \
  --no-privileges \
  --clean \
  --if-exists \
  backup.dump