Google Cloud SQL and AlloyDB

A collector is a small process that sits next to your database, reads its statistics, and sends them to DBGorilla. For Cloud SQL and AlloyDB the CLI deploys it for you as one Compute Engine instance in your own project, through Infrastructure Manager, under your own Google Cloud credentials. It reuses your Application Default Credentials, and nothing sensitive passes through DBGorilla.

The collector logs in to the database as its own service account with an IAM token, discovers read replicas or read pools from the primary, and reads instance metrics from Cloud Monitoring. Cloud SQL for PostgreSQL, Cloud SQL for MySQL and AlloyDB are covered. AlloyDB has no MySQL flavour.

If you would rather run the collector in GKE or on a VM you manage, the second half of this page, Without the CLI, gives the config and the manifests. The result is the same collector.

Do not run uninstall to fix an install error

uninstall deletes the collector and its identity for good. A reinstall creates a new collector. If install says a collector already exists, run dbgorilla collector status to see it, and dbgorilla collector stop if you want it paused. Only run uninstall when you want it gone.

Before you start

1. You are signed in. dbgorilla whoami should print your email. If not, see CLI Install and Setup.

2. Google Cloud credentials resolve. Run gcloud auth application-default login, or set GOOGLE_APPLICATION_CREDENTIALS. The project comes from --project, the credentials, GOOGLE_CLOUD_PROJECT, or gcloud’s active configuration, in that order.

3. A service account for the deploy. Infrastructure Manager runs the deployment as the account you name with --deploy-service-account. It needs roles/config.agent plus permission to create the collector’s instance group, its service account and its secrets.

4. Two patches on a fresh Cloud SQL instance. A new instance fails the preflight twice, and each failure names its fix. Run both now, because each one restarts the instance:

   gcloud sql instances patch prod-pg --database-flags=cloudsql.iam_authentication=on
   gcloud sql instances patch prod-pg --server-ca-mode=GOOGLE_MANAGED_CAS_CA

   The first turns on IAM database authentication, which is off by default. On MySQL the flag is spelled cloudsql_iam_authentication. The second moves the instance to a shared server CA; the default per-instance CA carries no hostname, so an IAM token cannot ride a verified connection to it. That change is one way. Read replicas do not inherit flags, so patch each replica as well. AlloyDB has the same requirement: alloydb.iam_authentication=on on the primary and every read pool instance.

   If you cannot change the CA mode, pass --db-password and the install uses password authentication over an encrypted, unverified connection instead.

5. pg_stat_statements loaded (PostgreSQL). On Cloud SQL and AlloyDB it is a database flag, cloudsql.enable_pg_stat_statements, and changing it restarts the instance, so add it to the patch above. Then in each database you will watch, run CREATE EXTENSION IF NOT EXISTS pg_stat_statements;. What you lose without it is on the overview.

6. A network the collector can live on. The collector instance joins the database’s VPC with a private IP only, so two things follow. The VPC needs a route to the internet, through Cloud NAT or equivalent, for the container image pull and the connection to DBGorilla; without it the deployment reports ACTIVE while the collector never starts. And the subnetwork needs Private Google Access on, or the boot script cannot reach Secret Manager or the registry. The CLI checks the second and prints the fix:

   gcloud compute networks subnets update <subnet> --region=<region> --enable-private-ip-google-access

   On a custom-mode VPC with several subnetworks in the database’s region, pass --subnetwork.

7. Your deployment supports collectors. Not all do. Check with:

   dbgorilla collector list

   If you get this deployment does not support the managed collector, stop here.

If your organisation sets sql.restrictPublicIp

Many Google organisations enforce that policy, and under it a Cloud SQL instance must be private IP only. A private IP instance needs private services access on the VPC: a reserved internal range and a Service Networking peering to Google’s producer network, set up once per VPC before the instance is created. The collector then reaches the instance at the private services access DNS name the instance publishes, which resolves only inside that VPC. If you are creating the instance for this, do the peering first; it cannot be added to an instance later without recreating it.

Install it

Start with a dry run. It renders the config and probes the template without creating anything, so a missing permission or a wrong subnetwork shows up before any resource exists:

dbgorilla collector install --target gcp --dry-run

Then deploy:

dbgorilla collector install --target gcp \
  --deploy-service-account projects/my-project/serviceAccounts/deployer@my-project.iam.gserviceaccount.com

The CLI finds your Cloud SQL instance or AlloyDB cluster, works out the networking, mints the collector’s identity, writes the server secret to Secret Manager, and deploys the published Terraform template through Infrastructure Manager as a one-instance regional managed instance group on Container-Optimized OS. The deployment is named dbgorilla-collector. Secrets never land in instance metadata or in the deployment inputs; the instance fetches them at boot with its own service account.

