Automate common database tasks

1.1How Exports Differ From Backups

Data export and archival in Cloud SQL (opens in a new tab) moves database contents into portable files in Cloud Storage (opens in a new tab) for reuse, copying, and long-term retention. An export creates an external file that can recreate data, copy a single database or table, and remain available even if the source instance is deleted. Backups and point-in-time recovery instead support fast operational restore of the instance itself. Exports are therefore the choice when data must be portable, selective, or retained after cleanup.

1.2Automating Exports and Archival Storage

Automated exports combine native database export tools with schedulers and tiered storage to meet compliance and synchronization needs without interrupting production. Serverless exports in Cloud SQL offload the export to a temporary instance, so the primary instance keeps serving queries at normal performance and the temporary instance is automatically deleted when the export completes. Cloud Scheduler with the REST API automates Cloud SQL exports, Cloud Scheduler with Cloud Run functions supports advanced automation, and Managed Airflow schedules regular exports of multiple databases with a directed acyclic graph that copies data to another project, dataset, or table or to Cloud Storage. Data flows from the source database to the destination Cloud Storage bucket, and small instances can be temporarily increased in CPU and RAM to improve large imports.

Archival storage lowers cost by moving infrequently accessed export files to lower-cost tiers as access likelihood decreases. The pattern migrates data from higher-cost storage to Nearline, Coldline, and Archive classes, where Nearline is designed for backup scenarios with access at most once a month. Lower cost comes with minimum storage durations and retrieval costs for early access, so lifecycle rules, Soft Delete, Object holds, and Identity and Access Management policies are used to protect exports and control who can reach the bucket.

1.3PostgreSQL Vacuum and Cloud SQL Maintenance Timing

Database health automation keeps PostgreSQL-compatible databases fast through engine cleanup and controlled platform updates. AlloyDB for PostgreSQL (opens in a new tab) and Cloud SQL divide the work: the engine needs regular vacuuming, while the managed platform needs software patches applied during scheduled windows. Standard VACUUM reclaims dead tuples and freezes transaction IDs while running in parallel with SELECT, INSERT, UPDATE, and DELETE, but it blocks ALTER TABLE and other definition changes. VACUUM FULL reclaims more space but exclusively locks the table and runs slowly, so standard VACUUM is preferred for routine automation.

Tuning focuses on reducing vacuum duration and write-ahead log pressure:

• Increase system memory and maintenance_work_mem, remembering that autovacuum can allocate up to autovacuum_max_workers multiples of that memory.
• Reduce write-ahead log volume where possible, because VACUUM generates many log records that slow completion.
• Raise vacuum_freeze_min_age toward its maximum of 1,000,000,000 from the default of 50,000,000 when nearing the two-billion transaction ID limit.
• Use INDEX_CLEANUP OFF on PostgreSQL 12.0 and later to freeze table data quickly without a full index scan.
• Drop non-critical indexes before vacuum on versions before 12.0, because each additional index adds scan time.

Maintenance timing (opens in a new tab) controls when disruptive updates occur so downtime lands during low traffic. Timing selects the rollout week after notification, the maintenance window selects a one-hour day-and-time slot, and the deny maintenance period blocks maintenance for up to 90 days. If no window is set, Cloud SQL uses 10 PM to 6 AM Monday through Friday and Friday 10 PM to Monday 6 AM on weekends in the instance time zone.

1.4Index Fragmentation and Iterative Tuning

Index optimization keeps indexes fast for queries and compact for storage as tables change. In Cloud SQL for SQL Server, maintenance centers on measuring fragmentation with sys.dm_db_index_physical_stats joined to sys.indexes, which returns avg_fragmentation_in_percent for each index. Heavily modified tables show the highest values, so a regular job should prioritize them first. Administrators then run ALTER INDEX ... REORGANIZE to reduce fragmentation without recreating the full index, often generating statements from INFORMATION_SCHEMA.TABLES and executing them incrementally on the most fragmented tables.

