BackupRestore

A containerised Python job that copies a Cloud SQL for PostgreSQL instance from one GCP project to another with Cloud SQL's own backup and restore, then checks that the copy matches the source.

It was written in Dec 2023 – Jan 2024 by a four-person consulting team, me included, to move a client's Cloud SQL instances between GCP projects. It's published here with all client details (names, project IDs, instance names, IPs, bucket names) replaced by placeholders, and with a fresh git history.

How it works

src/main.py runs one source → target instance pair through four steps:

1. Backup and restore (backuprestore.py): runs gcloud sql backups create on the source instance and takes the newest backup ID. It then calls the Cloud SQL Admin API restoreBackup on the target instance and polls gcloud sql operations list until the RESTORE_VOLUME operation is DONE.
2. Inventory the source (datavalidation.py): lists the instance's databases with gcloud and connects to each one with psycopg2. For every table it records the size, row count, column count, column types, foreign keys and indexes, plus the database size. The result is written as a CSV to gs://$BUCKET_NAME/<project>/<instance>.csv.
3. Inventory the target the same way.
4. Compare (datacomparison.py): reads both CSVs back from the bucket and diffs them line by line. Any mismatch is logged, followed by a final "identical / not identical" result.

Logs are written as one JSON object per line (JSONFormatter.py), so Cloud Logging can parse them on GKE.

.
├── Dockerfile            # Ubuntu 22.04 + Python + psql client + gcloud CLI
├── requirements.txt
├── .env.example          # every setting the job reads
├── k8s/
│   ├── README.md         # GKE setup: Workload Identity, Artifact Registry, Secret, Job
│   ├── service_account.yaml
│   └── example-job.yaml  # one Kubernetes Job = one instance pair
└── src/
    ├── main.py           # orchestrates the four steps
    ├── backuprestore.py
    ├── datavalidation.py
    ├── datacomparison.py
    └── JSONFormatter.py

Running it

On GKE (how it was used): see k8s/README.md. You build the image, push it to Artifact Registry, put the DB credentials in a Kubernetes Secret and apply one Job per instance pair. The pod gets its GCP permissions through Workload Identity: roles/cloudsql.admin on both projects and roles/storage.objectUser on the bucket.

Locally: you need gcloud signed in with the same permissions, network access to both instances on port 5432, and an existing target instance.

pip install -r requirements.txt
cp .env.example .env        # fill in real values
set -a; . ./.env; set +a
cd src && python3 main.py

Variable	Meaning
SOURCE_PROJECT_ID / TARGET_PROJECT_ID	GCP projects of the source and target instances
SOURCE_INSTANCE_NAME / TARGET_INSTANCE_NAME	Cloud SQL instance names
SOURCE_DB_HOST / TARGET_DB_HOST	IP addresses the job connects to with psycopg2
DB_USER / DB_PASSWORD	PostgreSQL credentials, which must be the same on both instances
BUCKET_NAME	GCS bucket for the inventory CSVs

Limitations

• The restore overwrites the target instance. The target has to exist already, with a compatible version and enough storage.
• One run handles one instance pair. Migrating many instances means running many Jobs.
• The comparison is a plain line-by-line text diff of the two CSVs. It assumes both have the same number of lines in the same order.
• Table names are put into the SQL with f-strings and gcloud commands run with shell=True. Both inputs come from information_schema or the job's own settings, not from end users, but they aren't parameterised.
• Tables outside the default search_path (non-public schemas) aren't handled reliably.
• The "null" check only finds rows where every column is NULL.
• If the restore request fails, the error is logged but the job still waits for a restore operation.
• There are no automated tests, and the dependencies in requirements.txt are unpinned.