Database backup

This guide describes how to back up the Rediflow PostgreSQL database. All application data lives in PostgreSQL; there is no SQLite or other backend.

Principles

• Regular full backups: Run pg_dump (or equivalent) on a schedule.
• Retention policy: Decide how many backups to keep and where (local, remote, offsite).
• Test restores: Periodically verify that a backup can be restored successfully.
• Monitor duration and disk: Backups consume CPU, I/O, and storage; ensure your schedule and retention fit your environment.

Backup options

Option 1: pg_dump (logical backup)

Recommended for portability and restore to another instance. Works against a running database (no downtime).

# Custom format (compressed; recommended for restore flexibility)
pg_dump -h localhost -p 5432 -U rediflow -Fc rediflow > rediflow_$(date +%Y%m%d_%H%M).dump

# Plain SQL (human-readable; larger files)
pg_dump -h localhost -p 5432 -U rediflow rediflow > rediflow_$(date +%Y%m%d).sql

Restore: pg_restore -d rediflow rediflow_YYYYMMDD_HHMM.dump (or psql -d rediflow < rediflow_YYYYMMDD.sql for plain SQL). For a full restore, use an empty database or drop and recreate it first.

Caveat: pg_dump is CPU- and I/O-intensive. On large databases, frequent full dumps can impact production. Adjust frequency to your DB size and load.

Option 2: Podman volume backup

If using the default compose volume rediflow_pgdata, you can archive the raw data directory:

podman run --rm -v rediflow_pgdata:/data -v $(pwd):/backup alpine tar czf /backup/rediflow_pgdata_$(date +%Y%m%d).tar.gz -C /data .

Caveat: Restore requires a compatible PostgreSQL version. Prefer pg_dump for portability unless you need file-level backup.

Option 3: WAL archiving (advanced)

For minimal data loss and point-in-time recovery, PostgreSQL supports WAL archiving with archive_mode and archive_command. Configure postgresql.conf and a base backup (pg_basebackup). This is the right approach for production systems that need continuous protection; it requires more setup and operational discipline. See PostgreSQL documentation for WAL archiving.

Scale-appropriate guidance

Adjust for your workload: Monitor backup duration and disk usage. If a full dump takes 30 minutes and your DB is under heavy load, running it every hour may hurt performance. Start conservative (e.g. daily) and increase frequency only if needed and safe.

Example cron jobs

Daily full backup at 02:00 (adjust credentials and paths):

0 2 * * * pg_dump -h localhost -p 5432 -U rediflow -Fc rediflow > /backups/rediflow/rediflow_$(date +\%Y\%m\%d).dump

Retention: Add a cleanup job (e.g. delete dumps older than 30 days) or use a backup tool that rotates files.

More frequent (e.g. every 6 hours): Only if your DB is small and you have verified that backup duration and I/O impact are acceptable:

0 */6 * * * pg_dump -h localhost -p 5432 -U rediflow -Fc rediflow > /backups/rediflow/rediflow_$(date +\%Y\%m\%d_\%H).dump

Warning: Running pg_dump every few minutes on a large database can harm production. Prefer WAL archiving for continuous protection.

Export scripts (project-level)

For archival or migration of project data (not a full DB backup), use:

• scripts/export_projects_data.py — projects, WPs, tasks, milestones, etc.
• scripts/export_settings_data.py — orgs, role types, calendars, etc.

These produce JSON or ODS for selective backup or data transfer.