Point-in-Time Recovery, or PITR, is a Cloud SQL feature that lets you restore a database to any past state rather than only to the moment a backup was taken. It gives you more granular control over recovery than a standard backup and restore, and the Professional Cloud Database Engineer exam tends to test the details that make it work. To recover to a specific moment, Cloud SQL combines a previous backup with a continuous stream of transaction logs, and several of the things you need to know are about which logging mechanism each engine uses, how long the logs are kept, and where they live.
PITR depends on two pieces being present together. The first is a backup, which serves as the starting point for the restore. The second is a continuous record of every change made to the database from that backup forward, captured in transaction logs. When you ask Cloud SQL to recover to a chosen recovery point timestamp, it takes the most recent backup before that moment and then replays the transaction logs to carry the data forward to the exact point you specified. The backup is the anchor, and the logs fill the gap between that backup and your recovery point.
Because both pieces are required, PITR only works across the window where you have both a backup and an unbroken set of transaction logs leading up to the point you want. If the logs for a given period are not retained, you cannot recover to a moment inside that period.
Cloud SQL implements the transaction log differently depending on the database engine, and the exam expects you to know which is which. Cloud SQL for MySQL uses binary logging, and Cloud SQL for PostgreSQL uses write-ahead logging, often abbreviated as WAL.
Binary logging records all changes to the database in a binary format after the transaction is committed. Each log entry captures a modification that has already been applied to the data. Write-ahead logging works in the other order. It logs changes before applying them to the database, so no change is finalized unless it has first been written to the log. That ordering is what protects the integrity of the system during a crash, since the log already holds a durable record of what was about to happen. The practical takeaway for the exam is the mapping: MySQL means binary logs, PostgreSQL means write-ahead logs.
How far back you can perform a recovery is controlled by how long the transaction logs are kept, which is set by the number of retention days. This is the window that determines the oldest moment you can restore to. You can configure it through the Google Cloud console, through the API parameter transactionLogRetentionDays, or with the gcloud flag --retained-transaction-log-days.
The range you are allowed to set depends on the instance edition. In the Enterprise edition, transaction logs can be retained for 1 to 7 days. In the Enterprise Plus edition, that range expands to 1 to 35 days. When a scenario calls for the ability to recover to a point further back than a week, the longer retention window available in Enterprise Plus is the relevant detail.
One more detail can affect an instance, and that is where the transaction logs are stored. Older Cloud SQL Enterprise instances retain transaction logs on the primary disk, while modern instances for both editions now use Cloud Storage for these logs. Keeping logs on the primary disk means they consume the same space your database files need, which can contribute to disk pressure. Offloading them to Cloud Storage prevents that competition for space.
There is a nuance here that exam scenarios lean on. Even though both editions currently use Cloud Storage for logs, a scenario might still present migrating to Enterprise Plus as the way to ensure logs are offloaded and disk pressure is resolved. We would treat that as a signal to watch for when a question ties transaction log storage to running out of disk space rather than to recovery itself.
Our Professional Cloud Database Engineer course covers Cloud SQL point-in-time recovery alongside backups and high availability, with practice questions that drill these distinctions.
