# Google Cloud SQL and AlloyDB Set up the collector for Cloud SQL for PostgreSQL, Cloud SQL for MySQL, or AlloyDB. AlloyDB has no MySQL flavour. The CLI deploys the collector as one Compute Engine instance in your own project, through Infrastructure Manager, using your own Google Cloud credentials. It reuses your Application Default Credentials, and no sensitive data passes through DBGorilla. To run the same collector in GKE or on a VM you manage, use [Without the CLI](#without-the-cli). 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. :::danger[Do not run `uninstall` to fix an install error] See [Manage collectors](/docs/administer/manage-collectors/#your-machine-docker) for what `uninstall` destroys and what to run instead. ::: ## Before you start 1. Sign in to the DBGorilla CLI. If you have not, see [CLI Install and Setup](/docs/getting-started/cli-install-and-setup/). 2. Make your 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. Choose a service account for the deployment. Infrastructure Manager runs the deployment as the account named with `--deploy-service-account`. It needs `roles/config.agent` and permission to create the collector's instance group, service account, and secrets. 4. Patch a fresh Cloud SQL instance twice. A new instance fails the preflight twice, and each failure names its fix. Each patch restarts the instance, so run both now: ```sh 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 use a verified connection to it. The CA change cannot be undone. 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`. The install then uses password authentication over an encrypted, unverified connection. 5. Load `pg_stat_statements` (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 under [pg_stat_statements](/docs/connect/prepare-postgresql/#pg_stat_statements). 6. Prepare the network. The collector instance joins the database's VPC with a private IP only. The VPC needs a route to the internet through Cloud NAT or an equivalent service for the container image pull and the connection to DBGorilla. Without it, the deployment reports ACTIVE while the collector never starts. The subnetwork needs Private Google Access enabled, or the boot script cannot reach Secret Manager or the registry. The CLI checks Private Google Access and prints the fix: ```sh gcloud compute networks subnets update --region= --enable-private-ip-google-access ``` On a custom-mode VPC with several subnetworks in the database's region, pass `--subnetwork`. 7. Check that your deployment supports collectors: ```sh dbgorilla collector list ``` If you get `this deployment does not support the managed collector`, stop here. :::note[If your organisation sets `sql.restrictPublicIp`] Under that policy, 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 ### With the CLI Start with a dry run. It renders the config and probes the template without creating anything, so missing permissions or a wrong subnetwork appear before any resource exists: ```sh dbgorilla collector install --target gcp --dry-run ``` Then deploy: ```sh 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, determines the networking, creates the collector's identity, and writes the server secret to Secret Manager. It then deploys the published Terraform template through Infrastructure Manager as a one-instance regional managed instance group on Container-Optimized OS, named `dbgorilla-collector`. Secrets never land in instance metadata or 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. On Cloud SQL, the collector's service account can use IAM database login only on that instance and its read replicas; the install prints the scope. `--allow-project-wide-login` lifts it. AlloyDB's login role cannot be narrowed this way, so on AlloyDB the grant is project-wide. Either way, the account only logs in where it has been registered as a database user. #### Register the database user The template creates the collector's own service account. Under IAM authentication, that account is also the database user. The install ends by printing two steps for a database administrator to run. For Cloud SQL: ```sh 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: ```sh 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, run the grants the CLI prints: ```sql 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 that [Prepare PostgreSQL](/docs/connect/prepare-postgresql/#database-grants) lists one at a time, so on Google Cloud the grant is three lines. Until they are run, the collector connects to DBGorilla but cannot log in to the database. Those grants are for PostgreSQL. For Cloud SQL for MySQL, use the grants on [MySQL](/docs/connect/prepare-mysql/#database-grants). #### The template You can read the template before running anything. The CLI deploys the directory at `gs://dbgorilla-collector-templates/collector/gce/v1.5/` 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. ### Without the CLI This deploys the same collector by hand in a GKE cluster or on a VM you already manage. You provide the identity and config the CLI would have written: - A collector identity. Create one as in [What you need first](/docs/connect/infrastructure-as-code/#what-you-need-first). - 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. Bind the Cloud SQL login role with an IAM Condition naming the instances it may log in to, as the CLI does: `resource.type == "sqladmin.googleapis.com/Instance" && resource.name == "projects//instances/"` (one clause per instance, replicas included). AlloyDB's login role cannot be conditioned. The collector picks up the account wherever it runs: through GKE Workload Identity, a service account attached to the VM, or `GOOGLE_APPLICATION_CREDENTIALS`. Register the service account as a database user and grant it exactly as in [Register the database user](#register-the-database-user). #### The config Cloud SQL for PostgreSQL: ```toml [dbgorilla] agent_id = "" tenant_id = "" secret = { env = "DBG_SERVER_SECRET" } [topology] interval = "60s" [commands] enabled = true allowed = ["explain", "collect_statistics"] # see https://www.dbgorilla.com/docs/reference/collector-commands/#database-commands [[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 = ".us-central1.sql-psa.goog." # the instance's private services access name, trailing dot included port = 5432 databases = ["orders"] ssl_mode = "verify-full" ``` The `[commands]` block allows `explain` and `collect_statistics`. To turn either off, remove it from `allowed` or set `enabled = false`, then restart the collector. See [Database commands](/docs/reference/collector-commands/#database-commands). Cloud SQL for MySQL uses the same block with `engine = "mysql"`, `port = 3306`, and `ssl_mode = "verify_identity"`, which is MySQL's spelling of the same setting. Its complete file is under [Cloud SQL for MySQL](#cloud-sql-for-mysql). The AlloyDB file is under [AlloyDB](#alloydb). The collector refuses three things at startup, and each message names the fix: - A `user` that still carries `.gserviceaccount.com`. - An AlloyDB IAM block without the login scope. - 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. To get the host and IDs right, run `dbgorilla collector install --target gcp --dry-run`, which prints it for your instance. :::note[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]`. Cloud Monitoring data runs a few minutes behind, and the default leaves time for it to publish. ::: #### Certificates Cloud SQL has two server certificate modes, and only one 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. Use this mode. With the default `GOOGLE_MANAGED_INTERNAL_CA`, the certificate carries no DNS name, so `verify-full` has nothing to check. The collector sends an IAM token over that connection only if you accept it explicitly in the config: ```toml [component.connect] ssl_mode = "require" allow_unverified_token_transport = true ``` The connection is encrypted and unverified, with your VPC as the boundary. This setting 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: ```sh openssl s_client -starttls postgres -showcerts -connect :5432 " ``` With IAM login, no database password goes in `secrets.databasePasswords`. Then: ```bash 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 pod needs to reach the instance on 5432 or 3306, and `otlp.dbgorilla.com` and `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: ```bash docker run -d --name dbg-collector --restart unless-stopped \ -v "$PWD/collector.toml:/etc/dbg-collector/collector.toml:ro" \ -e DBG_SERVER_SECRET="" \ dbgorillapublic.azurecr.io/dbg-collector:0.14.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`. #### Cloud SQL for MySQL The Cloud SQL block with MySQL's engine, port, and `ssl_mode`. The IAM flag on MySQL is spelled `cloudsql_iam_authentication`. ```toml [dbgorilla] agent_id = "" tenant_id = "" secret = { env = "DBG_SERVER_SECRET" } [topology] interval = "60s" [commands] enabled = true allowed = ["explain", "collect_statistics"] # see https://www.dbgorilla.com/docs/reference/collector-commands/#database-commands [[component]] name = "orders" engine = "mysql" [component.provider] type = "cloud_sql" project = "my-project" region = "us-central1" instance = "prod-mysql" # 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 = ".us-central1.sql-psa.goog." # the instance's private services access name, trailing dot included port = 3306 databases = ["orders"] ssl_mode = "verify_identity" ``` Run it as in [Running it in GKE](#running-it-in-gke) or [Running it on a VM](#running-it-on-a-vm). #### AlloyDB AlloyDB for PostgreSQL has no `host`. The collector asks the AlloyDB Admin API where each instance is and finds read pools from the primary. ```toml [dbgorilla] agent_id = "" tenant_id = "" secret = { env = "DBG_SERVER_SECRET" } [topology] interval = "60s" [commands] enabled = true allowed = ["explain", "collect_statistics"] # see https://www.dbgorilla.com/docs/reference/collector-commands/#database-commands [[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"] ``` Run it as in [Running it in GKE](#running-it-in-gke) or [Running it on a VM](#running-it-on-a-vm). To pin AlloyDB's certificate, see [Certificates](#certificates). ## Check it is working Follow [Check it worked](/docs/connect/check-it-is-working/#google-cloud) for Google Cloud. ## Upgrade Updating in place and `dbgorilla collector upgrade` are under [Day-to-day](/docs/administer/manage-collectors/#google-cloud) in Manage collectors. ## Remove What `dbgorilla collector uninstall` deletes is under [Removing it](/docs/administer/manage-collectors/#google-cloud) in Manage collectors. ## Next To manage this deployment from Terraform, see [Deploying from Terraform](/docs/connect/infrastructure-as-code/#google-cloud).