Back Up and Restore SQLite and PostgreSQL Databases

A backup you have never restored is only a candidate file

A successful backup job and a new archive every day do not prove that the data can come back. A file may have been copied during a write, the encryption key may be missing, versions may be incompatible, or the backup may sit on the same disk as the original. A database backup is proven when you can restore it in an isolated environment without touching production and pass application-level checks.

Define two targets first. RPO is how much recent data you can afford to lose; RTO is how long service may be unavailable. A personal content site may accept a day's loss and several hours to recover. An order system may not. Frequency and method should follow the targets.

Do not copy a live SQLite database casually

SQLite may appear to be one file, but WAL mode can also use -wal and -shm files. Copying only the main file while writes continue may not give you a consistent snapshot. Prefer SQLite's online backup API or its command-line .backup command:

sqlite3 /var/lib/myapp/app.db '.backup /var/backups/myapp/app-2026-08-14.db'
sqlite3 /var/backups/myapp/app-2026-08-14.db 'PRAGMA integrity_check;'

integrity_check should return ok. Do not place the backup directory under a public static path or allow the application user to overwrite all historical copies. If you choose an offline copy instead, explicitly stop the app, confirm writes have ended, copy the database and any relevant files, then restart and verify. Do not rely on a guess about whether it was idle.

Record PostgreSQL versions and roles with logical backups

pg_dump can export a consistent snapshot while the database remains in use. Its custom format lets pg_restore list objects and choose how to restore them:

pg_dump --format=custom --file=/var/backups/myapp/app-2026-08-14.dump myapp
pg_restore --list /var/backups/myapp/app-2026-08-14.dump | head

A database-level dump may not include global roles and privileges; preserve those separately if your recovery plan needs them. Record the PostgreSQL major version, extensions, and encoding. For large data sets or a short RPO, evaluate physical backup and WAL archiving rather than assuming a daily pg_dump will always suffice.

Include the assets needed to make data useful again

A website may also need user uploads, configuration templates, Caddy files, systemd units, Compose files, and a record of the deployed version. Decide explicitly whether secrets are backed up: without them, encrypted data may be unusable; with them, backup encryption and access control become even more important. Code can be rebuilt from a repository, but record the exact commit or image digest.

Divide assets into must restore, can rebuild, and can discard. Logs may need an investigation window; caches can usually be rebuilt; a database and user uploads need a consistent recovery point.

Keep an off-site copy, not just several local files

Use multiple copies on different media, with at least one in a separate location. A local backup is convenient for a quick restore but may disappear with a disk failure or account compromise. Object storage or another controlled machine can hold an off-site copy. Several copies under the same cloud account still share an account-level risk.

Set daily, weekly, and monthly retention instead of accumulating files until the disk fills. Before deleting old backups, list the candidates and confirm the latest successful copy. Monitor backup age, unexpected size changes, and upload failures.

Rehearse the restore away from production

Create a temporary directory or separate database; do not overwrite the live one. Record the start time, restore the backup, run an integrity check, and start a temporary application that points to the restored copy. Check article counts, recent records, login, uploaded files, and important queries—not just that the database process starts.

Record the actual recovery time, missing instructions, and credential problems. Clean up the temporary copy safely after the rehearsal. Rehearse at least quarterly, and again after a major database upgrade, backup-script change, or directory move.

What counts as done?

You can identify the latest successful backup, its integrity result, off-site location, retention period, person responsible for recovery, and time taken in the last rehearsal. Next, put that backup into a real deployment and rollback process.