< _

PostgreSQL Backups for Small Projects

Abstract generated cover for PostgreSQL Backups for Small Projects.

Backups are easy to ignore until you need one. That is the problem. For a small PostgreSQL-backed project, the backup plan does not need to be complicated. It does need to exist, run automatically, and be tested. A backup that has never been restored is only a hope. The practical goal is simple:

If the server dies or data is damaged, can I recover the important database state? If the answer is unclear, the project does not have a real backup plan yet.

The Mental Model

A database backup is a recoverable copy of important state. For PostgreSQL, small projects often start with logical dumps using `pg_dump`. The basic flow is: ```plain text dump database -> store backup file -> copy off server -> test restore -> rotate old backups

Each step matters.
If backups only live on the same server, a server failure may take the backups too.
If backups are never restored, you do not know whether they work.
If old backups never rotate, storage eventually fills up.
## Basic \`pg_dump\`
A simple backup command:
```bash
pg_dump my_database > my_database.sql

A more useful filename includes a timestamp:

pg_dump my_database > backups/my_database-2026-06-20.sql

For custom-format dumps:

pg_dump -Fc my_database > backups/my_database-2026-06-20.dump

Custom-format dumps are restored with `pg_restore`. Plain SQL dumps are restored with `psql`. The exact choice depends on your workflow, but know how you plan to restore before you need to restore.

Restoring a Backup

Plain SQL restore:

psql restored_database < my_database.sql

Custom-format restore:

pg_restore -d restored_database my_database.dump

Do not test restores against the production database. Create a separate test database and restore there. The restore test should answer:

  • does the backup file work?
  • are tables present?
  • is expected data present?
  • does the app start against the restored database? That last question matters because a technically restored database may still be missing something the app needs.

Automating Backups

Manual backups are easy to forget. For small projects, a cron job or systemd timer can be enough. The script might:

  1. Create a dated backup file.
  2. Compress it.
  3. Upload it to separate storage.
  4. Delete old local backups.
  5. Log success or failure. The important part is that failure is visible. If the backup job has been failing for two months and nobody noticed, automation did not solve the problem. It only made the failure quieter.

Store Backups Somewhere Else

A backup on the same server is useful for some mistakes, but not all disasters. If you accidentally drop a table, a local backup may help. If the server disk dies, the local backup may be gone too. At least one copy should live elsewhere:

  • object storage
  • another server
  • secure backup service
  • encrypted offsite storage For private data, think about encryption and access controls. Backups often contain sensitive production information. Treat them accordingly.

Retention

Retention means deciding how long backups are kept. A simple small-project policy might be: plain text daily backups for 7 days weekly backups for 4 weeks monthly backups for 6 months The right policy depends on the project. More retention gives you more recovery options, but it also uses more storage and may increase data exposure risk. The main thing is to have an intentional policy instead of an accidental folder full of old dumps.

What Else Needs Backup?

The database may not be the only important state. Small projects may also need:

  • uploaded files
  • generated private reports
  • media assets
  • environment configuration
  • deployment notes
  • TLS or service configuration
  • background job state Source code in Git is usually recoverable. Production data often is not. Make sure the backup plan covers the actual important state, not just the database because it is obvious.

Common Mistakes

Mistake 1: Never testing restore

This is the biggest one. A backup is not real until you know it can be restored.

Mistake 2: Keeping backups only on the production server

That protects against some mistakes, but not server loss. Copy backups off the server.

Mistake 3: Forgetting sensitive data

Database backups can contain customer data, emails, tokens, addresses, and business records. Protect backups like production data.

Where This Shows Up in Real Projects

Backups matter for any project with state:

  • Django app
  • ecommerce backend
  • internal tool
  • CRM integration
  • reporting database
  • API service Even if the project is small, losing the database can be expensive. A simple backup script, offsite copy, and restore test can prevent a small failure from becoming a major incident.

Key Takeaways

  • PostgreSQL backups should be automatic and tested.
  • `pg_dump` is a common starting point for small projects.
  • Store at least one backup copy off the server.
  • Test restores into a separate database.
  • Decide a retention policy intentionally.

  • Back up all important state, not just source code.

    Related Articles

  • VPS Deployment Basics for Developers

  • Docker Compose for Small Projects
  • API Integrations for Business Software

← Back to Blog Index