Cloud SQL point in time Data residency

Question

I use point in time recovery on CloudSQL for PostgreSQL. The instance is highly available thus replicated to three availability zones. When enabling point in time on the GCP console it is clearly mentioned that the storage usage on the instance will be increased. This brings the following questions

• Are point in time data located only on the original main instance?
• Are point in time data replicated to multiple zones?
• Do follower instances have point in time enabled if yes is this isolated from the other instances?
• In case of an unsuccessful failover when the main database is unresponsive and thus manual provisioning of the Database is needed, is point in time feasible? If yes is it going to be with the latest data (provided the point in time data of the main database are regionally replicated), or the data from the point in time functionality from a follower instance?

Answer 1

1. Are point in time data located only on the original main instance?

Yes. You cannot enable PITR in a read replica. You can only enable it on the main instance. The "data" is write ahead logs and they are stored on the primary instance. See.

2. Are point in time data replicated to multiple zones?

No, PITR is implemented with write-ahead logs and these are only stored in the data disk of the primary instance.

3. Do follower instances have point in time enabled if yes is this isolated from the other instances?

If by follower instances you are referring to read replicas, read replicas leverage write-ahead logs (same as with PITR) to stay up to date with the primary. Read replicas are meant to be copies of the primary, as up to date as possible.

4. In case of an unsuccessful failover when the main database is unresponsive and thus manual provisioning of the Database is needed, is point in time feasible? If yes, is it going to be with the latest data (provided the point in time data of the main database are regionally replicated), or the data from the point in time functionality from a follower instance?

If the primary is unresponsive and there is an issue with the instance data disk, you will not be able to perform a PITR. This is because PITR for Postgres uses write-ahead logs which are stored on the instance data disk, see. If the instance's data disk has no issues, then the PITR should work.

Best practices for disaster recovery however would be to have a cross-region read replica configured for the instance which would provide an active copy of the instance in a region distinct from the primary. Even if the primary region goes down the cross-region, read replica could be promoted to a standalone instance and made available for both writes and reads. This of course requires setting up such a configuration -before- you need it.