Skip to main content

Back up PostgreSQL, MySQL, S3 storage and Cloudflare

Nothing in Choir Master CMS backs up a PostgreSQL or MySQL database, an S3-compatible bucket or a Cloudflare account. The software does not do it for you and does not ship a tool for it. You use the standard tools of each service. This page names them and shows how to use them with this site, with the names of this site's databases and buckets filled in. Run each once by hand and check the result before relying on it. For SQLite and local files, see Back up a single-server site.

Read What to back up first. A complete backup is the database, both file stores (public and private) and your settings.

PostgreSQL​

Use pg_dump, which makes a consistent copy while the site runs. Use the same connection details as DATABASE_URL.

pg_dump --format=custom --file=choir-$(date +%Y%m%d).dump "$DATABASE_URL"

The custom format is compressed and lets you restore selectively with pg_restore. Keep the pg_dump program the same major version as the server or newer.

The PostgreSQL container from the Compose stack. The service is called db, and the user and database default to choir:

docker compose exec -T db pg_dump -U choir -d choir --format=custom > choir-$(date +%Y%m%d).dump

If you changed POSTGRES_USER or POSTGRES_DB in .env, use those. The -T option matters: without it Docker adds terminal characters to the file.

A managed database (Neon, RDS, Supabase and so on) usually has its own backups or snapshots. Use them as well as a dump of your own, and find out how long they are kept.

MySQL and MariaDB​

Use mysqldump (mariadb-dump on MariaDB). The options make a consistent copy of tables without locking the site.

mysqldump --single-transaction --routines --default-character-set=utf8mb4 \
  -h db.example.org -u choir -p choir > choir-$(date +%Y%m%d).sql

It asks for the password. Do not put the password on the command line, where other users of the machine can see it.

The MariaDB container, if you changed the Compose file to use it as described in MySQL and MariaDB. The container already has the user, password and database name in its environment:

docker compose exec -T mysql sh -c 'mariadb-dump --single-transaction --routines -u"$MARIADB_USER" -p"$MARIADB_PASSWORD" "$MARIADB_DATABASE"' > choir-$(date +%Y%m%d).sql

S3-compatible storage, and MinIO​

With STORAGE_PROVIDER=s3 the site keeps files in two buckets, S3_PUBLIC_BUCKET and S3_PRIVATE_BUCKET. Back up both, keeping the same file names (keys, such as images/1791738420452-05dc....png). The database refers to files by those keys, so a copy that renames them is no use.

Amazon S3, Backblaze B2, Spaces, and similar​

Use the AWS command-line tool, which also works with other providers when you give it their endpoint:

aws s3 sync s3://choir-public ./backup/choir-public
aws s3 sync s3://choir-private ./backup/choir-private

For a provider other than Amazon add --endpoint-url "$S3_ENDPOINT" to each command. The credentials for the tool need read access to both buckets, which can be a different key from the one the site uses. Turn on versioning or your provider's own replication if it offers it: it protects against deleted files, which sync does not.

MinIO from the Compose stack​

The MinIO service is not reachable from the host by default, so the simplest way is to run the MinIO command-line client on the stack's network. First list the network names with docker network ls; the one you want ends in _default.

export MINIO_ROOT_USER=choir MINIO_ROOT_PASSWORD='the value from your .env'
mkdir -p backup
docker run --rm --network choirmaster_default \
  -v "$PWD/backup":/backup -e MINIO_ROOT_USER -e MINIO_ROOT_PASSWORD \
  --entrypoint sh minio/mc -c '
    mc alias set local http://minio:9000 "$MINIO_ROOT_USER" "$MINIO_ROOT_PASSWORD" &&
    mc mirror local/choir-public /backup/choir-public &&
    mc mirror local/choir-private /backup/choir-private'

Change choirmaster_default to your network and the bucket names if you changed S3_PUBLIC_BUCKET or S3_PRIVATE_BUCKET. Typing the password into the shell leaves it in your shell history: put it in a file you read from instead if that matters on your machine.

Another option is to stop MinIO and copy its volume (minio-data) the way the single-server page copies a data volume. Restore it into the same version of MinIO.

Cloudflare​

A Worker has three kinds of data. You back up two of them.

D1, the database​

wrangler d1 export writes the whole database as a SQL file. Use the database_name from your wrangler.toml.

npx wrangler d1 export choir-db --remote --output=choir-db-$(date +%Y%m%d).sql

Cloudflare also keeps a rolling history of D1 databases (Time Travel) that you can restore from with npx wrangler d1 time-travel restore. Read Cloudflare's own documentation for how far back it goes on your plan, and use it as an extra safety net, not your only copy.

R2, the two file buckets​

Back up the buckets bound as PUBLIC_BUCKET and PRIVATE_BUCKET (named choir-public and choir-private in the example wrangler.toml). wrangler can fetch one named object (wrangler r2 object get) but cannot list or copy a whole bucket. For that, use R2's S3-compatible interface with a tool such as rclone. Create an R2 API token with read access (R2 > Manage R2 API Tokens in the Cloudflare dashboard), then:

export RCLONE_CONFIG_R2_TYPE=s3
export RCLONE_CONFIG_R2_PROVIDER=Cloudflare
export RCLONE_CONFIG_R2_ENDPOINT=https://YOUR_ACCOUNT_ID.r2.cloudflarestorage.com
export RCLONE_CONFIG_R2_ACCESS_KEY_ID=...
export RCLONE_CONFIG_R2_SECRET_ACCESS_KEY=...
rclone copy r2:choir-public ./backup/choir-public
rclone copy r2:choir-private ./backup/choir-private

Use copy rather than sync, so that a deletion never removes a file from your backup.

KV, the sessions​

If you use a KV namespace for sessions, there is nothing to back up. Sessions are temporary; if they are lost everyone logs in again.

Settings​

wrangler.toml is in your project folder: copy it. Secrets set with wrangler secret put cannot be read back, so keep your own copy of each value (admin password, email keys, LICENSE_KEY) in a password manager.

Schedule and keep copies away from the account​

Run these on a timer as in Back up a single-server site, and keep the copies in an account or machine that is not the one that holds the live data. Anyone who can delete your database can usually delete a backup in the same account.

Putting the copies back is on Restore a backup, or move to a new server.