Disk layout and iterative testing support that maintenance. SQL Server stores data in 64 KB extents made of eight contiguous 8 KB pages, so formatting secondary disks with a 64 KB allocation unit lets the engine read and write whole extents efficiently. In Spanner, tuning follows an ordered cycle: first upload sample data, then point the application to Spanner, verify consistency, load-test and inspect expensive queries with Query Insights, and repeat after each schema or index change. Suboptimal Spanner performance often traces to full scans of large tables, cross-joins, predicates on non-key columns, high CPU utilization, transaction locking, or hotspots investigated with Key Visualizer.

2.1Scheduled Export Architecture and Trigger Flow

Scheduled exports use Cloud Scheduler (opens in a new tab) to start work on a timetable, Pub/Sub to deliver the trigger, and Cloud Run functions to run the export logic. Data flows from Cloud Scheduler to a Pub/Sub topic to a function, then from the database to a Cloud Storage (opens in a new tab) bucket. The same pattern is recommended for Firestore in Datastore mode and suggested for Cloud SQL, which does not provide built-in automated exports. The project must have billing enabled, the destination bucket must exist near the database location, and a Requester Pays bucket cannot be used.

The end-to-end flow preserves efficiency because no server runs between exports:

1. Schedule fires: Cloud Scheduler publishes the configured payload to the Pub/Sub topic at the cron time.
2. Trigger delivers: Pub/Sub delivers the message to the export function.
3. Export starts: The function decodes the payload and calls the export operation with the bucket and optional filters.
4. Data lands: The database service writes timestamp-organized output files to the Cloud Storage bucket.

A Cloud Scheduler job defines a name, a frequency in unix-cron format, and a timezone such as UTC, with a Pub/Sub target set to the topic the function listens to. The JSON payload must include a bucket value such as gs://bucket_name and can include kinds to export only selected entity kinds and namespaceIds to limit namespaces.

2.2Monitoring Export Jobs and Handling Failures

Export monitoring tracks each asynchronous export job through its lifecycle from queue to completion. Each job is tracked by a unique dataExportId and moves in order from IN_QUEUE while waiting for capacity, to PROCESSING while data transfers, to either FINISHED_SUCCESS or FINISHED_FAILURE. Operators monitor the lifecycle with GetDataExport for one job or ListDataExport filtered by type, time, or status. The Data Export API uses exponential backoff for transient network problems, plus queuing and prioritization to support point-in-time exports of up to 100 TB.

For timer-driven exports, success at one stage does not prove success at the next. The Cloud Scheduler result confirms only that a message was sent to Pub/Sub, so verification must continue through function logs, long-running operations, and destination files:

1. Check scheduler execution: Run the job manually, refresh, and confirm Success and current Last run time.
2. Check function logs: Open Logs Explorer for successful initiation versus error entries.
3. Check operation progress: List long-running operations to see running, succeeded, or failed status.
4. Check destination data: Open the bucket and confirm new timestamped files for the expected window.

2.3Storage Buckets, Permissions, and Encryption Controls

Storage configuration controls where scheduled exports can be written and which identities can write them. The export bucket should be near the database location, cannot be a Requester Pays or Rapid bucket for Firestore exports, and fills over time because each scheduled run creates new objects. Lifecycle rules with deletion and SetStorageClass transitions, retention policies with Bucket Lock, versioning, uniform bucket-level access, blocked public access, and logging limit cost and accidental loss.

Permissions separate what the operator needs from what the automated writer needs:

A same-project Firestore bucket works by default, while a bucket in another project requires an explicit grant to the source project's service agent. Customer-managed encryption adds a second boundary: a Looker scheduled export requires a customer-managed key in a different region or a global key, and the service account needs Cloud KMS CryptoKey Encrypter/Decrypter plus bucket write rights to create the encrypted artifact.

3.1Choosing a Valid Upgrade Target and Pre-Upgrade Checks

