cofin/alloydb-omni

AlloyDB Omni

Overview

AlloyDB Omni is the downloadable edition of AlloyDB that runs anywhere: local machines, on-premises data centers, or other cloud providers. It is distributed as a container image and includes the same query processing and columnar engine as the managed AlloyDB service.

Operating Layers

Use this skill in three distinct layers:

1. Deploy AlloyDB Omni on Docker, Podman, Kubernetes, or RPM-based hosts.
2. Connect an agent or client to the running database.
3. Operate the database with lifecycle, tuning, backups, diagnostics, and upgrades.

Keep those layers separate when giving guidance. Deployment is not the same thing as agent connectivity.

Quick Reference

Deployment Methods

| Method | Image | Use Case | |---|---|---| | Docker | google/alloydbomni:latest | Local development, CI | | Podman | google/alloydbomni:latest | Rootless containers, RHEL | | Kubernetes | AlloyDB Omni Operator | Production on-prem/multi-cloud | | RPM | alloydbomni package | Bare metal / VM (RHEL/CentOS) |

Key Environment Variables

| Variable | Purpose | Example | |---|---|---| | POSTGRES_PASSWORD | Initial superuser password (required) | mysecretpassword | | POSTGRES_DB | Database to create on first start | myapp | | POSTGRES_USER | Superuser name (default: postgres) | postgres |

Dev Workflow

1. Start container with docker compose up -d
2. Connect with psql -h localhost -U postgres
3. Use AlloyDB features (columnar engine, ML embeddings) locally
4. Tear down with docker compose down (data persists in named volume)

<workflow>

Workflow

Step 1: Choose Deployment Method

Use Docker/Podman for local development and CI. Use the Kubernetes operator for production non-GCP deployments. Use RPM for bare-metal servers.

Step 2: Configure Container Resources

Set --memory, --cpus, and --shm-size based on workload. For development, 2 CPUs / 4GB RAM / 256MB shared memory is a reasonable starting point.

Step 3: Set Up Persistence

Always use a named volume for /var/lib/postgresql/data. Without a volume, data is lost when the container stops. Optionally mount ./init-scripts to /docker-entrypoint-initdb.d for first-run SQL.

Step 4: Tune PostgreSQL Parameters

For non-trivial workloads, configure shared_buffers (25% of container memory), effective_cache_size (75%), and work_mem via ALTER SYSTEM SET or a mounted config file.

Step 5: Connect and Develop

Connect via localhost:5432. AlloyDB Omni supports all AlloyDB features including the columnar engine, so you can test analytical queries locally.

</workflow>

Host Integration Order

Use the lowest-admin supported path for the current host, and degrade cleanly:

1. Gemini CLI: use the dedicated alloydb-omni extension.
2. Other agents with MCP support: use MCP Toolbox with the official AlloyDB Omni prebuilt config.
3. No extension / no MCP: fall back to Docker/Podman/Kubernetes/RPM plus psql and SQL guidance from this skill's references.

Do not make the skill Gemini-only. The Gemini extension path is preferred when available, but the deployment and operational guidance in this skill must still work across other agents and plain terminal workflows.

<guardrails>

Guardrails

• Always set container resource limits — without --memory and --cpus, the container can consume all host resources and destabilize the machine
• Always use a named volume for data persistence — bind mounts work but named volumes are more portable and easier to manage
• Set shm_size to at least 256MB — the default 64MB is too small for PostgreSQL and causes "could not resize shared memory segment" errors
• Never use POSTGRES_PASSWORD in production — use secrets management (Docker secrets, Kubernetes secrets, or Vault)
• Back up the data volume regularly — use pg_dump or volume snapshots; there is no managed backup like GCP AlloyDB
• Pin the image tag in CI — google/alloydbomni:latest can change between runs; use a specific version tag for reproducibility

</guardrails> <validation>

Validation Checkpoint

Before delivering configurations, verify:

• [ ] Container has explicit memory and CPU limits set
• [ ] Data directory uses a named volume, not a tmpfs or anonymous volume
• [ ] shm_size is set to at least 256MB
• [ ] POSTGRES_PASSWORD is set (container will not start without it)
• [ ] Port mapping is correct (default: 5432:5432)

</validation> <example>

Example

Docker Compose for local AlloyDB Omni development:

# docker-compose.yml
services:
  alloydb:
    image: google/alloydbomni:latest
    container_name: alloydb-omni
    environment:
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-devsecret}
      POSTGRES_DB: myapp
      POSTGRES_USER: postgres
    ports:
      - "5432:5432"
    volumes:
      - alloydb-data:/var/lib/postgresql/data
      - ./init-scripts:/docker-entrypoint-initdb.d
    restart: unless-stopped
    shm_size: "256m"
    deploy:
      resources:
        limits:
          cpus: "2"
          memory: 4G

volumes:
  alloydb-data:

Initialization script to enable the columnar engine:

-- init-scripts/01-extensions.sql
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS google_ml_integration;

</example>

Kubernetes Operator Lifecycle

The AlloyDB Omni Kubernetes Operator manages DBCluster custom resources (CRD: dbclusters.alloydbomni.dbadmin.goog/v1). Key lifecycle operations:

• HA failover: enable automatic standby with availabilityOptions.standby: Enabled in primarySpec; the operator promotes the standby automatically on primary failure
• Read replica scaling: kubectl patch dbcluster <name> --type=merge -p '{"spec":{"readPoolSpec":{"replicas":<N>}}}'
• Rolling parameter updates: patching primarySpec.parameters triggers a controlled rolling restart with no data loss
• Backup: annotate the DBCluster with alloydbomni.dbadmin.goog/backup=true to trigger an immediate backup
• Upgrades: update databaseVersion or the image tag; the operator orchestrates a rolling restart

