Encrypted Google Drive Backups

The host backup tooling sends WordPress archives, Tier-1 logical database sets, and PostgreSQL physical snapshots through rclone crypt. WordPress runs on the last day of each month at 03:00 UTC and keeps four archives per site. Logical databases run every Sunday at 03:00 UTC, keeping three local and twelve remote sets. PostgreSQL physical snapshots run on the first Sunday of each month at 04:00 UTC, keeping two local and four remote snapshots.

Repository hardening and production activation are complete. Both OAuth/crypt destinations, complete four-site and active-engine workflows, encrypted canaries, logical restores, and the first PostgreSQL physical snapshot/download/isolated restore are validated. The weekly database, last-day WordPress, and first-Sunday physical timers are enabled/active; both offsite flags are true. MSSQL remains intentionally excluded at 0/0, and the weekly logical cadence remains below the documented one-hour RPO.

Configuration contractCopy Anchor Link

Host-local configuration must use:

/etc/contabo-backups/             root:root 0700
├── backup.env                    root:root 0600
└── rclone.conf                   root:root 0600 and writable

backup.env contains schedules, destinations, retention, transfer limits, and the RCLONE_CONFIG path. Start from runtime/environments/dev/infrastructure/backups/backups.dev.env.

rclone.conf contains OAuth client credentials, refresh tokens, and crypt passwords. It must never be committed, copied into evidence, or pasted into chat. It remains writable because rclone persists refreshed OAuth token state.

Prepare the files without adding credentials:

sudo operations/backups/install-backup-config.sh --dry-run
sudo operations/backups/install-backup-config.sh

The second command is a VPS mutation and requires explicit approval.

Initial Google Drive authorizationCopy Anchor Link

The initial personal My Drive uses OAuth, not a service account. Required inputs are:

1. Google Drive API enabled in the central Google Cloud project.
2. An External OAuth consent screen published to Production.
3. An OAuth Desktop client ID and client secret.
4. One-time consent from the Google account that owns the destination Drive.
5. A generated crypt password and separate crypt salt stored in an offline password manager.

Configure directly on the VPS so secrets never enter the repository:

sudo rclone config --config /etc/contabo-backups/rclone.conf

Create contabo_drive as a Google Drive remote with scope drive.file. Then create contabo_crypt as a crypt remote wrapping contabo_drive:Contabo with:

filename_encryption = off
directory_name_encryption = false

Rclone creates the Contabo root. The drive.file scope may not see a root folder that was created manually through the Drive web UI. Backup contents are encrypted; readable folder and archive names appear with .bin suffixes in the underlying Drive.

Configure the Gorsi account independently as gorsistudio_drive, using its own OAuth client ID, client secret, and account authorization. Wrap gorsistudio_drive:Contabo with gorsistudio_crypt using a separate crypt password and salt. The Gorsi credentials and crypt material follow the same root-only handling rules.

Destination modelCopy Anchor Link

Each site registry under operations/backups/sites.d/ contains no credentials. It selects a crypt remote and a path:

SITE_NAME=dbskc.com
WP_VOLUME=perspective-v_dbskc_data
DB_NAME=dbskc_db
RCLONE_REMOTE=contabo_crypt
REMOTE_PATH=wordpress/dbskc.com

The initial Drive hierarchy is:

Contabo/
├── wordpress/<domain>/
├── databases/tier1-<UTC timestamp>/
├── databases/postgres-physical/postgres-physical-<UTC timestamp>/
└── services/

The four-site scope is dbskc.com, gorsistudio.com, store.gorsistudio.com, and wcblahore.pk. The Gorsi sites route through gorsistudio_crypt; the other two sites, all shared Tier-1 sets, and all PostgreSQL physical snapshots remain central. Nishat Colony and new.nishatcolony.pk are outside backup scope. Never route a complete shared database set to a client Drive.

Backup behaviorCopy Anchor Link

For each WordPress site the helper:

