Databases fail for many reasons: a bad deployment, dropped tables, disk corruption, or even something as simple as accidentally running a destructive query. When PostgreSQL is containerized, another layer of risk is introduced. If your container or volume is removed without a backup, your data is gone for good.
If you are serious about your application, automated PostgreSQL backups are non-negotiable. In this guide, we'll walk through practical approaches to setting up automated backups for PostgreSQL inside Docker. We'll cover the tools PostgreSQL provides, how to schedule backups, how to rotate and prune them, and what to consider for larger or production systems.
By the end, you'll have a working strategy that runs on autopilot and keeps your database safe.
Postgres Setup
For demonstration, here’s a minimal docker-compose.yml file running PostgreSQL:
services:
db:
image: postgres:16
container_name: postgres
environment:
POSTGRES_USER: myuser
POSTGRES_PASSWORD: mypass
POSTGRES_DB: mydb
volumes:
- db_data:/var/lib/postgresql/data
volumes:
db_data:
This gives you a working Postgres instance with persistent storage. The database lives inside the db_data volume. If that volume is lost or corrupted, so is your data. Backups need to live outside of this container lifecycle.
Want to deploy PostgreSQL on your server? Check out our guide to deploying PostgreSQL on a VPS using Docker.
Creating a Manual Backup with pg_dump
pg_dump is the most common way to generate a logical backup of a PostgreSQL database. It exports your schema and data into a SQL file, which makes it portable and easy to restore. Running it inside Docker is straightforward with docker exec:
docker exec postgres pg_dump -U myuser mydb > backup.sql
This works fine for smaller databases, but keep in mind that logical dumps can take a long time and consume significant disk space as your dataset grows. For very large databases, you might prefer physical backups with pg_basebackup or file system snapshots. For most applications though, pg_dump strikes the right balance between simplicity and reliability.
The downside of running pg_dump manually is that it only works if you remember to do it. And let’s be honest, engineers are great at automating everything except the things they’re supposed to do daily. So lets get into automating the backups so you can sleep easy at night knowing your data is safe.
Automating Backups with a Sidecar Container
To automate backups, you can run a lightweight container alongside your database that executes pg_dump on a schedule. A common approach is to loop with sleep, writing a new dump file every day.
Here’s an example backup service added to docker-compose.yml:
backup:
image: postgres:16
container_name: pg_backup
depends_on:
- db
volumes:
- ./backups:/backups
entrypoint: >
bash -c 'while true; do
pg_dump -h db -U myuser mydb > /backups/db-$(date +%F-%H-%M-%S).sql;
sleep 86400;
done'
environment:
PGPASSWORD: mypass
This setup:
- Connects to the
dbservice. - Dumps the database once every 24 hours.
- Saves the output into a mounted
./backupsdirectory with a timestamp.
It’s simple, but surprisingly effective. For many small projects, this sidecar approach is all you need.
Scheduling postgresql backups with Cron
While looping with sleep inside a container is fine for simple setups, cron is the tool most engineers reach for when they want proper scheduling. Cron gives you full control over when backups run, how often, and how they integrate with other maintenance tasks.
On the host machine, you can add a cron entry like this:
0 2 * * * docker exec postgres pg_dump -U myuser mydb > ~/backups/db-$(date +\%F).sql
This creates a new backup every day at 2 AM. If you need more frequent dumps, you can schedule them hourly or every few minutes just by adjusting the expression.
The advantage of cron is its flexibility. You can add post-processing steps in the same job, such as compressing the file or pushing it to cloud storage. And unlike a backup container running in a loop, cron makes it easy to see and manage schedules across your server.
For environments where cron is not available, you can achieve the same effect inside a dedicated container running a cron daemon. This approach keeps all logic inside Docker and avoids relying on host-level configuration.
Compression to Save Space
As soon as you start keeping more than a handful of backups, file size becomes an issue. Logical dumps are plain text, and plain text compresses very well. The solution is to pipe the output through a compression tool like gzip:
docker exec postgres pg_dump -U myuser mydb | gzip > backup.sql.gz
This can reduce backup file sizes by 70-90%, depending on your data. For production systems with daily backups, compression is essential to keep storage costs manageable.
Backup Retention and Cleanup
Without proper cleanup, your backup directory will eventually consume all available disk space. A good retention policy keeps recent backups readily available while pruning older ones to save space.
Here's a simple retention strategy using find:
# Keep daily backups for 7 days, delete older ones
find ./backups -name "db-*.sql.gz" -mtime +7 -delete
# More sophisticated: keep daily for 7 days, weekly for 4 weeks
find ./backups -name "db-*.sql.gz" -mtime +7 ! -name "*-01.sql.gz" -delete
You can integrate this directly into your cron job:
# Complete backup and cleanup pipeline
0 2 * * * docker exec postgres pg_dump -U myuser mydb | gzip > ~/backups/db-$(date +\%F).sql.gz && find ~/backups -name "db-*.sql.gz" -mtime +7 -delete
For production systems, consider a more nuanced retention policy:
- Hourly backups for the last 24 hours
- Daily backups for the last 7 days
- Weekly backups for the last 4 weeks
- Monthly backups for the last 12 months
Restoring PostgreSQL Backups
Creating backups is only half the story. You need to know how to restore them when disaster strikes. Here's how to restore from your compressed backups:
# Restore to existing database (will overwrite existing data)
gunzip -c backup.sql.gz | docker exec -i postgres psql -U myuser mydb
# Restore to a new database for testing
docker exec postgres createdb -U myuser mydb_test
gunzip -c backup.sql.gz | docker exec -i postgres psql -U myuser mydb_test
# Restore from uncompressed backup
docker exec -i postgres psql -U myuser mydb < backup.sql
Always test your restore process on a non-production database first. A backup is only as good as your ability to restore from it.
Putting It All Together
A complete PostgreSQL backup solution combines automated scheduling, compression, and retention policies. Here's a production-ready example that ties everything together:
#!/bin/bash
# Complete backup script with error handling and cleanup
BACKUP_DIR="/backups"
DB_NAME="mydb"
RETENTION_DAYS=7
DATE=$(date +%F_%H-%M-%S)
BACKUP_FILE="$BACKUP_DIR/db-$DATE.sql.gz"
# Create backup with compression
if docker exec postgres pg_dump -U myuser "$DB_NAME" | gzip > "$BACKUP_FILE"; then
echo "Backup successful: $(basename $BACKUP_FILE)"
# Clean up old backups
find "$BACKUP_DIR" -name "db-*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "Cleaned up backups older than $RETENTION_DAYS days"
else
echo "Backup failed!"
exit 1
fi
Save this as a script, make it executable, and add it to your crontab. This gives you a reliable backup system that runs automatically and manages storage space.
The approaches in this guide scale from development environments to production systems. Start with the sidecar container for simplicity, then move to cron-based scheduling as your needs grow. The key is consistency: automated backups that run regularly are far more valuable than perfect backups that never happen.
Simplify with Serversinc
Sound like a lot of work? Serversinc handles all the complexity for you. Our platform combines container orchestration, cron APIs for scheduling backup commands, volume management, and automated notifications, so you can set up reliable PostgreSQL backups without the operational overhead.
Try Serversinc free at serversinc.io and get your database backups automated in minutes.