PCDOE Manage Database Solutions Practice Test 10 — 15 Questions

Question 1

A company uses Cloud SQL for PostgreSQL and needs to enable point-in-time recovery (PITR) with a retention period of 14 days. Currently, automated backups are configured with a 7-day retention. What should the engineer do to meet this requirement?

Accepted Answer

Note that Cloud SQL for PostgreSQL only supports up to 7 days of PITR retention; consider exporting logs to Cloud Storage for longer recovery.. Cloud SQL PITR is based on write-ahead log (WAL) archiving. The transaction log retention is set via the 'transaction log retention' setting, not the backup retention. PITR for PostgreSQL in Cloud SQL supports up to 7 days of log retention; 14 days is not supported. The engineer must accept the limitation or use a different approach.

Answer

Increase the backup retention period to 14 days; PITR is automatically extended.

Answer

Set the 'transaction log retention' to 14 days in the Cloud SQL instance settings.

Answer

Enable binary logging and set the binary log retention to 14 days.

Question 2

A Cloud Spanner database has a table 'Orders' with a column 'status'. The development team needs to add a new column 'priority INT64' to the table without downtime. Which statement should the database administrator execute?

Accepted Answer

ALTER TABLE Orders ADD COLUMN priority INT64;. Spanner supports online schema changes that are non-blocking. ALTER TABLE ... ADD COLUMN is the correct DDL statement and executes without locking the table.

Answer

CREATE INDEX idx_priority ON Orders (priority);

Answer

UPDATE Orders SET priority = 0; (no prior column)

Answer

ALTER TABLE Orders ADD priority INT64 NOT NULL DEFAULT 0;

Question 3

A company is using Memorystore for Redis and wants to ensure data persistence in case of a failure. What is the recommended approach?

Accepted Answer

Use Cloud Storage snapshots (export) or configure cross-region replication.. Memorystore for Redis does not support native persistence (AOF/RDB). For data durability, the recommendation is to use scheduled Cloud Storage snapshots or cross-region replication.

Answer

Configure a standard tier instance, which provides automatic persistence.

Answer

Enable RDB persistence and configure the backup interval.

Answer

Enable AOF persistence in Memorystore settings.

Question 4

A Cloud Bigtable instance is experiencing high latency on reads. The operations team suspects a hot spot. Which tool should they use to identify the hot spot?

Accepted Answer

Key Visualiser. Key Visualizer is the correct tool because it is specifically designed to visualize access patterns in Cloud Bigtable and identify hot spots—regions of a table that receive disproportionate read/write traffic. It generates heatmaps of row key access, allowing operators to pinpoint uneven load distribution that causes high latency.

Answer

Stackdriver Debugger

Answer

Bigtable cbt tool

Answer

Cloud Monitoring dashboard

Question 5

An engineer needs to create a cross-region read replica for a Cloud SQL for MySQL instance to improve read scalability and provide disaster recovery. What must be configured on the primary instance to support cross-region replication?

Accepted Answer

Enable binary logging on the primary instance.. Cross-region replication for Cloud SQL for MySQL relies on MySQL's native binary log (binlog) based replication. Enabling binary logging on the primary instance records all data changes in binary log files, which the read replica in another region uses as the source for applying changes. Without binary logging enabled, the primary instance cannot provide the change stream necessary for cross-region replication to function.

Answer

Enable automated backups and point-in-time recovery.

Answer

Configure a Cloud VPN between the regions.

Answer

Set the primary instance to use MySQL 8.0.

Question 6

A team is using Firestore in Native mode. They have a collection 'users' with an array field 'roles'. They need to query all users where roles contains 'admin'. What index configuration is required?

Accepted Answer

Create an index exemption on the 'roles' field.. Firestore supports array membership queries using the 'array-contains' operator. An index exemption for the array field is required because Firestore does not automatically index array fields for array-contains queries. The exemption is created manually.

Answer

Create a single-field index on roles with ascending order.

Answer

Create a composite index on roles and the default field.

Answer

No index is needed; Firestore automatically supports array-contains queries.

Question 7

A company is using Cloud SQL and wants to automatically increase storage when disk usage reaches a threshold. What should they configure?

Accepted Answer

Enable 'auto-storage increase' in the instance settings.. Cloud SQL provides a built-in 'auto-storage increase' setting that, when enabled, automatically increases the instance's storage capacity when disk usage reaches a predefined threshold (typically 90% or when free space drops below a certain amount). This eliminates the need for manual intervention or custom automation, ensuring high availability and preventing out-of-disk errors.

Answer

Use Active Assist recommendations to manually resize.

Answer

Set up a Cloud Monitoring alert to manually increase storage when usage exceeds 80%.

Answer

Configure a Cloud Function to resize the disk via API when threshold is reached.

Question 8

A Cloud Bigtable cluster is currently using HDD storage. The team wants to switch to SSD for better performance. What is the correct approach?

Accepted Answer

