PostgreSQL Docker Backup Script

Note: Currently running on bnet006:/usr/local/bin/backup_postgres_db.sh

Overview

This script performs automated backups of PostgreSQL databases running in Docker containers. It's designed to create full backups of multiple Supabase database containers, compress them, and maintain logs of the backup process.

Features

• Creates full database backups using pg_dumpall
• Backs up multiple containers (staging, production, and killbill)
• Implements error handling with proper exit codes
• Compresses backups to save storage space
• Maintains a single consolidated log file for all backup operations
• Automatically cleans up old backup files
• Validates container existence before attempting backups

Installation

1. Copy the script to your desired location:

   cp supabase-envs.sh /path/to/file/
   

2. Make it executable:

   chmod +x /path/to/file/supabase-envs.sh
   

Configuration

The script uses the following configuration variables that can be modified:

• BACKUP_ROOT: Base directory for all backups
• LOG_DIR: Directory for storing log files
• Container names for each database environment:
  • STAGE_CONTAINER
  • PROD_CONTAINER
  • KILLBILL_CONTAINER

Usage

Run the script manually:

/path/to/file//supabase-envs.sh

Scheduling

The script is configured to run daily at 23:00 via cron. To set up the cron job:

crontab -e

Add the following line:

0 23 * * * /path/to/file/supabase-envs.sh

Logs

Logs are written to:

• /var/log/postgres_bkp/postgres_backup.log

Requirements

• Docker
• PostgreSQL containers with pg_dumpall capability
• supabase_admin user with appropriate permissions
• Sufficient disk space for backups

Restoring

To restore a backup, use the following command:

gunzip -c /path/to/backup.sql.gz | docker exec -i [container_name] psql -U supabase_admin --disable-triggers -d [database_name]

Typical commands for our three containers:

# Stage
gunzip -c /var/backups/postgres/supabase-stage-db/supabase_stage.sql.gz | docker exec -i supabase-stage-db psql -U supabase_admin --disable-triggers -d postgres

# Production
gunzip -c /var/backups/postgres/supabase-db/supabase_prod.sql.gz | docker exec -i supabase-db psql -U supabase_admin --disable-triggers -d postgres

# KillBill
gunzip -c /var/backups/postgres/killbill-supabase-db/killbill_supabase.sql.gz | docker exec -i killbill-supabase-db psql -U supabase_admin --disable-triggers -d postgres

The --disable-triggers flag is essential as it handles circular foreign key dependencies in the database during restoration. After restoration completes, all triggers will be automatically re-enabled - no manual intervention is required.

Troubleshooting

If backups are failing, check:

1. Container names match those in the script
2. The supabase_admin user exists and has appropriate permissions
3. Sufficient disk space is available
4. The Docker daemon is running

Development

Developed by b.net - service@bamsenet.de