PCDOE Manage Database Solutions Practice Test 5 — 15 Questions

Question 1

A company is using Cloud SQL for PostgreSQL and needs to perform point-in-time recovery (PITR) to recover from a logical error that occurred 30 minutes ago. They have already configured automated backups. What additional configuration is required?

Accepted Answer

Set the 'transaction log retention' to a value between 1 and 7 days.. Cloud SQL PITR requires write-ahead log (WAL) archiving, which is enabled by setting the 'transaction log retention' in days (1-7). Automated backups alone do not capture continuous transaction logs.

Answer

Create a cross-region backup replica to enable PITR.

Answer

Enable binary logging on the instance.

Answer

Increase the storage size to accommodate logs.

Question 2

An organization needs to run a disaster recovery drill for its Cloud SQL for MySQL instance by promoting a read replica to a standalone instance. The replica is in the same region as the primary. After the drill, they want the original primary to resume serving writes. What should they do?

Accepted Answer

Promote the replica, then delete and recreate the replica from the original primary after the drill.. Promoting a read replica in Cloud SQL for MySQL breaks the replication link, making the replica a standalone primary. After the drill, you must delete the promoted replica and create a new read replica from the original primary to re-establish replication. This is the only supported method because Cloud SQL does not allow reversing the promotion or re-attaching a promoted instance as a replica.

Answer

Stop replication, then restart the primary instance to make it the new replica.

Answer

Use the gcloud command to switch roles between the primary and replica.

Answer

Promote the replica, then perform a failover on the original primary to make it the new primary.

Question 3

A Cloud Spanner database is experiencing high CPU utilization (above 80%). The team wants to add an index to optimize a frequently used query. The schema change must not cause downtime. How should they proceed?

Accepted Answer

Create the index using the Google Cloud Console; it will be applied online.. Spanner supports online schema changes; CREATE INDEX is non-blocking and can be executed via DDL without downtime.

Answer

Export the database, create the index in a new instance, then import.

Answer

Use gcloud spanner databases ddl update with --async flag.

Answer

Stop the application, run the DDL statement, then restart.

Question 4

A company uses Bigtable for real-time analytics. They notice that writes to a specific row range are significantly slower. The Key Visualizer shows a hotspot. What is the most likely cause and recommended action?

Accepted Answer

Redesign the row key to avoid sequential writes to the same region.. Hotspots occur when a single tablet server is overloaded due to high write traffic to a contiguous key range. The solution is to redesign the row key to distribute writes.

Answer

Create a new cluster and replicate data to that cluster.

Answer

Change the storage type from HDD to SSD.

Answer

Increase the number of nodes to handle the load.

Question 5

A developer needs to connect to a Memorystore for Redis instance from a Compute Engine VM in the same VPC network. The Redis instance has AUTH enabled. What must the developer provide in the connection string?

Accepted Answer

The Redis AUTH password.. When AUTH is enabled on a Memorystore for Redis instance, the client must provide the AUTH password in the connection string (e.g., `redis://:password@host:6379`) or via the `AUTH` command after connecting. This password is set during instance creation and is required for authentication before any data commands can be executed. Without it, the Redis server will reject the connection with a `NOAUTH Authentication required` error.

Answer

The IAM role 'Memorystore User'.

Answer

The client certificate and private key.

Answer

The service account key file.

Question 6

A Cloud Firestore database in Native mode is used for a mobile app. The app queries a collection with a composite filter on two fields. Queries are slow and the app shows an error that an index is required. What should the developer do?

Accepted Answer

Create a composite index in the Firebase Console or gcloud CLI.. Cloud Firestore requires an index to support composite filters (queries filtering on multiple fields). When a query uses a composite filter and no matching index exists, Firestore returns an error indicating an index is required. The correct solution is to create the necessary composite index manually via the Firebase Console or the gcloud CLI, as Firestore does not automatically create composite indexes for queries with equality and range filters on different fields.

Answer

Use a database export and import to regenerate indexes.

Answer

Restructure the data to use a single field for the filter.

Answer

Enable the automatic index creation in the Firestore settings.

Question 7

An organization is using Cloud SQL for MySQL with automated backups enabled. They need to restore a backup to a new instance in a different region for testing. The backup is stored in the same region as the source. How can they achieve this with minimal operational overhead?

Accepted Answer

Restore the backup to a new instance in the same region, then use export/import to move data to the target region.. Option D is correct because Cloud SQL backups are region-specific and cannot be restored directly to a different region. The minimal-operational-overhead approach is to restore the backup to a new instance in the same region, then use the export/import feature (e.g., SQL dump or CSV) to move the data to the target region. This avoids the complexity and cost of maintaining a cross-region read replica, while still achieving the goal with built-in tools.

Answer

Use gcloud sql backups restore with the --restore-instance flag pointing to a new instance in the target region.

Answer

Create a cross-region read replica from the source, then promote it in the target region.

Answer

Restore the backup directly to a new instance in the target region using the Cloud Console.

Question 8

A company uses Cloud Spanner and wants to export a database for long-term archival. The export must include the schema in a portable format. Which export option should they choose?

Accepted Answer

Create a database-level backup from the Cloud Console; it will export data and schema as Avro + Protobuf.. Spanner database backups are stored in Cloud Storage as Avro files with a Protobuf schema file, preserving both data and schema.