With several databases in the project, an interactive run offers a picker. Otherwise pass --db-instance-id: a Cloud SQL instance name, or an AlloyDB cluster or cluster/instance. Name the primary; replicas and read pools are discovered from it.

The template creates the collector’s own service account, and under IAM authentication that account is also the database user. The install finishes by printing two steps for a database administrator to run. For Cloud SQL:

gcloud sql users create dbg-collector@my-project.iam.gserviceaccount.com \
  --instance=prod-pg --type=cloud_iam_service_account

For AlloyDB the user is registered without the .gserviceaccount.com suffix:

gcloud alloydb users create dbg-collector@my-project.iam \
  --cluster=prod-alloydb --region=us-central1 --type=IAM_BASED

Then, connected to the primary as an administrator, the grants the CLI prints:

GRANT pg_monitor TO "dbg-collector@my-project.iam";
GRANT CONNECT ON DATABASE orders TO "dbg-collector@my-project.iam";
GRANT pg_read_all_data TO "dbg-collector@my-project.iam";

pg_read_all_data covers the per-schema USAGE and SELECT grants the overview walks through one at a time, so on Google Cloud the grant is three lines. Until they are run, the collector connects to DBGorilla and cannot log in to the database.

You can read the template before running anything. The CLI deploys the directory at gs://dbgorilla-collector-templates/collector/gce/v1.3/ and carries no copy of its own. To host it yourself, pass --template-source gs://…. If the address cannot be reached the install stops before creating anything.

Day-to-day

dbgorilla collector status      # deployment state, and whether it is connected
dbgorilla collector logs -f     # follow the container logs from Cloud Logging
dbgorilla collector stop        # pause it; the identity is kept
dbgorilla collector start       # resume
dbgorilla collector restart

stop scales the instance group to zero and start back to one. Both are reversible; the config, the secrets and the identity stay where they are.

status always exits 0

It returns success even when the collector is unreachable, its deployment is gone, or the control plane has never heard from it. Read the output, not the exit code. The two lines that matter are Deploy: (whether it is running at all) and Connection: (whether DBGorilla can see it; not yet seen by control plane means no). A deployment that reports ACTIVE with no connection usually means the VPC has no route to the internet.

logs reads Cloud Logging, so it needs valid Google Cloud credentials. If yours have expired, logs fails even though the collector is fine.

Two things the gcp target cannot do yet. Changing which databases a collector watches in place, and dbgorilla collector upgrade. Both are dbgorilla collector uninstall followed by a fresh install, which is a new collector with a new identity.

Removing it

dbgorilla collector uninstall

This deletes the Infrastructure Manager deployment and waits for its resources to go, removes the secrets the CLI wrote to Secret Manager, deprovisions the identity on the server, and clears the local config. It asks for confirmation first. If it is interrupted mid-way, the identity has not been deprovisioned: check dbgorilla collector status and run uninstall again.

Without the CLI

Everything below deploys the same collector by hand, for a GKE cluster or a VM you already manage. You provide the identity and the config the CLI would have written.

A collector identity. In the DBGorilla app, open Collectors, New Collector. It issues an agent ID, a tenant ID and a secret. The secret is shown once.

A service account for the collector, with roles/monitoring.viewer for the metrics, plus roles/cloudsql.client and roles/cloudsql.instanceUser for Cloud SQL, or roles/alloydb.client and roles/alloydb.databaseUser for AlloyDB. The collector picks up the account from wherever it runs: GKE Workload Identity, a service account attached to the VM, or GOOGLE_APPLICATION_CREDENTIALS. Register it as a database user and grant it exactly as in Install it above.

The config

Cloud SQL for PostgreSQL:

[dbgorilla]
agent_id  = "<agent id from the app>"
tenant_id = "<tenant id from the app>"
secret    = { env = "DBG_SERVER_SECRET" }

[topology]
interval = "60s"

[commands]
enabled = false

[[component]]
name   = "orders"
engine = "postgres"

[component.provider]
type     = "cloud_sql"
project  = "my-project"
region   = "us-central1"
instance = "prod-pg"                      # the primary; replicas are discovered from it

[component.auth]
method = "gcp_iam"
user   = "dbg-collector@my-project.iam"   # the service account email without .gserviceaccount.com

[component.connect]
host      = "<dns-name>.us-central1.sql-psa.goog."   # the instance's private services access name, trailing dot included
port      = 5432
databases = ["orders"]
ssl_mode  = "verify-full"

Cloud SQL for MySQL is the same block with engine = "mysql", port = 3306, and ssl_mode = "verify_identity", which is MySQL’s spelling of the same thing.

AlloyDB has no host. The collector asks the Admin API where each instance is:

