Backup Docker Installation

[cschellhas]

Hi,

what is the best way to backup the Data for a possible restore?

Is it enough to backup the 3 Data-Volumes?
db-data
event-data
geoip

thanks in advance

[squareloop1]

I am trying wo wrap my head around this too at the moment.

It is easy to backup the postgres db, but it seems to be more tricky to backup the clickhouse db. Do we even need to backup the geoip database as it can just be redownloaded without losing any sensible information or analytics data?

Anybody knows of a good approach on how to backup and restore plausible running in the official docker environment?

[asbrwl]

I had tried to do a backup and restore by moving the volumes.
While Postgres and the plausible application itself moved correctly, the click house data store did not.
Post migration, I was able to login and all sites were there but the dashboard was unable to pull any data.

[tallesairan]

same with me :((

[kees-closed]

Anyone else tried some things? I suppose the Postgres can be backed up by dumping all the tables into a file. I might experiment with this later on.

[KatoPue]

Hello there,

here is what i did to move all the plausible data from one Server to another.

backup.sh dumps all information from the volumes into zipped files.
I moved those over to the new server and ran restore.sh wich created and filled the volumes with the data from the zips.

For more information consult the documentation at https://github.com/loomchild/volume-backup.

my scripts:

# backup.sh
#!/usr/bin/env sh

docker-compose stop
docker run --rm -v plausible_db-data:/volume -v `pwd`:/backup loomchild/volume-backup backup plausible_db-data
docker run --rm -v plausible_event-data:/volume -v `pwd`:/backup loomchild/volume-backup backup plausible_event-data
docker-compose start -d

# restore.sh
#!/usr/bin/env sh

docker-compose stop
docker run --rm -v plausible_db-data:/volume -v `pwd`:/backup loomchild/volume-backup restore -f plausible_db-data
docker run --rm -v plausible_event-data:/volume -v `pwd`:/backup loomchild/volume-backup restore -f plausible_event-data
docker-compose start -d

I hope this helps :)

[johakr]

Works like a charme. Saved my day. Thanks for sharing!

[simardcasanova]

I got an error when I tried to run the code above; for unknown reasons, the volumes had different names. To find the correct names, just run docker volume ls and replace plausible_db-data and plausible_event-data in the code above.

[Sayyiku]

Thanks for sharing! This is very useful for me！

[schnerring]

I self-host Plausible on Kubernetes and wrote DB-level backup scripts that I run daily against Plausible using cron. Restore scripts are also available:

https://github.com/schnerring/k8s-backup-scripts

The scripts aren't really applicable to docker-compose, but you can try to extract the important bits from the scripts and adjust them accordingly.

Use pg_dump to backup Plausible DB:

backup_postgres() {
  echo "Backing up Postgres ..."
  pod=$(get_pod_name "${POSTGRES_LABEL}" "${POSTGRES_NAMESPACE}")
  kubectl exec -i -n "${POSTGRES_NAMESPACE}" "$pod" -- \
    pg_dump -Fc "${PLAUSIBLE_DB}" >"${PLAUSIBLE_BACKUP_DIR}/$(date +%y%m%d)-postgres-${PLAUSIBLE_DB}.dump"
}

Use clickhouse-backup for Clickhouse. Install clickhouse-backup into the container, run clickhouse-backup create to create a backup and delete the local backup with clickhouse-backup delete after downloading it from the container.

backup_clickhouse() {
  echo "Backing up ClickHouse ..."

  mkdir -p "${PLAUSIBLE_BACKUP_DIR}"

  install_clickhouse_backup

  backup_name=$(date +%y%m%d)-clickhouse
  echo "Creating backup ${backup_name} ..."
  pod=$(get_pod_name "${PLAUSIBLE_EVENT_DATA_LABEL}" "${PLAUSIBLE_NAMESPACE}")
  kubectl exec -i -n "${PLAUSIBLE_NAMESPACE}" "$pod" -- \
    clickhouse-backup create "${backup_name}"

  tmp="${PLAUSIBLE_BACKUP_DIR}/tmp"
  pod_path="${PLAUSIBLE_NAMESPACE}/${pod}:/var/lib/clickhouse/backup/${backup_name}"
  echo "Copying ${pod_path} to ${tmp} ..."
  kubectl cp "${pod_path}" "${tmp}"

  backup_destination_path="${PLAUSIBLE_BACKUP_DIR}/${backup_name}.tar.gz"
  echo "Compressing ${backup_destination_path} to ${tmp} ..."
  tar -zcf "${backup_destination_path}" -C "${tmp}" .

  echo "Cleaning up ..."
  rm -rf "${tmp}"
  kubectl exec -i -n "${PLAUSIBLE_NAMESPACE}" "$pod" -- \
    clickhouse-backup delete local "${backup_name}"
}

The restore script just reverts the above logic. Keep in mind that you need to manually restore the plausible.schema_migrations Clickhouse table, because clickhouse backup only supports MergeTree family tables like this:

EDIT: as of ClickHouse v23 and clickhouse-backup v2, tables other than MergeTree are now also supported

INSERT INTO plausible.schema_migrations (version,inserted_at) VALUES
	 (20200915070607,'2022-04-22 04:07:40'),
	 (20200918075025,'2022-04-22 04:07:40'),
	 (20201020083739,'2022-04-22 04:07:40'),
	 (20201106125234,'2022-04-22 04:07:40'),
	 (20210323130440,'2022-04-22 04:07:40');

NOTE: the query depends on the Plausible version. The above query is for Plausible v1.4.4

[sewid]

I wrote a tutorial some time ago in german: https://sewid-webdesign.de/blog/backup-und-restore-von-plausible-analytics

[bblanchon]

I didn't manage to back up the ClickHouse database using clickhouse-backup, as @schnerring recommended (the backups only contained metadata, but no actual data).
Instead, I use the BACKUP ALL command available in ClickHouse 23.4.

Here is a script that backs up both ClickHouse and PostgreSQL data to an S3 bucket:

S3_REGION="eu-west-3"
S3_BUCKET="my-bucket-name"
TIMESTAMP=$(date '+%Y-%m-%d-%H%M')

docker compose exec -T plausible_events_db clickhouse-client --query "BACKUP ALL TO S3('https://s3.$S3_REGION.amazonaws.com/$S3_BUCKET/$TIMESTAMP/clickhouse/')"
docker compose exec -T plausible_db pg_dump -d plausible_db -U postgres -Fc | s3cmd put - s3://$S3_BUCKET/$TIMESTAMP/postgres.dump

This script uses the s3cmd package, but you can replace s3cmd put with aws s3 cp to use awscli instead.