test

testing

🏗 Assumptions

Your docker-compose.yml looks something like:

 

services:
db:
image: postgres:15
container_name: my_postgres

Your database service name = db

If yours is different, adjust accordingly.

1️⃣ Create Backup Directory (On VPS Host)

sudo mkdir -p /var/backups/postgres
sudo chmod 700 /var/backups/postgres

2️⃣ Create Production Backup Script

sudo nano /usr/local/bin/pg_backup.sh

Paste this:

 

#!/bin/bash
 

# ===== CONFIG =====
COMPOSE_FILE="/path/to/your/docker-compose.yml"
SERVICE_NAME="db"
DB_NAME="your_db_name"
DB_USER="your_db_user"
BACKUP_DIR="/var/backups/postgres"
RETENTION_DAYS=14
DATE=$(date +"%Y-%m-%d_%H-%M-%S")

# ===== BACKUP =====
mkdir -p $BACKUP_DIR

docker compose -f $COMPOSE_FILE exec -T $SERVICE_NAME \
pg_dump -U $DB_USER -F c -b $DB_NAME \
> $BACKUP_DIR/db_$DATE.dump

# ===== CHECK SUCCESS =====
if [ $? -eq 0 ]; then
echo "Backup successful: db_$DATE.dump"
else
echo "Backup FAILED!"
exit 1
fi

# ===== DELETE OLD BACKUPS =====
find $BACKUP_DIR -type f -name "*.dump" -mtime +$RETENTION_DAYS -delete

Save and exit.

Make executable:

 

sudo chmod +x /usr/local/bin/pg_backup.sh

3️⃣ Add Daily Cron Job

 

sudo crontab -e

Add:

 

0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

Now backups run daily at 2 AM.

🔐 If Your DB Uses Password Auth

Add password inline (safe inside VPS):

 

docker compose exec -T \
-e PGPASSWORD=your_password \
db pg_dump ...

Or better:

  • Use .env file

  • Or rely on internal docker network trust

🔄 How to Restore

To restore:

 

docker compose exec -T db \
pg_restore -U your_db_user -d your_db_name --clean --if-exists \
< /var/backups/postgres/db_file.dump

🚀 Strongly Recommended Production Improvements

1️⃣ Compress More (Optional)

 

pg_dump -U ... | gzip > backup.sql.gz

2️⃣ Offsite Backup (Very Important)

If your VPS dies, local backups die with it.

Use:

  • AWS S3

  • Backblaze

  • rsync to another server

3️⃣ Monitor Failures

You can add:

 

|| curl -X POST https://your-monitoring-webhook