1. Resolves the active database_mysql Swarm task, with Compose fallback for DEV.
2. Refuses missing or empty Docker volumes.
3. Authenticates to MySQL and confirms the configured database exists, including dry-runs.
4. Reads the MySQL root password from the mounted Swarm secret.
5. Dumps the site's database with GTID statements disabled and archives its external WordPress volume read-only.
6. Writes SHA-256 metadata and creates <domain>-<UTC timestamp>.tar.gz.
7. Uploads through the site's crypt remote and verifies with rclone cryptcheck.
8. Prunes only after verification and retains failed local staging for investigation.

The weekly database helper discovers every connectable non-template PostgreSQL database, including postgres, and writes one collision-safe custom dump per database plus compressed globals and PostgreSQL metadata. It also backs up every non-system MySQL database with GTID statements disabled and the MongoDB cluster. It uploads only when DB_OFFSITE_ENABLED=true, verifies before retention, and preserves partial or complete local staging if source backup, upload, or verification fails.

The monthly PostgreSQL helper uses the running container's native pg_basebackup with included WAL, tar/gzip output, spread checkpoints, a SHA-256 native backup manifest, and a 16 MiB/s default source limit. It rejects unsupported WAL settings, external tablespaces, or an unexpected PostgreSQL 18 data layout. It records non-secret image, version, volume, ownership, and database-count metadata and uploads only when POSTGRES_PHYSICAL_OFFSITE_ENABLED=true.

For the current production activation, MSSQL remains intentionally scaled to 0/0 and is excluded through DB_ENGINES=postgres,mysql,mongodb. MSSQL support remains in the generic helper and must return to scheduled coverage if that service is re-enabled.

All three workflows use /run/lock/contabo-backups.lock with a six-hour bounded wait, one rclone transfer, two checkers, and an 8 MiB/s cap by default.

DEV validation and VPS executionCopy Anchor Link

DEV-safe previews:

operations/tests/backup-scripts.test.sh
operations/systemd/wp-gdrive-backup/install.sh \
  --env-file runtime/environments/dev/infrastructure/backups/backups.dev.env --dry-run
operations/systemd/tier1-db-backup/install.sh \
  --env-file runtime/environments/dev/infrastructure/backups/backups.dev.env --dry-run
operations/systemd/postgres-physical-backup/install.sh \
  --env-file runtime/environments/dev/infrastructure/backups/backups.dev.env --dry-run

Approval-gated VPS workflow:

operations/backups/wp-gdrive-backup.sh run --site dbskc.com --dry-run
operations/backups/wp-gdrive-backup.sh run --site dbskc.com
operations/backups/db-backup.sh upload --dry-run
operations/backups/db-backup.sh upload
operations/backups/postgres-physical-backup.sh run --offsite --dry-run
operations/backups/postgres-physical-backup.sh run --offsite

For a future revalidation or rollback window, disable WordPress before manual testing and re-enable it only after the four-site run passes. Install/re-enable the physical timer only after a snapshot passes an isolated physical restore:

sudo operations/systemd/wp-gdrive-backup/install.sh
sudo operations/systemd/tier1-db-backup/install.sh
sudo operations/systemd/postgres-physical-backup/install.sh

Safe diagnosticsCopy Anchor Link

sudo operations/diagnostics/backup-diagnostics.sh
journalctl -u wp-gdrive-backup.service --no-pager -n 200
journalctl -u tier1-db-backup.service --no-pager -n 200
journalctl -u postgres-physical-backup.service --no-pager -n 200

The diagnostic output contains configuration paths, modes, remote names, connectivity, quota, timer state, and sanitized evidence only. Do not use commands that dump the process environment or unredacted rclone configuration during troubleshooting.

Restore validationCopy Anchor Link

Download through the crypt remote, never through the underlying Drive remote:

rclone --config /etc/contabo-backups/rclone.conf copy \
  contabo_crypt:wordpress/<domain>/<archive>.tar.gz ./restore/

Extract the archive, verify db.sql.gz and files.tar.gz against manifest.csv, then restore into a scratch database and scratch Docker volume. Use operations/restore/postgres-restore.sh for guarded logical or physical PostgreSQL validation. It requires scratch-prefixed targets, refuses databases_postgres-data, publishes no ports for physical restores, and never updates the Swarm service. Production restore or service restart requires separate approval. Preserve the rclone configuration and crypt password/salt outside both the VPS and Google Drive; losing them makes encrypted backups unrecoverable.