Skip to main content

​
How backups work

The agent uses pg_dump piped through gzip to create compressed SQL dumps: 
pg_dump → gzip → /var/backups/postgresql/{dbname}/YYYYMMDD_HHMMSS.sql.gz
Key details:
  • Backups are triggered via the API or dashboard (async, returns immediately)
  • Uses asyncio.create_subprocess_exec (not shell) to prevent command injection
  • Only PGPASSWORD and PATH are passed to the subprocess environment
  • If pg_dump fails, the incomplete file is deleted

​
Storage

Backups are stored locally on the VPS: 
/var/backups/postgresql/
├── my_app/
│   ├── 20260319_100000.sql.gz
│   └── 20260319_120000.sql.gz
└── other_db/
    └── 20260318_060000.sql.gz

​
Retention

The agent has a delete_old_backups(retention_days=7) method, but it’s not yet automated. Backups accumulate until manually cleaned. To clean up manually: 
find /var/backups/postgresql -name "*.sql.gz" -mtime +7 -delete
Automating retention via cron or systemd timer is on the roadmap. S3 backup storage (for durability and download links) is also planned.

​
Restoring

To restore a backup: 
# Create the database if it doesn't exist
sudo -u postgres createdb mydb

# Restore
gunzip -c /var/backups/postgresql/mydb/20260319_120000.sql.gz | sudo -u postgres psql mydb
Restoring overwrites existing data in the target database. Consider restoring to a temporary database first to verify the backup.