Table: gcp_alloydb_instance - Query Google Cloud Platform AlloyDB Instances using SQL

Google Cloud AlloyDB is a fully managed PostgreSQL-compatible database service, optimized for high performance with the reliability of Google's infrastructure. AlloyDB Instances within a cluster offer flexible options for scaling and redundancy, meeting the needs of demanding database applications.

Table Usage Guide

The gcp_alloydb_instance table can be queried to retrieve detailed information about individual AlloyDB instances within a cluster. It is invaluable for database administrators and system architects looking to monitor instance-specific performance, understand configuration details, and manage resource allocation effectively.

Examples

Basic Information Query

Retrieve essential details about all AlloyDB instances to quickly get an overview of your instances' configurations and statuses.

select
  name,
  state,
  availability_type,
  display_name
from
  gcp_alloydb_instance;

select
  name,
  state,
  availability_type,
  display_name
from
  gcp_alloydb_instance;

List Instances by Availability Type

This query is useful for identifying instances according to their availability setup, which helps in understanding your database's fault tolerance and geographic distribution.

select
  name,
  availability_type,
  state
from
  gcp_alloydb_instance
where
  availability_type = 'REGIONAL';

select
  name,
  availability_type,
  state
from
  gcp_alloydb_instance
where
  availability_type = 'REGIONAL';

Find Instances with Specific Labels

Labels are key-value pairs assigned to instances and can be used for organization and access control. This query helps filter instances based on these labels.

select
  name,
  labels
from
  gcp_alloydb_instance
where
  labels -> 'environment' = 'production';

select
  name,
  json_extract(labels, '$.environment') as environment
from
  gcp_alloydb_instance
where
  environment = 'production';

Instances Currently in Maintenance

This query helps in identifying which instances are currently undergoing maintenance, allowing for better planning and reduced downtime impact.

select
  name,
  state
from
  gcp_alloydb_instance
where
  state = 'MAINTENANCE';

select
  name,
  state
from
  gcp_alloydb_instance
where
  state = 'MAINTENANCE';

Detailed View of Instance Configurations

Gain a deeper understanding of the configurations for a specific instance, useful for troubleshooting or planning upgrades.

select
  name,
  machine_config,
  client_connection_config,
  ip_address
from
  gcp_alloydb_instance
where
  name = 'instance-12345';

select
  name,
  json_extract(machine_config, '$') as machine_config,
  json_extract(client_connection_config, '$') as client_connection_config,
  ip_address
from
  gcp_alloydb_instance
where
  name = 'instance-12345';

Get node details of the instances

Retrieve information about the nodes.

select
  name,
  node -> 'Id' as node_id,
  node -> 'Ip' as node_ip,
  node -> 'State' as node_state,
  node -> 'ZoneId' as node_zone_id
from
  gcp_alloydb_instance,
  jsonb_array_elements(nodes) as node;

select
  name,
  json_extract(node, '$.Id') as node_id,
  json_extract(node, '$.Ip') as node_ip,
  json_extract(node, '$.State') as node_state,
  json_extract(node, '$.ZoneId') as node_zone_id
from
  gcp_alloydb_instance,
  json_each(json(nodes)) as node;

Schema for gcp_alloydb_instance

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
akas	jsonb		Array of globally unique identifier strings (also known as) for the resource.
annotations	jsonb		Annotations to allow client tools to store a small amount of arbitrary data.
availability_type	text		Availability type of the instance.
client_connection_config	jsonb		Client connection specific configurations.
cluster_name	text	=	User-settable display name for the cluster.
create_time	timestamp with time zone		Creation time of the instance.
database_flags	jsonb		Database flags set at the instance level.
delete_time	timestamp with time zone		Deletion time of the instance.
etag	text		Resource freshness validation.
gce_zone	text		Compute Engine zone where the instance is located.
instance_display_name	text	=	User-settable display name for the instance.
instance_type	text		Type of the instance.
ip_address	inet		IP address assigned to the instance.
labels	jsonb		Labels as key-value pairs.
location	text	=	The GCP multi-region, region, or zone in which the resource is located.
machine_config	jsonb		Configurations for the machines that host the underlying database engine.
name	text		The resource name of the instance.
nodes	jsonb		List of available read-only VMs in this instance.
project	text	=, !=, ~~, ~~, !~~, !~~	The GCP Project in which the resource is located.
query_insights_config	jsonb		Configuration for query insights.
read_pool_config	jsonb		Read pool instance configuration.
reconciling	boolean		Indicates if the instance is reconciling.
satisfies_pzs	boolean		Reserved for future use.
self_link	text		Server-defined URL for the resource.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
state	text		Current serving state of the instance.
title	text		Title of the resource.
uid	text		System-generated UID of the resource.
update_time	timestamp with time zone		Last update time of the instance.
writable_node	jsonb		This is set for the read-write VM of the PRIMARY instance only.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- gcp

You can pass the configuration to the command with the --config argument:

steampipe_export_gcp --config '<your_config>' gcp_alloydb_instance