GuidesBackups
Tutorial: back up your database every night

1. Make a backup by hand
With PostgreSQL:
pg_dump -Fc my_db > /var/backups/my_db-$(date +%F).dump
With SQLite:
sqlite3 /var/lib/app/base.db ".backup '/var/backups/base-$(date +%F).db'"
2. Turn it into a script
Save this as /usr/local/bin/backup.sh, then make it executable with chmod +x:
#!/bin/bash
set -euo pipefail
pg_dump -Fc my_db > /var/backups/my_db-$(date +%F).dump
find /var/backups -name 'my_db-*.dump' -mtime +14 -delete
The last line deletes files older than fourteen days.
3. Run it every night
Type crontab -e and add:
0 3 * * * /usr/local/bin/backup.sh
The script runs at 3 a.m. every day.
4. Copy it off the server
rsync -a /var/backups/ user@other-server:/backups/
Add this line at the end of the script.
5. Test the restore
createdb restore_test
pg_restore -d restore_test /var/backups/my_db-2026-11-11.dump
Open the test database and check the data is there. Repeat this test regularly.
The full method is in Hosting on a VPS.
Frequently asked questions
- How often should I back up?
- As often as what you accept to lose. One backup a night means losing one day at most.
- Why copy the backup elsewhere?
- Because a backup stored on the server disappears with it.
- How do I know the backup is good?
- By restoring it into a test database. It is the only check that counts.