A valid upgrade target is a major version that Cloud SQL (opens in a new tab) lists as upgradable for the specific instance. Operators run gcloud sql instances describe and locate upgradableDatabaseVersions, or call the instances.get method, where each entry shows the target major, version string, and display name. Cloud SQL for MySQL does not support skipping major versions, so an instance must move one major at a time, while SQL Server allows a combined major-version and edition change with license pricing reviewed first. Confirming the exact target first avoids requesting an unsupported in-place upgrade.

Pre-upgrade checks prevent failed upgrades and application errors. Release notes must be reviewed for breaking changes, discontinued features, and changed defaults, including preserved SQL Server compatibility levels and MySQL defaults such as character_set_server changing from utf8 to utf8mb4. Instance settings must pass the automatic check: PostgreSQL LC_COLLATE must be en_US.UTF8, unsupported extensions such as chkpass must be dropped, PostGIS must be updated, custom flags must still exist, MySQL partial_revokes=ON behavior must be accounted for, and storage must have room for upgrade work. Validation uses a clone as an isolated test environment, runs the full upgrade on the clone, measures downtime and storage growth, and then schedules production during low activity.

3.2In-Place Upgrade Sequence for Cloud SQL and AlloyDB

In-place upgrades update the existing instance or cluster without creating a new instance, so identity and connection strings are preserved. Cloud SQL in-place upgrades keep the same instance name, IP address, and settings with no data-file move, using pg_upgrade for PostgreSQL, the MySQL server upgrade process, or the SQL Server in-place utility. Migrating data to a new versioned instance instead creates a new name and address that applications must cut over to, with downtime that depends on transfer size and cutover coordination. AlloyDB for PostgreSQL (opens in a new tab) in-place upgrades keep the same cluster endpoint but run longer overall, with primary downtime typically 20 minutes to one hour depending on schema complexity.

When a single-instance Cloud SQL upgrade starts, each step depends on the prior step:

1. Check configuration: Cloud SQL verifies compatibility and fails fast with guidance if not ready.
2. Make unavailable: Cloud SQL makes the instance unavailable to connections.
3. Pre-upgrade backup: Cloud SQL takes an on-demand backup that can restore the prior version state.
4. Perform upgrade: Cloud SQL upgrades binaries and system objects in place.
5. Make available: Cloud SQL makes the instance available for new writes.
6. Post-upgrade backup: Cloud SQL takes an on-demand backup immediately after writes are allowed.

An upgrade that includes replicas extends that flow from one instance to a primary-plus-replica topology. Cloud SQL checks the primary and all replicas, makes the primary unavailable, takes the pre-upgrade backup, stops replication, upgrades the primary first, makes the primary available, restarts replication, takes the post-upgrade backup, then upgrades all replicas including cascading and cross-region replicas. Even if a replica upgrade fails, the upgraded primary stays available.

3.3Maintenance Timing, Rescheduling, and Post-Upgrade Validation

Maintenance timing (opens in a new tab) controls how long after notification an update is applied, so staging can be updated before production. A notification is sent before each rollout, then instances update in weekly groups within a one-hour window. The common pattern sets staging to Week 1 and production to Week 2 with the same weekly window, so operational tests in staging can trigger a deny period for production if staging fails.

Rescheduling (opens in a new tab) moves a scheduled update up to 24 hours before it occurs, for one instance at a time, with options for immediate, next available window, or a specific time. Week 1 or Week 2 timing allows rescheduling up to 28 days after the original date, while Week 5 allows only up to 7 days. A deny maintenance period blocks all scheduled maintenance for up to 90 days to protect peaks such as November 1 through January 15. Notifications are opt-in per project, send one email per instance at least one week before maintenance, and are not sent for read replicas.

Post-upgrade validation confirms function, connectivity, and performance in order. Teams run acceptance and smoke tests of critical features, verify applications reconnect with the same IP address and that prior transactions are present, then review CPU, memory, connections, and replication lag while using Query Insights to find slow queries. Readiness also requires at least 20% free storage with a metrics-based alert on disk utilization, connection retry logic with exponential backoff, and no new administrative operation started before the prior one completes.

4.1Defining SLIs, SLOs, and Error Budgets

