Provide Instructions or possibility to backup data

[NotANormalNerd]

Hello everyone and thank you for your amazing work.

I would wish for a possibility to backup my data contained within plausible. Right now everything is stored in docker volumes and exporting them and then throwing them into a backup is quite some work. It would be nice if all the volumes could be host paths that can easily be backed up by something like borgbackup.

Thanks again and best regards.

[ruslandoga]

👋 @NotANormalNerd

I don't have experience with borgbackup so I won't be able to provide instructions. And I wonder how safe backing up volumes of a running database is as even SQLite warns against using cp

[ruslandoga]

However, we probably should have at least a guide outlining possible approaches. We can start with pg_dump and a link to https://clickhouse.com/docs/en/operations/backup

[th0rgall]

Hello, adding my 2 cents: on my self-hosted install, I'm backing up the Clickhouse database using https://github.com/Altinity/clickhouse-backup, and the Postgres database using https://github.com/eeshugerman/postgres-backup-s3; both with an S3 object storage server as a target.

The way I've configured it is to:

• add my Plausible Clickhouse & Postgres containers to an external Docker network named plausible_backup.
• have a separate Docker Compose project to run the backup containers (see the spoiler below).
• this compose project is run weekly using a system crontab, with the command docker compose -f ${COMPOSE_FILE} up && docker compose -f ${COMPOSE_FILE} rm -fsv.

Regarding borg & incremental backups: clickhouse-backup supports incremental backups, but I don't have those configured. I suppose you could try to configure these backup tools, or other tools, to back up (overwrite) to a local disk location. After each backup, you can then synchronize these local backups to a borg repository.

docker-compose.yml

You will need to change values here to match your environment.

# Note: these two backup services should be run using a system cron job
services:
  clickhouse_backup:
    image: altinity/clickhouse-backup
    command: create_remote
    environment:
      CLICKHOUSE_HOST: plausible-plausible_events_db-1
      REMOTE_STORAGE: s3
      # FYI: it doesn't seem like there is a password configured in the default compose install instructions
      CLICKHOUSE_PASSWORD: 
      S3_ACCESS_KEY: {key}
      S3_SECRET_KEY: {key}
      S3_BUCKET: {bucket}
      S3_PATH: {path}
      S3_ENDPOINT: {endpoint}
      S3_REGION: {region}
    volumes:
    - plausible_event-data:/var/lib/clickhouse
    networks:
    - plausible_backup
  postgres_backup:
    # https://github.com/eeshugerman/postgres-backup-s3
    image: eeshugerman/postgres-backup-s3
    environment:
      # We want a weekly schedule, but we will configure this with cron, 
      # so both containers exit.
      # SCHEDULE: '@weekly'     # optional
      BACKUP_KEEP_DAYS: 25     # optional
      # passphrase is for encryption (optional)
      PASSPHRASE: {passphrase}
      S3_ENDPOINT: {endpoint}
      S3_REGION: {region}
      S3_ACCESS_KEY_ID: {key}
      S3_SECRET_ACCESS_KEY: {key}
      S3_BUCKET: {bucket}
      S3_PREFIX: {path}
      POSTGRES_HOST: plausible-plausible_db-1
      POSTGRES_DATABASE: plausible_db
      POSTGRES_USER: {user}
      POSTGRES_PASSWORD: {password}
    networks:
    - plausible_backup

volumes:
  plausible_event-data:
    external: true

networks:
  plausible_backup:
    external: true