rizl4s/multi-postgres-backup-local repository overview

⁠multi-postgres-backup-local

Backup PostgresSQL to the local filesystem with periodic rotating backups, based on schickling/postgres-backup-s3⁠ and prodrigestivill/docker-postgres-backup-local⁠. Backup multiple databases and multiple postgres docker instances from the same host. In order to this you need to create an env file for each postgres instance you want to connect, and set database names in POSTGRES_DB env variable 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 the How the backups folder works?⁠.

This application requires the docker volume /app/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.

⁠Usage

Docker:

Docker Compose:

services:
  postgres:
    image: postgres:16-bookworm
    restart: always
    environment:
      - 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: rizl4s/multi-postgres-backup-local:16-bookworm
    restart: always
    volumes:
      - /var/opt/pgbackups:/app/backups
      - ./envs:/app/envs
    depends_on:
      - postgres

For security reasons the container run with a rootless user.

Please check that backup folder has appropriate permissions.

mkdir -p /var/opt/pgbackups && chown -R 1000:1000 /var/opt/pgbackups

As example check also the test-compose directory in this repo.

⁠Build

See here⁠ for build instructions.

⁠Environment Variables

Most variables are the same as in the official postgres image⁠.

⁠Special Environment Variables

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

⁠How the backups folder works?

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

Once this backup finish successfully 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/POSTGRES_HOSTNAME/last/DB-YYYYMMDD-HHmmss.sql.gz: all the backups are stored separately in this folder.
• BACKUP_DIR/POSTGRES_HOSTNAME/daily/DB-YYYYMMDD.sql.gz: always store (hard link) the latest backup of that day.
• BACKUP_DIR/POSTGRES_HOSTNAME/weekly/DB-YYYYww.sql.gz: always store (hard link) the latest backup of that week (the last day of the week will be Sunday as it uses ISO week numbers).
• BACKUP_DIR/POSTGRES_HOSTNAME/monthly/DB-YYYYMM.sql.gz: always store (hard link) the latest backup of that month (normally the ~31st).

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

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

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

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

⁠Hooks

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

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

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

⁠Manual Backups

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

docker exec -it <your_pgbackups_container> sh
./backup.sh

⁠Automatic Periodic Backups

You can change the SCHEDULE environment variable to alter the default frequency. Default is daily.

More information about the scheduling can be found here⁠.

Folders daily, weekly and monthly are created and populated using hard links to save disk space.