Create a new cluster in the same instance with SSD, then delete the old HDD cluster.. In Cloud Bigtable, storage type is a property of the cluster, not the instance. You cannot change the storage type of an existing cluster. The correct approach is to add a new cluster with SSD storage to the same instance, then delete the original HDD cluster. This allows you to migrate without data loss or downtime, as data is replicated across clusters in the same instance.

Answer

Use the gcloud bigtable clusters update command with the --storage-type flag.

Answer

Export the table to Cloud Storage, delete the instance, create a new one with SSD, and import.

Answer

Delete the existing cluster and recreate it with SSD. Data is retained in the instance.

Question 9

A Memorystore for Redis instance is running out of memory. The application can tolerate some data loss but not crashes. The team wants to ensure the instance remains available without manual intervention. Which eviction policy should they configure?

Accepted Answer

allkeys-lru. Option D (allkeys-lru) is correct because it allows Redis to evict the least recently used keys from the entire keyspace when memory is full, which keeps the instance available without crashing. Since the application can tolerate some data loss but not crashes, this policy ensures memory pressure is relieved automatically, preventing out-of-memory errors that would cause the instance to become unavailable.

Answer

volatile-lru

Answer

volatile-ttl

Answer

noeviction

Question 10

An engineer needs to restore a Cloud Spanner database from a backup that was taken 2 hours ago. The backup is stored in Cloud Storage. What steps are required?

Accepted Answer

Use gcloud spanner databases restore --backup= --destination-database=. Cloud Spanner backups are stored within the instance's allocated storage, not directly in Cloud Storage. To restore, you use the backup ID (created via gcloud or console) and restore to a new database. Import/export is a different mechanism.

Answer

Create a new instance and use gcloud spanner instances restore.

Answer

Use gcloud spanner databases import from the Cloud Storage backup file.

Answer

Restore is not possible; you must use point-in-time recovery.

Question 11

A company uses Cloud SQL for PostgreSQL. They need to monitor the replication lag on a read replica. Which metric should they use in Cloud Monitoring?

Accepted Answer

cloudsql.googleapis.com/database/postgresql/replication/replica_lag. For PostgreSQL replicas in Cloud SQL, the metric 'cloudsql.googleapis.com/database/postgresql/replication/replica_lag' measures lag in bytes (seconds can be derived). The metric 'replication_lag' is available for MySQL. For PostgreSQL, the specific metric is 'replica_lag'.

Answer

cloudsql.googleapis.com/database/replication/replica_lag

Answer

cloudsql.googleapis.com/database/replication/lag_seconds

Answer

cloudsql.googleapis.com/database/postgresql/replication/lag

Question 12

A company is designing a mobile application backend that requires real-time synchronization across users and offline support. Which Google Cloud database service is most suitable?

Accepted Answer

Firestore. Firestore is designed for real-time sync, offline support, and mobile/web applications. It provides real-time listeners and offline data persistence.

Answer

Bigtable

Answer

Cloud SQL

Answer

Cloud Spanner

Question 13

A company is using Cloud SQL for MySQL and wants to perform disaster recovery testing by promoting a read replica to a standalone instance. Which TWO actions are required? (Choose 2)

Accepted Answer

Stop replication on the replica using the Cloud Console or gcloud.. To promote a read replica to a standalone instance, you must stop replication on the replica and then promote it. The promotion process converts the replica into an independent primary instance.

Answer

Delete the replica and recreate it as a primary instance.

Answer

Enable point-in-time recovery on the replica.

Answer

Disable binary logging on the replica.

Question 14

A company is migrating on-premises PostgreSQL databases to Cloud SQL. They need to minimize downtime and ensure data consistency. Which THREE steps should they follow? (Choose 3)

Accepted Answer

Use Database Migration Service to set up continuous replication from the on-premises database.. A typical migration uses Database Migration Service (DMS) with continuous replication for minimal downtime. Setting up replication and then promoting the Cloud SQL instance is standard.

Answer

Export the on-premises database to a dump file and import it into Cloud SQL.

Answer

Manually sync data using a custom script with pg_dump and pg_restore.

Question 15

A company is using Cloud Bigtable and wants to set up monitoring and alerting for replication lag between clusters. Which TWO metrics should they use? (Choose 2)

Accepted Answer

cloudbigtable.googleapis.com/cluster/replication_lag. Option B is correct because `replication_lag` directly measures the time difference between the primary cluster and a replica cluster in Cloud Bigtable, which is the key metric for monitoring replication delay. Option D is also correct because `replication_delay` is another metric that tracks the same concept, often reported in seconds, and is used to alert when replicas fall behind. Both metrics are essential for ensuring data consistency and timely failover in multi-cluster Bigtable deployments.

Answer

cloudbigtable.googleapis.com/cluster/disk_usage

Answer

cloudbigtable.googleapis.com/cluster/cpu_load

Answer

cloudbigtable.googleapis.com/cluster/operations_count