A service-level indicator is a measurement of service performance from the user's point of view, a service-level objective is the target value for that indicator over a compliance period, and the error budget starts at 1 minus the objective and shrinks as performance misses the goal. In Google Cloud, Cloud Monitoring (opens in a new tab) collects infrastructure metrics that become the basis for indicators, and up to 500 objectives can be created for each service. An availability indicator is the ratio of successful responses to all responses, such as HTTP 2xx responses divided by all responses per minute. A latency indicator is the ratio of calls below a threshold to all calls, such as the share of 2xx responses faster than 300 ms.

Service monitoring (opens in a new tab) groups services to help select the right metrics. Request-response services wait for explicitly requested work to complete, data storage and retrieval services store or return data in response to a request, and data-processing services process customer data and return a result or feed. An objective is built from an indicator, a performance goal such as 99% availability or latency below 300 ms, and a compliance period such as a calendar WEEK or a rolling 259200 seconds for 3 days. The highest objective value that can be set is 99.9%, and a useful objective targets less than 100% so the gap creates budget for change.

4.2Request-Based Compared With Windows-Based SLIs

Request-based indicators count atomic units of work, such as successful HTTP requests, object reads, or database queries that meet a goodness rule. This approach works well when each request matters equally and volume is high enough to form a stable ratio. Windows-based indicators instead divide time into small windows and count how many windows meet a goodness criterion, such as latency below a threshold or error rate below a limit. Windows-based measurement is the choice when low traffic, bursty workloads, or long operations make per-request ratios noisy.

Database indicators build on the four golden signals of latency, traffic, error rate, and saturation. Latency covers query time, traffic covers queries per second, error rate becomes the failure side of availability, and saturation covers CPU, memory, disk I/O, and network use. For data storage metrics (opens in a new tab), Cloud Storage availability uses the gcs_bucket resource and storage.googleapis.com/api/request_count with method and response_code labels to isolate good reads, while Bigtable uses bigtable_table with server/request_count and server/error_count. Cloud Storage exposes no latency metric, so client-side instrumentation with logs-based metrics, Prometheus, or OpenCensus is required when latency matters.

4.3Alerting, SLA Coverage, and Root Cause Analysis

Alerting on error budgets turns indicator measurements into early warnings before a service-level agreement breach. The flow runs from metric collection into Cloud Monitoring, where the objective engine evaluates the indicator against the goal over the compliance period, then to an alerting policy that watches burn rate and notifies configured channels when consumption is too fast. Teams use the predefined Cloud SQL dashboard and Query Insights dashboard to find expensive queries and capacity issues, plus custom dashboards and policies such as an alert when available disk space falls below 20%. A calendar period resets at each WEEK boundary, while a rolling period continuously drops old points, so remaining budget can recover when poor performance rolls out of the window.

SLA coverage for Cloud SQL excludes outages outside Google's reasonable control and unhealthy instances that violate operational limits. Only instances configured for high availability with at least one dedicated CPU are covered; shared-core and single-zone instances are not covered. Coverage is also removed by storage-full conditions without automatic increase, consistently high CPU or memory use, transaction ID wraparound that takes the instance offline, and risky flag settings. Root cause analysis correlates the degraded trend with Cloud Logging, Cloud Trace for hop-by-hop latency, and Cloud Profiler for code bottlenecks, strengthened when Sqlcommenter tags propagate application context into database statements.

Key Facts

• Cloud SQL exports create portable files in Cloud Storage that survive instance deletion and can target a single database or table, while backups are deleted with the instance.
• Serverless exports offload Cloud SQL export work to a temporary instance that is deleted when the export completes.
• Cloud SQL maintenance moves the instance to an updated virtual machine by detaching the data disk and static IP from the original machine and attaching them to the updated machine.
• Cloud SQL for MySQL cannot skip major versions and must move one major at a time, while in-place upgrades keep the same instance name, IP address, and settings.
• A service-level indicator measures performance, a service-level objective is the target over a compliance period, and the error budget starts at 1 minus the objective.