Restored Cloud SQL instance shows read‑inconsistency after backup

Likely explanation

After a Cloud SQL backup restore, the instance is reported as RUNNING as soon as the restore API finishes, but internal crash‑recovery or redo‑log application may still be running. This creates a window where reads can return data that is older than the backup point. The length of this window is not fixed; it depends on instance size, storage throughput, and the write load at the time of the backup, typically ranging from a few seconds up to several minutes.

Confirmed facts

• Cloud SQL does not expose a dedicated “restore‑complete” flag or metric that directly signals when the instance has caught up to the backup snapshot.
• For PostgreSQL, the function pg_last_xact_replay_timestamp() returns NULL when recovery is complete.
• For MySQL, SHOW ENGINE INNODB STATUS shows no active recovery threads and the LSN has advanced past the backup LSN when recovery finishes.
• Cloud Monitoring metrics such as cloudsql.googleapis.com/database/replication_lag on a read replica drop to zero once the replica has applied all changes from the primary, providing an external signal that roughly matches internal consistency.
• A deterministic readiness check (e.g., querying a heartbeat table updated at backup time) can be used to block reads until the instance returns the expected timestamp or a later one.

Steps needed for this case

1. Wait until the restored instance reports a RUNNING state via the Cloud SQL API or Console.
2. Depending on the database engine, run the appropriate consistency check:
   • PostgreSQL: Execute SELECT pg_last_xact_replay_timestamp(); repeat until the result is NULL.
   • MySQL: Execute SHOW ENGINE INNODB STATUS; verify that there are no active recovery threads and that the LSN shown is greater than the LSN recorded at backup time.
3. If you prefer an application‑level check, query a known table that was updated at backup time (e.g., SELECT MAX(updated_at) FROM heartbeat WHERE source='backup') and compare the result to the backup timestamp; resume reads only when the returned timestamp is ≥ the backup timestamp.
4. Automate the check with a startup script, Cloud Function, or health‑check loop that polls until the condition is met before allowing traffic to the instance.