A backup is useful only if you can restore it into a working application. For a small web app, a reliable plan does not need a large operations team: it needs a clear recovery target, protected copies, a repeatable restore procedure, and occasional practice.
This guide covers self-managed PostgreSQL and SQLite. Managed database services may provide automated backups or point-in-time recovery, but the available retention, recovery window, permissions, and charges depend on the provider and plan. Check those details in your provider's current documentation instead of assuming that a feature is enabled.
Decide how much data and downtime you can accept
Write down two targets before choosing a backup schedule:
- Recovery point objective (RPO): how much recent data you could afford to lose. If your latest usable backup is from yesterday, changes since that backup may be lost.
- Recovery time objective (RTO): how long the app can be unavailable while you restore data, deploy compatible code, and verify the service.
These are planning targets, not guarantees. A restore drill is how you find out whether your actual process meets them.
Also list what the database backup does not include. User uploads stored in object storage, files on a server, encryption keys, environment configuration, and third-party service data may need their own recovery plan. See our guide to handling environment variables and secrets in web projects before putting database credentials or encryption keys in backup scripts.
Choose a backup method that matches your recovery target
| Database | Practical starting point | What it gives you | Main limitation |
|---|---|---|---|
| PostgreSQL | A regular pg_dump archive | A consistent logical snapshot of one database that you can restore with pg_restore | It is not a continuous history and does not include cluster-wide roles |
| PostgreSQL with a short recovery window | A base backup plus continuous write-ahead log (WAL) archiving, or a provider's point-in-time recovery | Recovery to a point between full backups when the required backup and log sequence is available | More storage, monitoring, and recovery setup; recovery is for the database cluster |
| SQLite | The Online Backup API, exposed by the sqlite3 shell's .backup command | A consistent snapshot of a live database | Each snapshot is a restore point; it is not a transaction-by-transaction recovery history |
PostgreSQL's pg_dump documentation describes logical dumps as snapshots taken when the dump begins. They do not block ordinary database reads and writes while the dump runs. A custom-format archive is convenient for selective or controlled restores. For recovery to a time between base backups, PostgreSQL requires a base backup and an uninterrupted sequence of archived WAL files; a pg_dump file alone cannot provide that point-in-time recovery.
SQLite's Online Backup API copies a snapshot while allowing other database users to continue working. If your app uses SQLite's write-ahead log mode, do not assume that copying only the main database file while the app is writing captures the latest committed data; use the backup API or stop writes and follow a tested file-level procedure. The SQLite WAL documentation explains why the database and its WAL file work together.
Set a schedule and keep independent copies
Use the RPO to choose how often to create restore points. If losing up to a day of updates is acceptable, a daily backup might fit. If even a short gap is unacceptable, more frequent logical dumps may still leave gaps; investigate PostgreSQL WAL archiving or your managed service's point-in-time recovery.
Retention should cover the time it might take to notice a problem. For a small, low-volume app, an example starting policy is to keep daily backups for two weeks and a monthly copy for six months. Adjust it for your data, storage budget, and legal or contractual requirements. A longer retention period also means more storage and a longer period during which sensitive data must be protected.
Keep at least one copy outside the database host or its main storage account. Encrypt backup files at rest and in transit, restrict who and what can read or delete them, and protect the encryption keys separately. A backup job that can write new copies should not automatically be able to erase every older copy. Treat backup files as production data: they can contain credentials, personal information, and records that users have deleted since the backup was made.
Monitor the whole path, not just whether a scheduler ran:
- Did the database export or snapshot complete?
- Did the copy reach the separate storage location?
- Is the file a plausible size, and is the storage within its retention and capacity limits?
- Can someone responsible for recovery access the backup without relying on the failed app server?
Send a failure alert somewhere independent of the app being backed up. A silent job failure can leave you with an old file that looks like a backup but misses recent changes.
Create and restore a PostgreSQL logical backup
For a small self-managed database, a custom-format pg_dump archive is a practical starting point:
pg_dump --format=custom --file=./backups/app-2026-10-27.dump appdb
The archive includes the database schema and data. The custom format is compressed by default and is restored with pg_restore, not by opening it as a plain SQL script. Use an appropriately authorized database account and a protected backup directory. Do not put a database password directly in a command, script, or repository.
To test a restore without overwriting the live database, create a separate empty test database and restore into it:
createdb app_restore_test
pg_restore --exit-on-error --dbname=app_restore_test ./backups/app-2026-10-27.dump
The pg_restore reference documents the archive formats and restore options. Roles and ownership need attention: restoring ownership requires the referenced roles to exist and suitable permissions to set ownership. For an isolated test, you may choose --no-owner so the restore user owns the objects, but decide deliberately how production roles and grants will be recreated.
pg_dump backs up one database, not every cluster-wide role or tablespace. On a self-managed PostgreSQL cluster, pg_dumpall --globals-only can export cluster-wide definitions separately; restoring those definitions requires elevated privileges. Managed services often restrict these operations or manage roles differently, so follow the provider's recovery procedure instead.
After restoring, check that the restore command completed without errors, that expected tables and recent records are present, and that the application can perform a basic read and write against the test database. A dump that exists on disk is not proof that the app can use it.
Create and check a SQLite snapshot
The sqlite3 command-line shell includes a .backup command that uses SQLite's backup facilities. Open the live database and write a named snapshot:
sqlite3 app.db
sqlite> .backup backup-2026-10-27.db
sqlite> .quit
Copy the completed snapshot to protected storage separate from the app host. If you have enabled WAL mode, avoid treating a raw copy of app.db as a complete backup while writes are active; use .backup or an application-level use of the Online Backup API.
For a restore drill, use a separate test path or machine so you do not replace production data. Open the snapshot with the SQLite shell, run an integrity check, and start a test instance of the app against it. Check application-specific records and key user flows as well. A database integrity check can detect structural problems, but it does not prove that the restored data is complete for your app or that the app version is compatible with it.
If you need to put a SQLite snapshot back into service, stop the app's database connections first and preserve the current files before replacing anything. Test this procedure in an isolated environment, especially when your deployment uses WAL mode, attached databases, or stores uploads outside the SQLite file.
Make schema changes part of the recovery plan
A database backup and an application release are a pair. A backup may contain a schema from before the latest deployment, while the newest application code expects a later schema. Keep your migration files and deployment history with the code, and decide which application version can run against each recovery point.
Before a migration that changes or removes data, confirm that you have a recent restorable backup and understand whether the migration can be reversed. Rehearse restoring a copy and applying the intended migration sequence. A database rollback is not always as simple as restoring an older file: it can discard valid user changes made after that backup.
For PostgreSQL, logical dumps can generally be loaded into newer server versions, but that does not guarantee that every extension, role, setting, or application migration is compatible. Test the actual source and target versions and dependencies you plan to use. For SQLite, test the restored file with the application version and migration path you intend to deploy.
Run a restore drill and keep a short incident checklist
Schedule a restore drill at least a few times a year, and after changing the database version, backup format, provider, or storage destination. Use a copy of a real backup in a separate environment. Record how long recovery took, what access or files were missing, and which records or application flows you checked.
When a real recovery is needed:
- Identify whether the problem is ongoing. If writes are corrupting data, stop or limit writes where safe before creating more restore points.
- Select a known-good restore point that meets your RPO. Preserve the affected database and backup copies for investigation rather than overwriting them immediately.
- Restore into a clean, isolated database or service using the documented procedure for that database and provider.
- Run integrity checks and application smoke tests. Confirm essential records, user access, and any external files or integrations the app needs.
- Point the app at the recovered database only after the checks pass. Confirm monitoring and backup jobs are healthy, and document any data loss or repair work.
For a security incident, also review credentials and access before reconnecting a restored database to the app. A restored copy can contain the same compromised secrets or vulnerable application data as the original.
A right-sized plan to start with
For a small app that can tolerate losing up to a day of changes, start with a daily database-specific backup, a separate encrypted copy, and a short retention policy that fits your budget. Test restoring one copy into an isolated environment before you depend on it. If that recovery point is too old or the restore takes too long, shorten the schedule, automate more of the recovery, or use a managed service's documented point-in-time recovery.
Keep the process small enough that you can repeat it. A backup you can find, decrypt, restore, and verify is more useful than a complex system that nobody has practiced.