GCP Cloud SQL Postgres Transaction Log Backup / Recovery

0
votes

I would like to recover (restore) a GCP Cloud Postgres SQL up to the last 15 minutes for DR purposed (RPO is 15 minutes). That means the database must be backed up (typically the transaction log) every 15 minutes. Is this possible in Cloud Postgres and if so, what's the process?

In addition I am concerned about someone or an application bug deleting data. This just happened to us on another system. Ideally, it would be very beneficial to restore the DB backup and the 15 minute incremental transaction log backups to another DB and pick and choose the data that needs to be recovered. Is this possible?

1
google-cloud-sql

1 Answers

1
votes

the database must be backed up (typically the transaction log) every 15 minutes.

You can not back up the transaction log, you only back up instance Data. To back up your instance every 15 minutes, the automatic backup would not be enough. Thus, you must use on-demand backup and trigger it every 15 minutes.

restore the DB backup and the 15 minute incremental transaction log backups to another DB

Yes, you can restore an instance backup to another instance

pick and choose the data that needs to be recovered.

Not in an easy way. You could implement this in some way by not creating an on-demand back up but instead export a SQL dump file or a CSV file and then running your own process to obtain the data you need.

Having said that, and your reference to DR (Disaster Recovery), I would like to point out that Cloud SQL has Automatic Fail-Over Replicas called High Availability. Also, for replication you could use Read-Replicas