PostgreSQL Backup

A simple script for backing up a PostgreSQL database to a Restic backend.

Usage

1. Create a backup user

For the database you wish to backup, create a backup user with the necessary permissions. For example:

CREATE ROLE backup WITH LOGIN PASSWORD '<PASSWORD>';
GRANT USAGE ON SCHEMA public TO backup;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT ON TABLES TO backup;

2. Run the container

I recommend simply using the available Docker image and a docker-compose.yml along the following lines.

For example, for a backup to Backblaze B2:

services:
  postgres-backup:
    image: wilw/postgres-backup:latest
    restart: always
    environment:
      # PostgreSQL connection variables. E.g.:
      - PGHOST=pgHost
      - PGDATABASE=pgDB
      - PGUSER=pgUser
      - PGPASSWORD=pgUserPassword

      # Restic specific variables. E.g.:
      - B2_ACCOUNT_ID=ID
      - B2_ACCOUNT_KEY=KEY
      - RESTIC_REPOSITORY=b2:BUCKET
      - RESTIC_PASSWORD=PASSWORD
      - RESTIC_HOSTNAME=postgres

The backup script will run hourly and will backup the database pgDB. Change this variable (and the other ones in the environment block) before running the container.

Note that the script simply calls pg_dump. As such, use any [https://www.postgresql.org/docs/current/libpq-envars.html](environment variables) that you need to configure your connection.

Likewise, the Restic configuration is done through environment variables. The script will call restic backup with the provided repository and password.

Remember to call restic init on the container once it launches if it's a new Restic repository. E.g.:

docker compose exec -it postgres-backup restic init

3. Notes on retention

The script, by default, has a maximum backup retention of 30 days.