GuidesBackups

Tutorial: back up your database every night

By the Banzai Labs teamUpdated on 2 min read

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.

Read next

All guides