This page describes exporting and importing data into Cloud SQL instances using SQL dump files.
Before you begin
Exports use database resources, but they do not interfere with normal database operations unless the instance is under-provisioned.
For best practices, see Best Practices for Importing and Exporting Data.
After completing an import operation, verify the results.
Export data from Cloud SQL for PostgreSQL
Required roles and permissions for exporting from Cloud SQL for PostgreSQL
To export data from Cloud SQL into Cloud Storage, the user initiating the export must have one of the following roles:
- The Cloud SQL Editor role
- A custom role,
including the following permissions:
cloudsql.instances.getcloudsql.instances.export
Additionally, the service account for the Cloud SQL instance must have one of the following roles:
- The
storage.objectAdminIdentity and Access Management (IAM) role - A custom role, including the following permissions:
storage.objects.createstorage.objects.list(for exporting files in parallel only)storage.objects.delete(for exporting files in parallel only)
For help with IAM roles, see Identity and Access Management.
Export to a SQL dump file from Cloud SQL for PostgreSQL
When you use Cloud SQL to perform an export, whether from the Google Cloud console, the gcloud CLI, or the API, you are using thepg_dump
utility, with the options required to ensure that the resulting export file is
valid for import back into Cloud SQL.
If you plan to import your data into Cloud SQL, you must follow the instructions provided in Exporting data from an external database server so that your SQL dump file is formatted correctly for Cloud SQL.
To export data from a database on a Cloud SQL instance to a SQL dump file in a Cloud Storage bucket:
Console
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- To open the Overview page of an instance, click the instance name.
- Click Export.
- In the File format section, click SQL to create a SQL dump file.
- In the Data to export section, use the drop-down menu to select the database you want to export from.
- In the Destination section, select Browse to search for a Cloud Storage bucket or folder for your export.
- Click Export to begin the export.
gcloud
- Create a Cloud Storage bucket.
- Find the service account for the Cloud SQL instance you're exporting
from. You can do this running the
gcloud sql instances describecommand. Look for theserviceAccountEmailAddressfield in the output.gcloud sql instances describe INSTANCE_NAME
- Use
gcloud storage buckets add-iam-policy-bindingto grant thestorage.objectAdminIAM role to the service account. For help with setting IAM permissions, see Using IAM permissions. - Export the database to your Cloud Storage bucket:
gcloud sql export sql INSTANCE_NAME gs://BUCKET_NAME/sqldumpfile.gz \ --database=DATABASE_NAME \ --offload
The
export sqlcommand does not contain triggers or stored procedures, but does contain views. To export triggers and/or stored procedures, use the pg_dump tool.For more information about using the
export sqlcommand, see thesql export sqlcommand reference page. - If you do not need to retain the IAM role you set previously, revoke it now.
REST v1
- Create a bucket for the export:
gcloud storage buckets create gs://BUCKET_NAME --project=PROJECT_NAME --location=LOCATION_NAME
This step is not required, but strongly recommended, so you do not open up access to any other data.
- Provide your instance with the
legacyBucketWriterIAM role for your bucket. For help with setting IAM permissions, see Using IAM permissions. - Export your database:
Before using any of the request data, make the following replacements:
- PROJECT_ID: the project ID
- INSTANCE_ID: the instance ID
- BUCKET_NAME: the Cloud Storage bucket name
- PATH_TO_DUMP_FILE: the path to the SQL dump file
- DATABASE_NAME_1: the name of a database inside the Cloud SQL instance
- DATABASE_NAME_2: the name of a database inside the Cloud SQL instance
HTTP method and URL:
POST https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID/export
Request JSON body:
{ "exportContext": { "fileType": "SQL", "uri": "gs://BUCKET_NAME/PATH_TO_DUMP_FILE", "databases": ["DATABASE_NAME"], "offload": TRUE | FALSE, "sqlExportOptions": { "clean": [TRUE|FALSE], "ifExists": [TRUE|FALSE] } } }To send your request, expand one of these options:
You should receive a JSON response similar to the following:
- If you do not need to retain the IAM role you set previously, remove it now.
REST v1beta4
- Create a bucket for the export:
gcloud storage buckets create gs://BUCKET_NAME --project=PROJECT_NAME --location=LOCATION_NAME
This step is not required, but strongly recommended, so you do not open up access to any other data.
- Provide your instance with the
storage.objectAdminIAM role for your bucket. For help with setting IAM permissions, see Using IAM permissions. - Export your database:
Before using any of the request data, make the following replacements:
- PROJECT_ID: the project ID
- INSTANCE_ID: the instance ID
- BUCKET_NAME: the Cloud Storage bucket name
- PATH_TO_DUMP_FILE: the path to the SQL dump file
- DATABASE_NAME_1: the name of a database inside the Cloud SQL instance
- DATABASE_NAME_2: the name of a database inside the Cloud SQL instance
HTTP method and URL:
POST https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID/export
Request JSON body:
{ "exportContext": { "fileType": "SQL", "uri": "gs://BUCKET_NAME/PATH_TO_DUMP_FILE", "databases": ["DATABASE_NAME"], "offload": TRUE | FALSE, "sqlExportOptions": { "clean": [TRUE|FALSE], "ifExists": [TRUE|FALSE] } } }To send your request, expand one of these options:
You should receive a JSON response similar to the following:
- If you do not need to retain the IAM role you set previously, revoke it now.
Import data to Cloud SQL for PostgreSQL
Required roles and permissions for importing to Cloud SQL for PostgreSQL
To import data from Cloud Storage into Cloud SQL, the user initiating the import must have one of the following roles:
- The Cloud SQL Admin role
- A custom role,
including the following permissions:
cloudsql.instances.getcloudsql.instances.import
Additionally, the service account for the Cloud SQL instance must have one of the following roles:
- The
storage.objectAdminIAM role - A custom role, including the following permissions:
storage.objects.getstorage.objects.list(for importing files in parallel only)
For help with IAM roles, see Identity and Access Management.
Import a SQL dump file to Cloud SQL for PostgreSQL
SQL files are plain text files with a sequence of SQL commands.
Console
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- To open the Overview page of an instance, click the instance name.
- Click Import.
- In the Choose the file you'd like to import data from section, enter the path
to the bucket and SQL dump file to use for the import, or browse to an existing
file.
You can import a compressed (
.gz) or an uncompressed (.sql) file. - For Format, select SQL.
Select the database you want the data to be imported into.
This causes Cloud SQL to run the
USE DATABASEstatement before the import.If you want to specify a user to perform the import, select the user.
If your import file contains statements that must be performed by a specific user, use this field to specify that user.
- Click Import to start the import.
gcloud
- Create a Cloud Storage bucket.
Upload the file to your bucket.
For help with uploading files to buckets, see Uploading objects.
- Describe the instance you are importing to:
gcloud sql instances describe INSTANCE_NAME
- Copy the
serviceAccountEmailAddressfield. - Use
gcloud storage buckets add-iam-policy-bindingto grant thestorage.objectAdminIAM role to the service account for the bucket.gcloud storage buckets add-iam-policy-binding gs://BUCKET_NAME \ --member=serviceAccount:SERVICE-ACCOUNT \ --role=roles/storage.objectAdmin
- Import the database:
gcloud sql import sql INSTANCE_NAME gs://BUCKET_NAME/IMPORT_FILE_NAME \ --database=DATABASE_NAME
For information about using the
import sqlcommand, see thesql import sqlcommand reference page.If the command returns an error like
ERROR_RDBMS, review the permissions; this error is often due to permissions issues. - If you do not need to retain the IAM permissions you
set previously, remove them using
gcloud storage buckets remove-iam-policy-binding.
REST v1
-
Create a SQL dump file. The linked instructions set certain flags that make the dump file compatible with Cloud SQL.
- If you are importing data from an on-premises PostgreSQL server:
- Create a SQL dump file using the instructions in Exporting data using pg_dump.
- Create a bucket in Cloud Storage using the instructions in Create buckets.
- Upload the SQL dump file to the Cloud Storage bucket using the procedure in Upload objects.
- Create a Cloud Storage bucket.
Upload the file to your bucket.
For help with uploading files to buckets, see Uploading objects.
- Provide your instance with the
legacyBucketWriterandobjectViewerIAM roles for your bucket. For help with setting IAM permissions, see Using IAM permissions. - Import your dump file:
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
- bucket_name: The Cloud Storage bucket name
- path_to_sql_file: The path to the SQL file
- database_name: The name of a database inside the Cloud SQL instance
HTTP method and URL:
POST https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id/import
Request JSON body:
{ "importContext": { "fileType": "SQL", "uri": "gs://bucket_name/path_to_sql_file", "database": "database_name" } }To send your request, expand one of these options:
You should receive a JSON response similar to the following:
To use a different user for the import, specify the
For the complete list of parameters for the request, see the instances:import page.importContext.importUserproperty. - If you do not need to retain the IAM permissions you set previously, remove them now.
REST v1beta4
-
Create a SQL dump file. The linked instructions set certain flags that make the dump file compatible with Cloud SQL.
- If you are importing data from an on-premises PostgreSQL server:
- Create a SQL dump file using the instructions in Exporting data using pg_dump.
- Create a bucket in Cloud Storage using the instructions in Create buckets.
- Upload the SQL dump file to the Cloud Storage bucket using the procedure in Upload objects.
- Create a Cloud Storage bucket.
Upload the file to your bucket.
For help with uploading files to buckets, see Uploading objects.
- Provide your instance with the
storage.objectAdminIAM role for your bucket. For help with setting IAM permissions, see Using IAM permissions. - Import your dump file:
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
- bucket_name: The Cloud Storage bucket name
- path_to_sql_file: The path to the SQL file
- database_name: The name of a database inside the Cloud SQL instance
HTTP method and URL:
POST https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/import
Request JSON body:
{ "importContext": { "fileType": "SQL", "uri": "gs://bucket_name/path_to_sql_file", "database": "database_name" } }To send your request, expand one of these options:
You should receive a JSON response similar to the following:
To use a different user for the import, specify the
For the complete list of parameters for the request, see the instances:import page.importContext.importUserproperty. - If you do not need to retain the IAM permissions you set previously, remove them now.
What's next
- Learn how to check the status of import and export operations.
- Learn more about best practices for importing and exporting data.
- Learn more about Cloud Storage.
- Known issues for imports and exports.