GCP Associate Cloud Engineer Practice Question 67: Cloud SQL Point in Time Recovery
Google Cloud Associate Cloud Engineer · Test 7 · question 7 of 10
A batch job that runs nightly maintenance scripts against a production Cloud SQL for PostgreSQL instance had a bug: at 02:14 today it dropped a table that the application still needs. Automated backups and point-in-time recovery are enabled on the instance, and the transaction log retention covers the last several days. You need to recover the table's data as it existed just before 02:14 today, and you must not lose any other data or disrupt the running production instance in the process.
What should you do?
Show answer and explanation
Answer:
Because automated backups and point-in-time recovery (transaction log based recovery) are enabled, Cloud SQL can clone the instance to a separate instance representing its exact state at any timestamp covered by the retained logs. Cloning to a moment just before the drop recreates the table with the latest good data, and doing the recovery into a new instance leaves the running production instance untouched; the table's data can then be exported or copied back in.
Option A only has last night's backup, so restoring it would discard every legitimate change made between that backup and 02:14, far more data loss than necessary when point-in-time recovery is available. Option C enables automatic failover for a zonal outage; it has no effect on data that was already deleted by application logic, since the deletion would replicate to the standby too. Option D does not help either: a read replica continuously applies the primary's changes, so the dropped table is also gone from the replica by the time you promote it.
Reference: Perform point-in-time recovery (PITR), Cloud SQL for PostgreSQL