.. | ||
restore | ||
scripts | ||
Dockerfile | ||
LICENSE | ||
README.md |
This image contains scripts that periodically backup an SQL database server (currently only PostgreSQL is supported). It is a heavily modified version of tiredofit/docker-db-backup, with these improvements and changes:
- Ability to run as a non-root user
- Scheduling based on
cron
(via supercronic) - Fully automated backup restoration
- Removal of features not necessary for this server
Environment Variables
DB_TYPE
is the type of database server.- Allowed:
postgres
- Allowed:
DB_HOST
is the hostname of the database server. The hostname is the name of the service container indocker-compose.yml
.- Example:
postgres
- Example:
DB_PORT
is the port of the database server.- Default:
5432
forpostgres
- Default:
DB_USER
is the database user that has permissions to create or restore the backup.DB_PASS
is the database user's password.COMPRESSION
sets the compression algorithm for backup files.- Default:
zstd
- Allowed:
none
,gzip
,zstd
- Default:
COMPRESSION_LEVEL
is the level or strength of compression. Higher levels take longer but usually result in smaller files.- Default:
9
when usinggzip
- Default:
10
when usingzstd
- Allowed:
1
to9
when usinggzip
- Allowed:
1
to19
when usingzstd
- Default:
BACKUP_RETENTION_SECONDS
is the amount of seconds backups should be kept for. Older backups are deleted when a new backup is created.- Default:
1440
(24 hours) - Example:
10080
(7 days) - Example:
43200
(30 days) - Example:
525600
(365 days) - Example:
""
(disables automatic cleanup)
- Default:
CRON
is the cron expression which determines how often backups are made. The format is based on cronexpr.- Default:
0 */2 * * *
(every 2 hours) - Example:
0 */4 * * *
(every 4 hours) - Example:
0 * * * *
(every hour) - Example:
0 0 * * *
(every day at midnight)
- Default:
TZ
is the server's timezone.- Default:
UTC
- Example:
Europe/Prague
- Default:
Locations
Database files and backups are stored in folders that follow this pattern:
/srv/<service>/postgres
for the database files/srv/<service>/postgres.backup
for the backups
Retention
By default, backups are made every two hours, and are only kept for one day.
The rationale behind these defaults is that I expect you to have daily backups of your whole server. With daily server backups, there is no need for these database backups to be stored for more than a day, since backups older than a day can be restored from your daily backups.
You can change both the frequency and retention of backups using environment variables.
Compression
The default compression settings use zstd
with a compression level 10, and a 24-bit (16 MiB
) sliding window (the sliding window is hardcoded in the scripts). I found this to be a good balance of speed, size, and memory usage.
You can experiment with different settings, but keep in mind that increasing either compression level or the sliding window size will increase memory requirements. All services that use this image have a hard limit of 128 MB
RAM for the backup container, which is already very close to the actual amount this image uses with default compression settings.
Server Downtime
If the database server is not available, the backup script will wait for a minute and then check the server's availability again. If the server is unavailable after the next scheduled backup was supposed to start, the scheduler (supercronic) detects that the previous backup has not finished yet, and it will keep postponing the next backup until the previous one finishes.
Restoring a Backup
Every service folder that uses this image includes a restorebackup.sh
script. To restore the backup, cd
into the service folder and run ./restorebackup.sh
as root
.
The script will show you a numbered list of available backups (see Locations), and ask you which backup you want to restore. Type the number next to the file name and press Enter
to proceed with the restoration, or press Ctrl + C
to exit the script.
You may see the following two errors during the restoration, which is expected:
ERROR: current user cannot be dropped
ERROR: role "<role>" already exists
The restoration process should finish automatically. In case something goes wrong, here is a step-by-step description to help you troubleshoot:
- The script checks whether the database container is running.
- If the database container is not running, the script shows how to start the database container, and exits. This gives you a chance to ensure the database container is working.
- If the database container isn't working, you can try wiping the folder with the actual database files (see Locations), and starting the database container again to create a fresh database.
- If the backup container is still running, it is stopped.
- If the service's server container is running, it is stopped and will be restarted when the script exits (whether it finishes successfully or not).
- In the folder where backups are stored (see Locations), a file named
restore
is created. The file contains the name of the selected backup. - The backup container is started. It finds the
restore
file, and initiates restoration.- All active connections to the database server are terminated (PostgreSQL does not allow deleting databases with active connections).
- The SQL statements in the backup file are executed.
- The restored databases are vacuumed and analyzed.
- If the restoration succeeded, the
restore
file is deleted and the backup container is started again to resume scheduled backups. Otherwise, it stays stopped so that you can fix the issue.
You can start the backup container manually using docker compose up -d backup
, and see its logs using docker compose logs -f backup
.
If the backup container is started while the restore
file still exists, it will print an error and wait for the file to be deleted before it starts scheduling backups again.
Restoring from an Alternative Location
If you need to restore a backup from an alternative location (for ex. from a daily system backup, as described in the Retention section), do the following:
- Stop the backup container using
docker compose stop backup
- Copy the backup file into the folder where current backups are stored (see Locations)
- Run the restoration script
You must ensure the file is readable and writable by the service's designated database user. If the file in the alternative location already has correct ownership information, use cp -a
when copying the file to preserve ownership.