Postgresql incremental backup using Docker Compose

.gitignore

.idea

README.md

Incremental backups

Inspiration

• https://www.mydbops.com/blog/postgresql-17-incremental-backup-pg-basebackup-pg-combinebackup
• https://www.dbi-services.com/blog/postgresql-17-support-for-incremental-backups/

Prerequisites

Start with a clean database (OPTIONAL!)

docker compose down --remove-orphans --volumes

Start the database

docker compose up database

Stop the database and uncomment the mounting of the pg_hba.conf in the volumes section of the docker-compose.yml file and start the database again.

Ensure WAL summarization is enabled in the database

docker compose exec --user postgres database psql -c "alter system set summarize_wal='on'"

Backup

Run the base backup

docker compose run --rm pg-backup-base

Do an incremental backup

docker compose run --rm pg-backup-incremental-1

Make a change to the database

docker compose run --rm change

Do another incremental backup

docker compose run --rm pg-backup-incremental-2

Restore

docker compose run --rm pg-backup-restore

Start the restored database

docker compose up restored-database -d

Promote to read-write

docker compose exec --user postgres restored-database pg_ctl promote -D /var/lib/postgresql/data

docker-compose.yml

services:
  database:
    image: postgres:17-alpine
    ports:
      - "5432:5432"
    volumes:
      - postgresql:/var/lib/postgresql/data
      #- ./pg_hba.conf:/var/lib/postgresql/data/pg_hba.conf:ro
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: example
      POSTGRES_DB: example
    healthcheck:
      test: [ "CMD", "pg_isready", "-U", "postgres", "-d", "${POSTGRES_DB}" ]
      interval: 5s
      timeout: 3s
      retries: 5

  pg-backup-base:
    image: postgres:17-alpine
    depends_on:
      - database
    entrypoint: pg_basebackup -h database -U postgres -D /backup --write-recovery-conf --format=plain --verbose --progress --checkpoint=fast
    environment:
      PGPASSWORD: example
    volumes:
      - ./backups/base:/backup

  #  pg-backup-incremental:
  #    image: postgres:17-alpine
  #    depends_on:
  #      - database
  #    entrypoint: >
  #      /bin/sh -c "
  #        target=/backup/incr/$$(date -Is)
  #        mkdir $$target
  #        pg_basebackup -h database -U postgres --incremental=/backup/base/backup_manifest -D $$target --write-recovery-conf --format=plain --verbose --progress --checkpoint=fast
  #      "
  #    environment:
  #      PGPASSWORD: example
  #    volumes:
  #      - ./backups/base:/backup/base:ro
  #      - ./backups/incr:/backup/incr

  pg-backup-incremental-1:
    image: postgres:17-alpine
    depends_on:
      - database
    entrypoint: pg_basebackup -h database -U postgres --incremental=/backup/base/backup_manifest -D /backup/incr/1 --write-recovery-conf --format=plain --verbose --progress --checkpoint=fast
    environment:
      PGPASSWORD: example
    volumes:
      - ./backups/base:/backup/base:ro
      - ./backups/incr:/backup/incr

  change:
    image: postgres:17-alpine
    depends_on:
      - database
    environment:
      PGPASSWORD: example
    command: psql -h database -U postgres -c "create table test(id int, name varchar, updated_at timestamptz);"

  pg-backup-incremental-2:
    image: postgres:17-alpine
    depends_on:
      - database
    entrypoint: pg_basebackup -h database -U postgres --incremental=/backup/incr/1/backup_manifest -D /backup/incr/2 --write-recovery-conf --format=plain --verbose --progress --checkpoint=fast
    environment:
      PGPASSWORD: example
    volumes:
      - ./backups/base:/backup/base:ro
      - ./backups/incr:/backup/incr

  pg-backup-restore:
    image: postgres:17-alpine
    volumes:
      - ./backups/base:/backup/base:ro
      - ./backups/incr:/backup/incr:ro
      - ./restore:/restore
    #entrypoint: pg_combinebackup /backup/base /backup/incr/1 /backup/incr/2 -o /restore
    entrypoint: sh -c "pg_combinebackup /backup/base $$(find /backup/incr -mindepth 1 -maxdepth 1 -type d | sort) -o /restore"

  restored-database:
    image: postgres:17-alpine
    ports:
      - "5431:5432"
    volumes:
      - ./restore:/var/lib/postgresql/data:rw
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: example
      POSTGRES_DB: example
    healthcheck:
      test: [ "CMD", "pg_isready", "-U", "${DATABASE_USERNAME}", "-d", "${DATABASE_NAME}" ]
      interval: 5s
      timeout: 3s
      retries: 5

volumes:
  postgresql:

pg_hba.conf

# Allow local connections
local   all             all                                     trust

# Allow all IP connections (change subnet as needed)
host    all             all             0.0.0.0/0               md5

# Allow replication connections from Docker subnet
host    replication     postgres        172.19.0.0/16           trust