Answer

Manually run SELECT statements to export data and DESCRIBE commands for schema.

Answer

Use the Spanner migration tool to export to a SQL dump file.

Answer

Use gcloud spanner databases export with the --async flag to export to a Cloud Storage bucket in Avro format.

Question 9

A Cloud SQL for PostgreSQL instance is running low on disk space. The database size is 500 GB and the current storage is 600 GB. The engineer needs to increase storage to 800 GB without downtime. What should they do?

Accepted Answer

Use gcloud sql instances patch to increase storage size to 800 GB.. Option B is correct because Cloud SQL for PostgreSQL supports online storage increases using the `gcloud sql instances patch` command, which resizes the disk without requiring an instance restart or downtime. The storage can be increased up to the maximum allowed for the instance tier, and the change takes effect immediately while the instance remains available.

Answer

Shut down the instance, edit the configuration to increase storage, then restart.

Answer

Create a new instance with 800 GB storage and migrate data using pg_dump.

Answer

Enable auto-storage increase and wait for it to happen automatically.

Question 10

A Bigtable cluster is configured with SSD storage. The team needs to reduce costs by switching to HDD storage while maintaining the same cluster ID and node count. What is the correct approach?

Accepted Answer

Create a new Bigtable instance with HDD storage, then use a table export/import to move data.. Bigtable does not allow in-place modification of storage type (SSD vs. HDD) on an existing cluster. To switch storage, you must create a new instance with HDD storage and migrate data using export/import (e.g., via Cloud Storage and Dataflow). Option C correctly describes this process, preserving the cluster ID and node count by recreating the instance with the same configuration but HDD storage.

Answer

Edit the cluster settings and change the storage type from SSD to HDD.

Answer

Delete the cluster and recreate it with HDD storage.

Answer

Use gcloud bigtable instances update to change the storage type.

Question 11

A Memorystore for Redis instance is experiencing out-of-memory errors, causing keys to be evicted. The application relies on all keys being present. The current maxmemory-policy is allkeys-lru. What should the engineer do to prevent evictions?

Accepted Answer

Change the eviction policy to noeviction.. With noeviction policy, Redis will return errors instead of evicting keys, preserving all data. However, writes may fail if memory is full. A better solution is to increase memory or use a cluster.

Answer

Change the eviction policy to volatile-lru.

Answer

Enable persistence to Cloud Storage to offload memory.

Answer

Scale up the instance to a higher tier with more memory.

Question 12

A company uses Firestore in Datastore mode. They need to create a composite index for a query that filters on two properties. The query is already running and returning an error that an index is required. What is the correct way to create this index?

Accepted Answer

Create the index using the gcloud datastore indexes create command with a YAML file.. Option A is correct because in Firestore (Datastore mode), composite indexes must be explicitly created before they can be used by queries that filter on multiple properties. The `gcloud datastore indexes create` command with a YAML file is the standard method to define and deploy these indexes, as the Datastore mode does not automatically create composite indexes from query patterns.

Answer

Modify the query to use a single filter to avoid needing a composite index.

Answer

Enable the 'auto-index' feature in the Datastore console.

Answer

The index will be created automatically based on the query pattern.

Question 13

A Cloud SQL for MySQL instance has a read replica in a different region. The team wants to monitor replication lag and receive alerts if lag exceeds 60 seconds. Which two steps should they take? (Choose TWO.)

Accepted Answer

Create a Cloud Monitoring alerting policy with a condition on 'cloudsql.googleapis.com/database/replication/replica_lag' with threshold >60s.. The replication_lag metric is available in Cloud Monitoring. An alerting policy based on this metric with a threshold of 60 seconds will trigger notifications when lag exceeds that value.

Answer

Configure a Cloud Function to query the replica status every minute.

Answer

Set up a Cloud Scheduler job to run a query on the replica to check lag.

Answer

Promote the replica to standalone if lag exceeds 60 seconds.

Question 14

A company is migrating its on-premises PostgreSQL database to Cloud SQL. The database is 2 TB and the migration must have minimal downtime. The source database supports continuous archiving. Which three steps should they take? (Choose THREE.)

Accepted Answer

Perform the cutover by promoting the Cloud SQL instance to primary.. Option B is correct because promoting the Cloud SQL instance to primary is the final step in a migration using continuous replication, which minimizes downtime by allowing the source database to remain operational until the cutover. This approach leverages Cloud SQL's ability to act as a replica that stays synchronized with the on-premises database via continuous archiving, ensuring data consistency with minimal interruption.

Answer

Use pg_dump to export the database and import into Cloud SQL.

Answer

Enable binary logging on the source database.

Question 15

A development team is using Firestore in Native mode. They want to ensure that queries on array fields return correct results. Which two actions should they take? (Choose TWO.)

Accepted Answer

Set index exemptions for arrays and maps to disable automatic indexing.. Firestore automatically creates single-field indexes including for array fields. However, if a query uses array-contains-any, a composite index may be needed. Index exemptions can be used to exclude certain fields from automatic indexing to control costs.

Answer

Create a composite index for every query involving array fields.

Answer

Use the 'array-contains' operator only on indexed arrays.

Answer

Manually create indexes for each array field in the Firestore console.