Best practices for importing and exporting
The following are best practices to consider when importing and exporting data:
- Don't use Cloud Storage Requester Pays buckets.
- Use serverless export to offload the export operation from the primary instance.
- Use the correct flags when you create a SQL dump file.
- Compress data to reduce cost.
- Reduce long-running import and export processes.
- Verify the imported database.
Don't use Cloud Storage Requester Pays buckets
You cannot use a Cloud Storage bucket that has Requester Pays enabled for imports and exports from Cloud SQL.
Use serverless export to offload the export operation from the primary instance
For a standard export from Cloud SQL, the export is run while the database is online. When the databases being exported are smaller, the impact is likely to be minimal. However, when there are large databases, or large objects, such as BLOBs in the database, there's the possibility that the export might degrade database performance. This might impact the time it takes to perform database queries and operations against the database. Once you start an export, it is not possible to stop it if your database starts to respond slowly.
To prevent slow responses during an export, you can use serverless export. With serverless export, Cloud SQL creates a separate, temporary instance to offload the export operation. Offloading the export operation allows databases on the primary instance to continue to serve queries and perform operations at the usual performance rate. When the data export is complete, the temporary instance is automatically deleted.
gcloud or REST
export functions, with the
offload flag, to perform a
serverless export operation.
During a serverless export operation you can run some other operations, such as
instance edit, import, and failover. However, if you select
the export operation stops some time after you delete the instance, and it
doesn't export any data.
A serverless export takes longer to do than a standard export, because it takes time to create the temporary instance. At a minimum, it takes longer than five minutes, but for larger databases, it might be longer. Consider the impact to time and performance before determining which type of export to use.
You can use serverless export on a primary instance or a read replica.
Use the correct flags when you create a SQL dump file
If you do not use the right procedure when you export data to a SQL dump file, your import might be unsuccessful. For information about creating a SQL dump file for import into Cloud SQL, see Exporting data.
Compress data to reduce cost
Cloud SQL supports importing and exporting both compressed and uncompressed files. Compression can save significant storage space on Cloud Storage and reduce your storage costs, especially when you are exporting large instances.When you export a SQL dump or CSV file, use a
.gzfile extension to compress the data. When you import a file with an extension of
.gz, it is decompressed automatically.
Reduce long-running import and export processes
Imports and exports into Cloud SQL using the import functionality (with a Cloud Storage bucket) can take a long time to complete, depending on the size of the database. This can have the following impacts:
- You cannot stop a long-running Cloud SQL instance operation.
- You can perform only one import or export operation at a time for each instance.
You can decrease the amount of time it takes to complete each operation by using the Cloud SQL import or export functionality with smaller batches of data.
For exports, you can use serverless export to minimize the impact on database performance and allow other operations to run on your instance while an export is running. For more tips, see Diagnosing Issues with Cloud SQL Instances.
Verify the imported database
After an import operation is complete, connect to your database and run the appropriate database commands to make sure the contents are correct. For example, connect and list the databases, tables, and specific entries.
Cloud Storage supports a maximum single-object size up five terabytes. If you have databases larger than 5TB, the export operation to Cloud Storage will fail. In that case, you need to break down your export files into smaller segments.
Automating export operations
Although Cloud SQL doesn't provide a built-in way to automate database exports, you can build your own automation tool using several Google Cloud components. To learn more, see this tutorial.
|The import operation is taking too long.||Too many active connections can interfere with import operations.
Close unused operations. Check the CPU and memory usage of your Cloud SQL instance to make sure there are plenty of resources available. The best way to ensure maximum resources for the import is to restart the instance before beginning the operation.
|An import operation can fail when one or more users referenced in the dump file don't exist.||Before importing a dump file, all the database users who own objects or
were granted permissions on objects in the dumped database must exist in the
target database. If they don't, the import operation fails to recreate the
objects with the original ownership or permissions.
Create the database users before importing.
|CSV export worked but SQL export failed.||CSV and SQL formats do export differently. The SQL format exports the
entire database, and likely takes longer to complete. The CSV format lets
you define which elements of the database to include in the export.
Use CSV exports to export only what you need.
|Export is taking too long.||Cloud SQL does not support concurrent synchronous operations.
Use export offloading. At a high level, in export offloading, instead of issuing an export on the source instance, Cloud SQL spins up an offload instance to perform the export. Export offloading has several advantages, including increased performance on the source instance and the unblocking of administrative operations while the export is running. With export offloading, total latency can increase by the amount of time it takes to bring up the offload instance. Generally, for reasonably sized exports, latency is not significant. However, if your export is small enough, then you may notice the increase in latency.
|Create Extension error.||The dump file contains references to unsupported extension.|
||The tool requires superuser role, and the superuser role is not supported in Cloud SQL for PostgreSQL.|
|The export operation times out before exporting anything, and you see
the error message
||If Cloud Storage does not receive any data within a certain
time frame, typically around seven minutes, the connection resets. It's
possible the initial export query is taking too long to run.
Do a manual export using the
|You want exports to be automated.||Cloud SQL does not provide a way to automate exports.
You could build your own automated export system using Google Cloud products such as Cloud Scheduler, Pub/Sub, and Cloud Functions, similar to this article on automating backups.
- Learn how to import and export data using PG dump files.
- Learn how to import and export data using CSV files.
- Learn how to enable automatic backups.
- Learn how to restore from backups.