[[component]]
name   = "orders"
engine = "postgres"

[component.provider]
type     = "alloydb"
project  = "my-project"
region   = "us-central1"
cluster  = "prod-alloydb"
instance = "prod-alloydb-primary"         # the primary; read pools are discovered from it

[component.auth]
method = "gcp_iam"
user   = "dbg-collector@my-project.iam"
scopes = ["https://www.googleapis.com/auth/alloydb.login"]

[component.connect]
databases = ["orders"]

Three things the collector refuses at startup, each with the fix in the message. A user that still carries .gserviceaccount.com. An AlloyDB IAM block without the login scope. And a Cloud SQL instance that is itself a read replica: name the primary and the replicas come with it.

The CLI renders exactly this config; dbgorilla collector install --target gcp --dry-run prints it for your instance and is the quickest way to get the host and IDs right.

Cloud Monitoring reads count against your project

The collector polls Cloud Monitoring once a minute per instance. Those are API reads on your project, like any other Cloud Monitoring client. To poll less often, set poll_interval under [component.provider.monitoring]. The default leaves Cloud Monitoring time to publish, since its data runs a few minutes behind.

Certificates

Cloud SQL has two server certificate modes and only one of them works with IAM login by default.

With GOOGLE_MANAGED_CAS_CA, every instance’s certificate names its DNS name, so verify-full checks the hostname and the connection is verified end to end. This is the mode to use.

With the default GOOGLE_MANAGED_INTERNAL_CA, the certificate carries no DNS name at all, so there is nothing for verify-full to check. The collector will not send an IAM token over that connection unless you accept it in writing:

[component.connect]
ssl_mode = "require"
allow_unverified_token_transport = true

That is encrypted and unverified, with your VPC as the boundary. It never extends to disable.

AlloyDB presents a self-signed certificate that no authority can verify, so ssl_mode defaults to require there and the same unverified hop is implied. To verify it anyway, capture the certificate from inside the VPC and pin it:

openssl s_client -starttls postgres -showcerts -connect <ip>:5432 </dev/null

Pin the issuing CA from the chain, not the leaf. AlloyDB rotates instance leaves, and a pinned leaf breaks at the next rotation. Mount the file and set ssl_mode = "verify-ca" with ca_cert pointing at it. The collector checks every pinned certificate on each discovery pass, warns thirty days before one expires, and refuses a component whose pins have all expired rather than running unverified.

Running it in GKE

Use the Helm chart from the Kubernetes page. The Google-specific part is Workload Identity: bind the collector’s Kubernetes ServiceAccount to the Google service account, and annotate it so the token is available to the pod.

values.yaml:

serviceAccount:
  annotations:
    iam.gke.io/gcp-service-account: dbg-collector@my-project.iam.gserviceaccount.com

secrets:
  serverSecret: "<secret from the app>"

No database password goes in secrets.databasePasswords with IAM login. Then:

helm install dbg-collector oci://dbgorillapublic.azurecr.io/charts/dbg-collector \
  --namespace dbg-collector --create-namespace \
  --values ./values.yaml \
  --set-file config.inline=./collector.toml

The chart makes no Service and opens no inbound port. The pod needs the instance on 5432 or 3306, and otlp.dbgorilla.com plus auth.dbgorilla.com on 443.

Running it on a VM

Attach the service account to the VM, then with collector.toml in the current directory:

docker run -d --name dbg-collector --restart unless-stopped \
  -v "$PWD/collector.toml:/etc/dbg-collector/collector.toml:ro" \
  -e DBG_SERVER_SECRET="<secret from the app>" \
  dbgorillapublic.azurecr.io/dbg-collector:0.9.0 \
  --config-file /etc/dbg-collector/collector.toml

Cloud SQL and AlloyDB need collector 0.9.0 or later. Pin a tag rather than latest.

Check it worked

dbgorilla collector status                              # CLI install
kubectl -n dbg-collector logs -f deploy/dbg-collector   # GKE
docker logs -f dbg-collector                            # VM

Four lines say it is healthy: opamp websocket connected, opamp handshake complete, otelcol started, discovered component.

Then look at what it found. A primary with two read replicas should show three nodes, one writer and two readers, each with Cloud Monitoring metrics. A Cloud SQL high availability standby is not a node: it has no endpoint and publishes no metrics, so a regional instance with no replicas shows one node, and that is correct.

The failures you are most likely to see are named in the log. A replica reported unreachable almost always means the IAM flag was set on the primary and not on the replica. A refusal naming GOOGLE_MANAGED_INTERNAL_CA means the instance is on the default certificate mode; switch it, or accept the unverified transport as described above. A metrics plane that stays empty means the service account is missing roles/monitoring.viewer.