--- name: gcp-alloydb description: | Provision and manage AlloyDB for PostgreSQL clusters and instances on Google Cloud — a managed, PostgreSQL-compatible database with disaggregated compute/storage, columnar engine, and AlloyDB AI for vector search and natural-language queries. Use for HA clusters, read pools, IAM database auth, and Terraform-managed AlloyDB deployments. license: Apache-2.0 compatibility: 'gcloud-cli, terraform, psql, AlloyDB Auth Proxy' metadata: author: google-cloud version: 1.1.0 category: devops repository: https://github.com/GoogleCloudPlatform/alloydb-auth-proxy tags: "gcp, alloydb, postgres, database, vector-search" --- # GCP AlloyDB ## Overview AlloyDB is Google Cloud's managed PostgreSQL-compatible database. It separates compute and storage so they scale independently, runs an analytics-grade columnar engine alongside the transactional rows, and ships with AlloyDB AI — vector search, hybrid search, and model-endpoint management for building RAG and semantic-search apps directly against the database. ## Instructions ### Core Concepts - **Cluster** — the top-level resource that owns storage, backups, and configuration shared across instances - **Primary instance** — the read/write instance; one per cluster - **Read pool instance** — horizontally scalable read replicas backed by the same storage - **Secondary cluster** — cross-region replica for DR - **Continuous backup** — point-in-time recovery within the recovery window (14 days by default; `--continuous-backup-recovery-window-days` changes it) - **AlloyDB AI** — `vector` and `alloydb_scann` extensions, `google_ml_integration` for embeddings and model endpoints, hybrid search ### Prerequisites ```bash # Enable APIs gcloud services enable alloydb.googleapis.com \ servicenetworking.googleapis.com \ compute.googleapis.com # Reserve a private IP range for VPC peering (one-time per VPC) gcloud compute addresses create alloydb-peering \ --global --purpose=VPC_PEERING --prefix-length=16 \ --network=default gcloud services vpc-peerings connect \ --service=servicenetworking.googleapis.com \ --ranges=alloydb-peering --network=default ``` ### Creating a Cluster and Primary Instance ```bash # Create a cluster (storage + config; no compute yet). # --password sets the initial `postgres` user; keep it in Secret Manager, not in shell history. gcloud alloydb clusters create prod-cluster \ --region=us-central1 \ --network=default \ --password="$ALLOYDB_POSTGRES_PASSWORD" \ --automated-backup-days-of-week=MONDAY,THURSDAY \ --automated-backup-start-times=02:00 \ --automated-backup-retention-count=14 ``` ```bash # Create the primary instance (the actual compute) gcloud alloydb instances create prod-primary \ --cluster=prod-cluster \ --region=us-central1 \ --instance-type=PRIMARY \ --cpu-count=4 \ --availability-type=REGIONAL \ --database-flags="alloydb.iam_authentication=on" ``` For production, prefer **IAM database authentication** over password auth — it removes static credentials entirely and integrates with service accounts. The flag is `alloydb.iam_authentication` (`cloudsql.iam_authentication` is Cloud SQL's); group login also needs `alloydb.iam_group_authentication=on`. Then add the account to the cluster (`gcloud alloydb users create app-sa@my-project.iam --cluster=prod-cluster --region=us-central1 --type=IAM_BASED`) and `GRANT` it database privileges. The `vector` extension needs no flag: just `CREATE EXTENSION`. ### Read Pool for Scaling Reads ```bash # Add a read pool (e.g., for analytics or reporting traffic) gcloud alloydb instances create reports-pool \ --cluster=prod-cluster \ --region=us-central1 \ --instance-type=READ_POOL \ --read-pool-node-count=3 \ --cpu-count=4 ``` ### Connecting from an Application ```bash # Run the Auth Proxy locally (handles IAM + TLS). Download the binary from the URL on the # AlloyDB docs page "Connect using the Auth Proxy" (v1.17.2 at time of writing), e.g. # wget https://storage.googleapis.com/alloydb-auth-proxy/v1.17.2/alloydb-auth-proxy.linux.amd64 -O alloydb-auth-proxy && chmod +x alloydb-auth-proxy # Google publishes no checksum for it; use only that HTTPS URL and pin the version. # --auto-iam-authn logs in with your IAM identity; add --public-ip only for instances with a public address. ./alloydb-auth-proxy --auto-iam-authn --port 5432 \ projects/my-project/locations/us-central1/clusters/prod-cluster/instances/prod-primary ``` ```python # pip install "google-cloud-alloydb-connector[pg8000]" sqlalchemy # Python connection using the AlloyDB connector + IAM auth from google.cloud.alloydb.connector import Connector, IPTypes import sqlalchemy connector = Connector() def getconn(): return connector.connect( "projects/my-project/locations/us-central1/clusters/prod-cluster/instances/prod-primary", "pg8000", user="app-sa@my-project.iam", db="orders", enable_iam_auth=True, ip_type=IPTypes.PRIVATE, ) engine = sqlalchemy.create_engine("postgresql+pg8000://", creator=getconn) with engine.connect() as conn: result = conn.execute(sqlalchemy.text("SELECT current_database(), version()")) print(result.fetchone()) ``` ### AlloyDB AI — Vector Search ```sql -- Enable extensions (once per database; alloydb_scann installs vector too) CREATE EXTENSION IF NOT EXISTS alloydb_scann CASCADE; CREATE EXTENSION IF NOT EXISTS google_ml_integration; GRANT EXECUTE ON FUNCTION embedding TO "app-sa@my-project.iam"; -- Schema with embeddings (text-embedding-005 returns 768 dimensions) CREATE TABLE products ( id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL, description TEXT, embedding vector(768) ); -- Backfill with a Vertex AI model; embedding() works without registering an endpoint UPDATE products SET embedding = embedding('text-embedding-005', description)::vector WHERE embedding IS NULL; -- ScaNN index: default is auto-tuned (needs 10,000+ rows, or add WITH (mode='AUTO') to defer the build) CREATE INDEX products_embedding_idx ON products USING scann (embedding cosine); -- Manual tuning: WITH (mode = 'MANUAL', num_leaves = 1000, quantizer = 'SQ8') -- Hybrid search: semantic ranking plus a lexical filter SELECT id, name, 1 - (embedding <=> embedding('text-embedding-005', 'wireless headphones with noise cancellation')::vector) AS similarity FROM products WHERE description ILIKE '%bluetooth%' ORDER BY embedding <=> embedding('text-embedding-005', 'wireless headphones with noise cancellation')::vector LIMIT 10; ``` `textembedding-gecko@003` (used by older copies of this skill) is not in the current model lists; use `text-embedding-005` (768 dims) or `gemini-embedding-001` (3072 dims by default, so size the column to match). The cluster's AlloyDB service agent needs access to Vertex AI (see "Integrate your database with Vertex AI" in the docs), and the model must be available in the cluster's region. To pass task types or use OpenAI-style endpoints, register a model with `CALL google_ml.create_model(...)` and call `google_ml.embedding(model_id => ..., content => ...)`. ### Cross-Region DR with Secondary Cluster ```bash # Create a read-only secondary cluster in another region # (then add its instance with `gcloud alloydb instances create-secondary dr-primary --cluster=dr-cluster --region=us-east1`) gcloud alloydb clusters create-secondary dr-cluster \ --region=us-east1 \ --primary-cluster=projects/my-project/locations/us-central1/clusters/prod-cluster ``` ```bash # Failover (promote the secondary cluster to a standalone read/write cluster) gcloud alloydb clusters promote dr-cluster --region=us-east1 ``` ### Terraform ```hcl resource "google_alloydb_cluster" "prod" { cluster_id = "prod-cluster" location = "us-central1" network_config { network = data.google_compute_network.default.id } initial_user { user = "postgres" password = var.alloydb_password } deletion_protection = true # the provider's default; set false only to tear down a dev cluster automated_backup_policy { location = "us-central1" backup_window = "1800s" enabled = true weekly_schedule { days_of_week = ["MONDAY", "THURSDAY"] start_times { hours = 2 } } quantity_based_retention { count = 14 } } } resource "google_alloydb_instance" "primary" { cluster = google_alloydb_cluster.prod.name instance_id = "prod-primary" instance_type = "PRIMARY" machine_config { cpu_count = 4 } availability_type = "REGIONAL" database_flags = { "alloydb.iam_authentication" = "on" } # In Terraform, add depends_on = [google_service_networking_connection.private_vpc] so the # private service access exists before the cluster is created. } ``` ## Examples ### Example 1 — Set up a production cluster with read replicas User asks for a production-ready AlloyDB deployment. Create the cluster with regional availability, configure automated backups with 14-day retention, provision a primary instance with IAM auth enabled, then add a 3-node read pool sized for the analytics workload. Hand the user the Auth Proxy command (`--auto-iam-authn`) and a Terraform module they can commit. Expected result: `gcloud alloydb instances list --cluster=prod-cluster --region=us-central1` shows `prod-primary` and `reports-pool` as READY. ### Example 2 — Add semantic search to an existing PostgreSQL app User has a products table on AlloyDB and wants vector search. Enable `alloydb_scann` (which pulls in `vector`) and `google_ml_integration`, add an `embedding vector(768)` column, backfill embeddings using `embedding('text-embedding-005', description)::vector`, create a ScaNN index with `USING scann (embedding cosine)`, and rewrite the search endpoint to combine cosine distance with existing `WHERE` filters for hybrid retrieval. ## Guidelines - Use **IAM database authentication** in production — service accounts authenticate to the database without storing passwords - Set `availability-type=REGIONAL` for HA; `ZONAL` only for dev/test - Keep the Auth Proxy in your container or sidecar — never expose AlloyDB to the public internet - Read pools are the right scaling lever before considering bigger primary CPUs - For vector workloads, prefer ScaNN with its default auto-tuning; for manual tuning, `num_leaves` around sqrt(rows) is the documented starting point for a two-level index. Increase `scann.num_leaves_to_search` at query time for recall - Use secondary clusters for DR; promotion is a manual step, and plan how applications are repointed to the promoted cluster - Continuous backup gives PITR within the recovery window — separate from automated backups (which are snapshot-based) - Monitor slow queries via Query Insights (built into the AlloyDB console) --- Source attribution: TerminalSkills Original project: https://github.com/TerminalSkills/skills/blob/3bfbf7f76459d89422eddc0e5f572ad0e6c393c9/skills/gcp-alloydb/SKILL.md License: Apache-2.0 License information: https://www.apache.org/licenses/LICENSE-2.0 Original upstream instructions; downloaded from Dave AI. ## Upstream license notice Apache License Version 2.0, January 2004 http://www.apache.org/licenses/ TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION 1. Definitions. "License" shall mean the terms and conditions for use, reproduction, and distribution as defined by Sections 1 through 9 of this document. "Licensor" shall mean the copyright owner or entity authorized by the copyright owner that is granting the License. "Legal Entity" shall mean the union of the acting entity and all other entities that control, are controlled by, or are under common control with that entity. For the purposes of this definition, "control" means (i) the power, direct or indirect, to cause the direction or management of such entity, whether by contract or otherwise, or (ii) ownership of fifty percent (50%) or more of the outstanding shares, or (iii) beneficial ownership of such entity. "You" (or "Your") shall mean an individual or Legal Entity exercising permissions granted by this License. "Source" form shall mean the preferred form for making modifications, including but not limited to software source code, documentation source, and configuration files. "Object" form shall mean any form resulting from mechanical transformation or translation of a Source form, including but not limited to compiled object code, generated documentation, and conversions to other media types. "Work" shall mean the work of authorship, whether in Source or Object form, made available under the License, as indicated by a copyright notice that is included in or attached to the work (an example is provided in the Appendix below). "Derivative Works" shall mean any work, whether in Source or Object form, that is based on (or derived from) the Work and for which the editorial revisions, annotations, elaborations, or other modifications represent, as a whole, an original work of authorship. For the purposes of this License, Derivative Works shall not include works that remain separable from, or merely link (or bind by name) to the interfaces of, the Work and Derivative Works thereof. "Contribution" shall mean any work of authorship, including the original version of the Work and any modifications or additions to that Work or Derivative Works thereof, that is intentionally submitted to the Licensor for inclusion in the Work by the copyright owner or by an individual or Legal Entity authorized to submit on behalf of the copyright owner. For the purposes of this definition, "submitted" means any form of electronic, verbal, or written communication sent to the Licensor or its representatives, including but not limited to communication on electronic mailing lists, source code control systems, and issue tracking systems that are managed by, or on behalf of, the Licensor for the purpose of discussing and improving the Work, but excluding communication that is conspicuously marked or otherwise designated in writing by the copyright owner as "Not a Contribution." "Contributor" shall mean Licensor and any individual or Legal Entity on behalf of whom a Contribution has been received by the Licensor and subsequently incorporated within the Work. 2. Grant of Copyright License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable copyright license to reproduce, prepare Derivative Works of, publicly display, publicly perform, sublicense, and distribute the Work and such Derivative Works in Source or Object form. 3. Grant of Patent License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable (except as stated in this section) patent license to make, have made, use, offer to sell, sell, import, and otherwise transfer the Work, where such license applies only to those patent claims licensable by such Contributor that are necessarily infringed by their Contribution(s) alone or by combination of their Contribution(s) with the Work to which such Contribution(s) was submitted. If You institute patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Work or a Contribution incorporated within the Work constitutes direct or contributory patent infringement, then any patent licenses granted to You under this License for that Work shall terminate as of the date such litigation is filed. 4. Redistribution. You may reproduce and distribute copies of the Work or Derivative Works thereof in any medium, with or without modifications, and in Source or Object form, provided that You meet the following conditions: (a) You must give any other recipients of the Work or Derivative Works a copy of this License; and (b) You must cause any modified files to carry prominent notices stating that You changed the files; and (c) You must retain, in the Source form of any Derivative Works that You distribute, all copyright, patent, trademark, and attribution notices from the Source form of the Work, excluding those notices that do not pertain to any part of the Derivative Works; and (d) If the Work includes a "NOTICE" text file as part of its distribution, then any Derivative Works that You distribute must include a readable copy of the attribution notices contained within such NOTICE file, excluding any notices that do not pertain to any part of the Derivative Works, in at least one of the following places: within a NOTICE text file distributed as part of the Derivative Works; within the Source form or documentation, if provided along with the Derivative Works; or, within a display generated by the Derivative Works, if and wherever such third-party notices normally appear. The contents of the NOTICE file are for informational purposes only and do not modify the License. You may add Your own attribution notices within Derivative Works that You distribute, alongside or as an addendum to the NOTICE text from the Work, provided that such additional attribution notices cannot be construed as modifying the License. You may add Your own copyright statement to Your modifications and may provide additional or different license terms and conditions for use, reproduction, or distribution of Your modifications, or for any such Derivative Works as a whole, provided Your use, reproduction, and distribution of the Work otherwise complies with the conditions stated in this License. 5. Submission of Contributions. Unless You explicitly state otherwise, any Contribution intentionally submitted for inclusion in the Work by You to the Licensor shall be under the terms and conditions of this License, without any additional terms or conditions. Notwithstanding the above, nothing herein shall supersede or modify the terms of any separate license agreement you may have executed with Licensor regarding such Contributions. 6. Trademarks. This License does not grant permission to use the trade names, trademarks, service marks, or product names of the Licensor, except as required for reasonable and customary use in describing the origin of the Work and reproducing the content of the NOTICE file. 7. Disclaimer of Warranty. Unless required by applicable law or agreed to in writing, Licensor provides the Work (and each Contributor provides its Contributions) on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied, including, without limitation, any warranties or conditions of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A PARTICULAR PURPOSE. You are solely responsible for determining the appropriateness of using or redistributing the Work and assume any risks associated with Your exercise of permissions under this License. 8. Limitation of Liability. In no event and under no legal theory, whether in tort (including negligence), contract, or otherwise, unless required by applicable law (such as deliberate and grossly negligent acts) or agreed to in writing, shall any Contributor be liable to You for damages, including any direct, indirect, special, incidental, or consequential damages of any character arising as a result of this License or out of the use or inability to use the Work (including but not limited to damages for loss of goodwill, work stoppage, computer failure or malfunction, or any and all other commercial damages or losses), even if such Contributor has been advised of the possibility of such damages. 9. Accepting Warranty or Additional Liability. While redistributing the Work or Derivative Works thereof, You may choose to offer, and charge a fee for, acceptance of support, warranty, indemnity, or other liability obligations and/or rights consistent with this License. However, in accepting such obligations, You may act only on Your own behalf and on Your sole responsibility, not on behalf of any other Contributor, and only if You agree to indemnify, defend, and hold each Contributor harmless for any liability incurred by, or claims asserted against, such Contributor by reason of your accepting any such warranty or additional liability. END OF TERMS AND CONDITIONS Copyright 2025 Terminal Skills Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. # Dave AI resource metadata Creator / publisher: TerminalSkills Original project: https://github.com/TerminalSkills/skills/blob/3bfbf7f76459d89422eddc0e5f572ad0e6c393c9/skills/gcp-alloydb/SKILL.md Attribution basis: Source repository record Fields of work: Development & Engineering Roles: Backend Developer, Data Engineer, Database Administrator, DevOps / SRE, ML / AI Engineer Specialties: Database, DevOps Tools: GCP, Terraform Country applicability: Country not specified Classification basis: Source catalog labels Instruction SHA-256: 7089902c5105551d6e4bcfbf6eb67d33cc49e53193cafa36145f89a55cb4dfc7