See references/kubernetes-operator.md for the full CRD spec, HA configuration YAML, scaling examples, health monitoring, and upgrade procedures.

RPM Lifecycle

RPM-based AlloyDB Omni installs are a first-class deployment path for RHEL-family hosts, VMs, and bare-metal systems where containers are not the right fit.

Key lifecycle operations:

• Install repository + package: add the AlloyDB Omni yum repo, then yum install alloydbomni
• Initialize data directory: run alloydb-omni init --data-dir=... before first start
• Manage the service: use systemctl enable --now alloydb-omni, status, restart, and journalctl
• Tune PostgreSQL settings: change parameters with ALTER SYSTEM SET ... and restart the service
• Upgrade in place: update the RPM package, restart the service, and verify version + extension state
• Back up and validate: verify local storage, service health, and extension availability before and after upgrades

See references/rpm.md for the full install, service-management, configuration, validation, and upgrade workflow.

Performance Diagnostics

Key diagnostics for AlloyDB Omni production workloads:

• Query plans: use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) to identify sequential scans, high-cost nodes, and buffer hit ratios
• Invalid indexes: query pg_class JOIN pg_index where indisvalid = false to find indexes that need rebuilding with REINDEX CONCURRENTLY
• Bloat detection: query pg_stat_user_tables for n_dead_tup and n_live_tup ratios; tables with dead-tuple ratio above 20% are candidates for VACUUM ANALYZE
• Active query monitoring: pg_stat_activity filtered on state = 'active' and wait_event_type to identify lock waits and long-running queries

See references/performance.md for ready-to-run diagnostic queries, autovacuum tuning, and connection lifecycle management.

Columnar Engine Tuning

The columnar engine accelerates analytical queries by caching selected columns in a compressed in-memory format.

• Memory limit: set google_columnar_engine.memory_limit (e.g., ALTER SYSTEM SET google_columnar_engine.memory_limit = '4GB') — allocate 10–25% of total container/node memory
• Recommended columns: add wide tables with high read frequency and low update frequency via SELECT google_columnar_engine_add('<table>') or individual column-level population
• Cost/benefit check: compare EXPLAIN output before and after adding a table — look for Custom Scan (columnar scan) nodes replacing Seq Scan
• Cache inspection: SELECT * FROM g_columnar_memory_usage shows per-relation memory consumption and hit rates

Gemini CLI and MCP Toolbox

This section is for the connection layer, not for deploying AlloyDB Omni itself.

For AlloyDB Omni, prefer the dedicated Gemini CLI extension when Gemini is the active host. Use the generic PostgreSQL route only as a fallback when the dedicated extension is unavailable.

gemini extensions install https://github.com/gemini-cli-extensions/alloydb-omni --auto-update
gemini extensions config alloydb-omni --scope workspace

Guide the user through the required connection variables before starting Gemini:

export ALLOYDB_OMNI_HOST="<database-host>"
export ALLOYDB_OMNI_PORT="<database-port>"
export ALLOYDB_OMNI_DATABASE="<database-name>"
export ALLOYDB_OMNI_USER="<database-user>"
export ALLOYDB_OMNI_PASSWORD="<database-password>"
export ALLOYDB_OMNI_QUERY_PARAMS="<optional-query-string>"

Important configuration guidance:

• Gemini CLI should be v0.6.0 or newer.
• Load the variables from a .env file when possible.
• Connection settings are fixed at session start; restart Gemini to switch databases.
• Treat configuration as workspace-scoped by default, not user-global.

For non-Gemini agents, or when the user needs a shared MCP endpoint, guide them to MCP Toolbox using the AlloyDB Omni prebuilt config rather than inventing a custom setup.

For reusable project workflows, prefer generated workspace skills:

toolbox --prebuilt alloydb-omni skills-generate \
  --name alloydb-omni-optimize \
  --toolset optimize \
  --description "AlloyDB Omni optimization skill" \
  --output-dir .agents/skills

If neither Gemini extensions nor MCP Toolbox are available, fall back to the manual Docker/Podman/Kubernetes/RPM workflows and psql diagnostics already documented in this skill's references.

---

References Index

For detailed guides and code examples, refer to the following documents in references/:

• Setup & Deployment
  • Container deployment (Docker/Podman), Kubernetes operator, local development workflows.
• Configuration
  • Memory/CPU tuning, persistence volumes, networking, PostgreSQL parameter overrides.
• Kubernetes Operator
  • DBCluster CRD spec, HA failover, read replica scaling, rolling updates, backup annotations, health monitoring, upgrade procedures.
• RPM Deployment
  • RHEL-family installation, systemd lifecycle, configuration, upgrades, and operational validation.
• Performance Diagnostics
  • Query planning, invalid index detection, bloat analysis, active query monitoring, columnar engine tuning, autovacuum, connection lifecycle.
• Gemini + MCP Guidance
  • PostgreSQL extension install, env vars, and MCP Toolbox fallback guidance for Omni workflows.

---

Official References

• https://cloud.google.com/alloydb/docs/omni
• https://docs.cloud.google.com/alloydb/omni/containers/17.5.0/docs/connect-ide-using-mcp-toolbox
• https://github.com/gemini-cli-extensions/alloydb-omni

Shared Styleguide Baseline

• Use shared styleguides for generic language/framework rules to reduce duplication in this skill.
• General Principles
• PostgreSQL / psql
• Keep this skill focused on tool-specific workflows, edge cases, and integration details.