Backup of google cloud sql instances database without it being blocked from usage until export

[mayankpi]

I wanted to create the on demand automated system of the google cloud sql instances database but if i use export then it is blocked and can't be used simultaneously until export is completed. Is there any other more efficient way of doing this?

[Darrell_M]

Hi,

To my understanding, exporting from Cloud SQL should not lock you from performing operations. However, it is possible that exporting many large objects cause the instance to become unresponsive[1].

If this is your issue, according to the best practices, you can use serverless export to offload the export operation from the primary instance[2]. This method creates a temporary instance to offload the export operation. This will allow you to perform your usual operations without performance degradation. When the export is completed, the temporary instance is automatically deleted.  

I hope this information was useful to you.

[1] https://cloud.google.com/sql/docs/postgres/known-issues#import-export

[2] https://cloud.google.com/sql/docs/postgres/import-export#serverless

[NSG7712]

What is the cost if I use offload option to export the DB? My db one schema size is 502GB total db size is 1.2 TB 

[bhakiagjunfikor]

Certainly! Here is a comment for backing up Google Cloud SQL instances without blocking usage until export:

/*
Backup Google Cloud SQL instance database without blocking usage.
This script creates a read replica, promotes it, and then exports the data.
It ensures minimal impact on the primary instance's performance.
Note: Adjust variables like [PROJECT_ID], [INSTANCE_NAME], [BACKUP_NAME], and [BUCKET_NAME] accordingly.

Steps:
1. Create a read replica.
2. Promote the read replica to the master.
3. Export the data from the master.
4. Cleanup: Delete the read replica.

Usage:
Run this script in the Cloud SQL instance's database.

Reference:
- https://cloud.google.com/sql/docs/mysql/replication#read-replicas

Example:
gcloud sql connect [INSTANCE_NAME] source('path/to/backup_script.sql');

Author: YourName
Date: YYYY-MM-DD
*/

Remember to replace [PROJECT_ID], [INSTANCE_NAME], [BACKUP_NAME], and [BUCKET_NAME] with your actual Google Cloud project ID, instance name, desired backup name, and Cloud Storage bucket name. Additionally, modify the example usage with your specific cloud command for connecting to the SQL instance.

Always test scripts in a non-production environment before running them in a production setting.