Repository files navigation

Backup PostgresSQL to the local filesystem with periodic rotating backups, based onschickling/postgres-backup-s3.Backup multiple databases from the same host by setting the database names inPOSTGRES_DB separated by commas or spaces.

Supports the following Docker architectures:linux/amd64,linux/arm64,linux/arm/v7,linux/s390x,linux/ppc64le.

Please consider reading detailed theHow the backups folder works?.

This application requires the docker volume/backups to be a POSIX-compliant filesystem to store the backups (mainly with support for hardlinks and softlinks). So filesystems like VFAT, EXFAT, SMB/CIFS, ... can't be used with this docker image.

Docker:

docker run -u postgres:postgres -e POSTGRES_HOST=postgres -e POSTGRES_DB=dbname -e POSTGRES_USER=user -e POSTGRES_PASSWORD=password  prodrigestivill/postgres-backup-local

Docker Compose:

version:'2'services:postgres:image:postgresrestart:alwaysenvironment:            -POSTGRES_DB=database            -POSTGRES_USER=username            -POSTGRES_PASSWORD=password#  - POSTGRES_PASSWORD_FILE=/run/secrets/db_password <-- alternative for POSTGRES_PASSWORD (to use with docker secrets)pgbackups:image:prodrigestivill/postgres-backup-localrestart:alwaysuser:postgres:postgres# Optional: see belowvolumes:            -/var/opt/pgbackups:/backupslinks:            -postgresdepends_on:            -postgresenvironment:            -POSTGRES_HOST=postgres            -POSTGRES_DB=database            -POSTGRES_USER=username            -POSTGRES_PASSWORD=password#  - POSTGRES_PASSWORD_FILE=/run/secrets/db_password <-- alternative for POSTGRES_PASSWORD (to use with docker secrets)            -POSTGRES_EXTRA_OPTS=-Z1 --schema=public --blobs            -SCHEDULE=@daily            -BACKUP_KEEP_DAYS=7            -BACKUP_KEEP_WEEKS=4            -BACKUP_KEEP_MONTHS=6            -HEALTHCHECK_PORT=8080

For security reasons it is recommended to run it as userpostgres:postgres.

In case of running aspostgres user, the system administrator must initialize the permission of the destination folder as follows:

# for default images (debian)mkdir -p /var/opt/pgbackups&& chown -R 999:999 /var/opt/pgbackups# for alpine imagesmkdir -p /var/opt/pgbackups&& chown -R 70:70 /var/opt/pgbackups

Most variables are the same as in theofficial postgres image.

This variables are not intended to be used for normal deployment operations:

First a new backup is created in thelast folder with the full time.

Once this backup finish succefully then, it is hard linked (instead of coping to avoid use more space) to the rest of the folders (daily, weekly and monthly). This step replaces the old backups for that category storing always only the latest for each category (so the monthly backup for a month is always storing the latest for that month and not the first).

So the backup folder are structured as follows:

• BACKUP_DIR/last/DB-YYYYMMDD-HHmmss.sql.gz: all the backups are stored separatly in this folder.
• BACKUP_DIR/daily/DB-YYYYMMDD.sql.gz: always store (hard link) thelatest backup of that day.
• BACKUP_DIR/weekly/DB-YYYYww.sql.gz: always store (hard link) thelatest backup of that week (the last day of the week will be Sunday as it uses ISO week numbers).
• BACKUP_DIR/monthly/DB-YYYYMM.sql.gz: always store (hard link) thelatest backup of that month (normally the ~31st).

And the following symlinks are also updated after each successfull backup for simlicity:

BACKUP_DIR/last/DB-latest.sql.gz -> BACKUP_DIR/last/DB-YYYYMMDD-HHmmss.sql.gzBACKUP_DIR/daily/DB-latest.sql.gz -> BACKUP_DIR/daily/DB-YYYYMMDD.sql.gzBACKUP_DIR/weekly/DB-latest.sql.gz -> BACKUP_DIR/weekly/DB-YYYYww.sql.gzBACKUP_DIR/monthly/DB-latest.sql.gz -> BACKUP_DIR/monthly/DB-YYYYMM.sql.gz

Forcleaning the script removes the files for each category only if the new backup has been successfull.To do so it is using the following independent variables:

• BACKUP_KEEP_MINS: will remove files from thelast folder that are older than its value in minutes after a new successfull backup without affecting the rest of the backups (because they are hard links).
• BACKUP_KEEP_DAYS: will remove files from thedaily folder that are older than its value in days after a new successfull backup.
• BACKUP_KEEP_WEEKS: will remove files from theweekly folder that are older than its value in weeks after a new successfull backup (remember that it starts counting from the end of each week not the beggining).
• BACKUP_KEEP_MONTHS: will remove files from themonthly folder that are older than its value in months (of 31 days) after a new successfull backup (remember that it starts counting from the end of each month not the beggining).

The folderhooks inside the container can contain hooks/scripts to be run in differrent cases getting the exact situation as a first argument (error,pre-backup orpost-backup).

Just create an script in that folder with execution permission so thatrun-parts can execute it on each state change.

Please, as an example take a look in the script already present there that implements theWEBHOOK_URL functionality.

By default this container makes daily backups, but you can start a manual backup by running/backup.sh.

This script as example creates one backup as the running user and saves it the working folder.

docker run --rm -v"$PWD:/backups" -u"$(id -u):$(id -g)" -e POSTGRES_HOST=postgres -e POSTGRES_DB=dbname -e POSTGRES_USER=user -e POSTGRES_PASSWORD=password  prodrigestivill/postgres-backup-local /backup.sh

You can change theSCHEDULE environment variable in-e SCHEDULE="@daily" to alter the default frequency. Default isdaily.

More information about the scheduling can be foundhere.

Foldersdaily,weekly andmonthly are created and populated using hard links to save disk space.

Some examples to restore/apply the backups.

To restore using the same backup container, replace$BACKUPFILE,$CONTAINER,$USERNAME and$DBNAME from the following command:

dockerexec --tty --interactive$CONTAINER /bin/sh -c"zcat$BACKUPFILE | psql --username=$USERNAME --dbname=$DBNAME -W"

Replace$BACKUPFILE,$VERSION,$HOSTNAME,$PORT,$USERNAME and$DBNAME from the following command:

docker run --rm --tty --interactive -v$BACKUPFILE:/tmp/backupfile.sql.gz postgres:$VERSION /bin/sh -c"zcat /tmp/backupfile.sql.gz | psql --host=$HOSTNAME --port=$PORT --username=$USERNAME --dbname=$DBNAME -W"