Point-in-time recovery, or PITR, is a Cloud SQL feature that lets you restore the database to any specific moment in the past. Not just the last backup. Any moment. The Associate Cloud Engineer exam expects you to know what PITR is, the underlying mechanism that makes it possible, and the scenarios where you would use it. This article covers all three.
It does not cover the full backup and restore matrix in Cloud SQL, retention policies for every edge case, or the operational details of running a PITR in production. The goal is what the Associate Cloud Engineer exam tests, which is the concept and the difference between MySQL and PostgreSQL.
Suppose someone runs a bad UPDATE at 2:47 PM on Tuesday and accidentally clears a column on every row in a critical table. You realize at 3:15 PM. You do not want to restore last night's backup, because that would lose all the legitimate work from this morning. What you want is to restore to 2:46 PM, the moment before the bad write.
That is point-in-time recovery. Cloud SQL keeps enough information about every change to the database that it can replay history up to a precise moment, and stop there. The result is a new database instance in the state it was in at that moment.
PITR is built on top of two things: a base backup and a continuous log of every change made to the database since that backup. To restore to a point in time, Cloud SQL takes the most recent backup before that point, and then replays the log forward up to the requested moment.
The change log is where MySQL and PostgreSQL differ.
MySQL records every change as a binary log entry, called a binlog, after the transaction is committed. The binlog captures the change in a binary format, and that record can be replayed on any other MySQL instance to reproduce the same state.
For PITR in Cloud SQL with MySQL, the binlog is the change history. Cloud SQL stores it. When you restore to a point in time, Cloud SQL replays binlog entries up to that point.
PostgreSQL uses a different approach called write-ahead logging, or WAL. The idea is that every change is written to the WAL before it is applied to the actual database. This is a durability mechanism, because if the database crashes mid-write, recovery can replay the WAL and get back to a consistent state.
For PITR, that same WAL is the change history. Cloud SQL preserves it, and a restore replays WAL entries up to the requested moment.
The mechanism is different but the function is the same. Binary logging happens after the transaction commits and records the change in binary format. WAL happens before the change is applied and ensures the change can be replayed safely.
For the Associate Cloud Engineer exam, you do not need to know the internals beyond this. What matters is: PITR exists in Cloud SQL for both MySQL and PostgreSQL. MySQL uses binary logging. PostgreSQL uses write-ahead logging. Both enable the same recovery feature.
PITR is not free. It requires Cloud SQL to keep transaction logs around, which uses storage. By default, Cloud SQL retains transaction logs for a configurable retention window, and you can only restore to points within that window. This is a tradeoff: longer retention gives you more flexibility but costs more storage.
You also have to enable PITR on the instance for it to work. It is not automatic on every Cloud SQL instance. If a question mentions a database where PITR was needed but not available, the cause is usually that PITR was not enabled.
If you see a scenario describing accidental data corruption, a bad write that needs to be undone, or a need to restore to a specific moment rather than just the last full backup, that is a PITR question. The answer is point-in-time recovery, enabled on the instance.
If you see a question that asks about the underlying mechanism, the answer depends on the engine. MySQL uses binary logging. PostgreSQL uses write-ahead logging. Some exam questions are this specific.
If you see a scenario where PITR was attempted but did not work, the cause is usually that it was not enabled, or the desired restore point was outside the transaction log retention window.
gcloud sql backups restore BACKUP_ID \
--restore-instance=my-restored-instance \
--backup-instance=my-original-instanceFor an actual PITR with a precise timestamp, the command uses gcloud sql instances clone with a point-in-time flag. The exam will not ask you to write the command from memory, but recognize that PITR creates a new instance, not an in-place rollback.
PITR is the feature. Binary logging is the MySQL mechanism, write-ahead logging is the Postgres mechanism, and they accomplish the same goal. The Associate Cloud Engineer exam tests three things: that PITR exists and what it is for, the difference between binlog and WAL, and that PITR is the right answer when a scenario describes recovering to a specific moment.
My Associate Cloud Engineer course covers PITR alongside automatic backups, manual backups, and scheduled exports in the Cloud SQL section.
