Skip to content

Database Backup

It is crucial to regularly back up your application database to protect your data and ensure you can restore it in case of corruption, server failure, or accidental deletion. This guide explains how to back up the PostgreSQL database used by CxReports when running via Docker Compose.

What You Are Backing Up

In the default Docker Compose configuration you created under "Docker Compose Configuration", the db service uses a postgres image and has a mounted volume (e.g. postgres_data) for the database files.

However, simply backing up the volume's files is less portable than using a PostgreSQL dump. The recommended approach is to use pg_dump to create a logical backup that can be restored on the same or a different PostgreSQL server.

Prerequisites

You will need the following information (these are the same values you configured in your docker-compose.yml and appsettings.Production.json):

  • Host: db (in Docker Compose network)
  • Database name: as configured (in the example: cxreports)
  • User: e.g. postgres (or your custom user)
  • Password: as configured (e.g. password, or your secure value)
  • Container name: Unless you renamed it, it will be something like yourfolder_db_1 (Docker Compose auto-names) or simply reference it by service db

Creating a Backup

You can run the backup from your host machine (assuming you have Docker installed) by executing a command that uses pg_dump inside the db container:

docker-compose exec db pg_dump -U postgres -d cxreports -F c -b -v -f /var/lib/postgresql/data/backup/cxreports_$(date +%Y%m%d_%H%M%S).dump

Here's what each option means:

  • docker-compose exec db → run a command inside the db service container
  • pg_dump → PostgreSQL command to dump database
  • -U postgres → user name
  • -d cxreports → database name
  • -F c → output format "custom" (makes a .dump file which can be restored with pg_restore)
  • -b → include large objects (blobs)
  • -v → verbose
  • -f /var/lib/postgresql/data/backup/... → path inside container where the backup file will be written

Directory Requirements

You must ensure the directory /var/lib/postgresql/data/backup/ exists and is writable. You might prefer to map a host directory into the container for backups so you retain them on your host.

The timestamp $(date +%Y%m%d_%H%M%S) ensures each backup file is uniquely named.

Copying Backup Files

Once the backup file exists inside the container, you'll want to copy it to your host machine (or to an off-site location) for safekeeping:

docker cp yourfolder_db_1:/var/lib/postgresql/data/backup/cxreports_20251029_120000.dump ./backups/

Replace yourfolder_db_1 with the actual container name (you can find it with docker ps), and adjust the source path/basename accordingly. Keep a regular schedule and rotate backups (e.g., keep the last 7 daily backups, one per week for the last month, etc.)

Restoring a Backup

If you need to restore the backup (e.g., on a new container or after you wipe your database), you can follow these steps:

1. Stop the Application

Stop the db container (and optionally app container) to ensure no active connections:

docker-compose down

2. Remove Old Database (Optional)

Remove the old database volume or start fresh (be aware this deletes existing data!):

docker volume rm yourfolder_postgres_data

Or if you use a host-directory volume, clear it appropriately.

3. Copy Backup File

Copy the backup file into the container's file system or mount it where pg_restore can access it.

4. Start Database Container

Start the db container again (it will initialize the database empty):

docker-compose up -d db

5. Restore the Database

Execute pg_restore to restore the dump into your database:

docker-compose exec db pg_restore -U postgres -d cxreports -v /var/lib/postgresql/data/backup/cxreports_20251029_120000.dump

6. Start Application Container

Start the app container as needed:

docker-compose up -d app

Automating Backups

For better reliability, consider adding a cron job (on the host or a separate backup container) to automate the backup process:

  • Run daily at early off-peak hours
  • Copy backup to host and optionally to external storage (S3, remote server, tape)
  • Rotate and purge old backups (e.g., keep 7 daily, 4 weekly, 12 monthly)
  • Monitor backup success/failure and validate restorability at intervals (e.g., quarterly test restore)

If using Docker on a host system, a sample host cron entry might look like:

0 2 * * * cd /path/to/project && docker-compose exec db pg_dump -U postgres -d cxreports -F c -b -v -f /var/lib/postgresql/data/backup/cxreports_$(date +\%Y\%m\%d_\%H\%M\%S).dump && docker cp yourfolder_db_1:/var/lib/postgresql/data/backup/cxreports_$(date +\%Y\%m\%d_\%H\%M\%S).dump /path/to/backups/

Best Practices

  • Test restores regularly - Always test restoring a backup periodically to verify your process works and the dump is valid
  • Secure backup files - They often contain sensitive data, so apply filesystem permissions and consider encrypting backups
  • Off-site storage - Store backups off-site or in the cloud in case the host machine fails or is destroyed
  • Monitor disk usage - Monitor disk usage of backup directory and rotation logic to prevent running out of space

Volume Mount Limitations

While the volume mount (postgres_data) ensures persistence of the database files, this alone does not replace a logical dump: for portability, migration, or protection from volume-level corruption, pg_dump is strongly recommended.