GCP Associate Cloud Engineer Practice Question 67: Cloud SQL Point in Time Recovery

Google Cloud Associate Cloud Engineer · Test 7 · question 7 of 10

Cloud SQLmedium

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?

Select one answer
Show answer and explanation

Answer:

  • B. Clone the instance to a new instance using point-in-time recovery, specifying a timestamp of 02:13 today, then export the table's data from the clone into production

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

Practice Test 7 ›

Study guides for Google Cloud Associate Cloud Engineer