Importing data into Cloud SQL

This page describes how to import data into Cloud SQL instances. You can import data from SQL dump or CSV files.

For best practices for importing data, see Best Practices for Importing and Exporting Data.

Importing data from a SQL dump file into Cloud SQL

Before you begin

  • This procedure requires you to import a file from Cloud Storage. To import data from Cloud Storage, the Cloud SQL instance's service account or the user must have the Cloud SQL Client role and at least the roles/storage.legacyBucketReader IAM role. If the account or user is also performing export operations, grant the Storage Object Admin (storage.objectAdmin) IAM role set. For help with IAM roles, see Cloud Identity and Access Management for Cloud Storage.

    You can find the instance's service account name in the Google Cloud Console on your instance's Overview page. You can verify the roles for your Cloud Storage bucket by using the gsutil tool:

    gsutil iam get gs://[BUCKET_NAME]

    Learn more about using IAM with buckets.

  • Create a SQL dump file. Use instructions from the bullet below that applies to your situation. These instructions set certain flags that make the dump file compatible with Cloud SQL.

  • Before importing a SQL dump, ensure that all the users who own objects or were granted permissions on objects in the dumped database exist in the new database. If they do not, the import will fail to recreate the objects with the original ownership and/or permissions. (Sometimes this is what you want, but usually it is not.)

    For help creating users, see Creating users.

Importing data from a SQL dump file in Cloud Storage

To import data from a SQL dump file to a Cloud SQL instance:

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Console.

    Go to the Cloud SQL Instances page

  2. Select the instance to open its Overview page.
  3. Click Import in the button bar.
  4. Under Choose the file you'd like to import data from, enter the path to the bucket and SQL dump file to use for the import. Or to browse to the file:
    1. Click Browse.
    2. Under Location, double-click the name of the bucket in the list.
    3. Select the file in the list.
    4. Click Select.

    You can import a compressed (.gz) or an uncompressed (.sql) file.

  5. For Format, select SQL.

  6. Select the database you want the data to be imported into.

    This causes Cloud SQL to run the USE DATABASE statement before the import.

  7. If you want to specify a PostgreSQL user to perform the import, select the user.

    If your import file contains statements that must be performed by a specific PostgreSQL user, use this field to specify that user.

  8. Click Import to start the import.

gcloud

  1. Create a Cloud Storage bucket, if you haven't already.

    For help with creating a bucket, see Creating Storage Buckets.

  2. Upload the file to your bucket.

    For help with uploading files to buckets, see Uploading objects.

  3. Describe the instance you are importing to: 
    gcloud sql instances describe [INSTANCE_NAME]
  4. Copy the serviceAccountEmailAddress field.
  5. Use gsutil iam to grant the storage.objectAdmin IAM role to the service account for the bucket. 
      gsutil iam ch serviceAccount:[SERVICE-ACCOUNT]:objectAdmin \
  gs://[BUCKET-NAME]
    For help with setting IAM permissions, see Using IAM permissions.
  6. Import the database: 
    gcloud sql import sql [INSTANCE_NAME] gs://[BUCKET_NAME]/[IMPORT_FILE_NAME] \
                            --database=[DATABASE_NAME]

    For information about using the import sql command, see the sql import sql command reference page.

    If the command returns an error like `ERROR_RDBMS`, review the permissions; this error is often due to permissions issues.

  7. If you do not need to retain the IAM permissions you set previously, remove them using gsutil iam.

REST v1beta4

    Create a SQL dump file. Use instructions from the bullet below that applies to your situation. These instructions set certain flags that make the dump file compatible with Cloud SQL.

  1. Create a Cloud Storage bucket, if you haven't already.

    For help with creating a bucket, see Creating Storage Buckets.

  2. Upload the file to your bucket.

    For help with uploading files to buckets, see Uploading objects.

  3. Provide your instance with the storage.objectAdmin IAM role for your bucket. For help with setting IAM permissions, see Using IAM permissions.
  4. Import your dump file:

    Before using any of the request data below, 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://www.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 importContext.importUser property.

    For the complete list of parameters for this request, see the instances:import page.
  5. If you do not need to retain the IAM permissions you set previously, remove them now.
To see how the underlying REST API request is constructed for this task, see the APIs Explorer on the instances:import page.

Importing data from CSV files into Cloud SQL

Before you begin

  • This procedure requires you to import a file from Cloud Storage. To import data from Cloud Storage, the Cloud SQL instance's service account or the user must have the Cloud SQL Client role and at least the roles/storage.legacyBucketReader IAM role. If the account or user is also performing export operations, grant the Storage Object Admin (storage.objectAdmin) IAM role set. For help with IAM roles, see Cloud Identity and Access Management for Cloud Storage.

    You can find the instance's service account name in the Google Cloud Console on your instance's Overview page. You can verify the roles for your Cloud Storage bucket by using the gsutil tool:

    gsutil iam get gs://[BUCKET_NAME]

    Learn more about using IAM with buckets.

  • The database and table you are importing into must already exist on your Cloud SQL instance. For help with creating a database, see Creating a database. To create a table in the database, use the CREATE TABLE SQL statement in the psql client.
  • Your CSV file must conform to the CSV file format requirements below.

CSV file format requirements

CSV files must have one line for each row of data and use comma-separated fields.

To see instructions for exporting to a CSV file formatted for Cloud SQL, see Exporting data from Cloud SQL to a CSV file.

Importing data from a CSV file in Cloud Storage

To import data to a Cloud SQL instance using a CSV file:

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Console.

    Go to the Cloud SQL Instances page

  2. Select the instance to open its Overview page.
  3. Click Import in the button bar.
  4. Under Choose the file you'd like to import data from, enter the path to the bucket and CSV file to use for the import. Or to browse to the file:
    1. Click Browse.
    2. Under Location, double-click the name of the bucket in the list.
    3. Select the file in the list.
    4. Click Select.

    You can import a compressed (.gz) or an uncompressed (.csv) file.

  5. Under Format, select CSV.
  6. Specify the Database and Table in your Cloud SQL instance where you want to import the CSV file.
  7. You can optionally specify a user to use for the import.
  8. Click the Import to start the import.

gcloud

  1. Create a Cloud Storage bucket, if you haven't already.

    For help with creating a bucket, see Creating Storage Buckets.

  2. Upload the file to your bucket.

    For help with uploading files to buckets, see Uploading objects.

  3. Upload data from the CSV file to the bucket.
  4. Describe the instance you are exporting from: 
    gcloud sql instances describe [INSTANCE_NAME]
  5. Copy the serviceAccountEmailAddress field.
  6. Use gsutil iam to grant the storage.objectAdmin IAM role to the service account for the bucket. For help with setting IAM permissions, see Using IAM permissions.
  7. Import the file: 
    gcloud sql import csv [INSTANCE_NAME] gs://[BUCKET_NAME]/[FILE_NAME] \
                            --database=[DATABASE_NAME] --table=[TABLE_NAME]

    For information about using the import csv command, see the sql import csv command reference page.

  8. If you do not need to retain the IAM permissions you set previously, remove them using gsutil iam.

REST v1beta4

  1. Create a Cloud Storage bucket, if you haven't already.

    For help with creating a bucket, see Creating Storage Buckets.

  2. Upload the file to your bucket.

    For help with uploading files to buckets, see Uploading objects.

  3. Provide your instance with the storage.objectAdmin IAM role for your bucket. For help with setting IAM permissions, see Using IAM permissions.
  4. Import data from the file:

    Before using any of the request data below, make the following replacements:

    • project-id: The project ID
    • instance-id: The instance ID
    • bucket_name: The Cloud Storage bucket name
    • path_to_csv_file: The path to the CSV file
    • database_name: The name of a database inside the Cloud SQL instance
    • table_name: The name of the database table

    HTTP method and URL: 

    POST https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/import

    Request JSON body: 

    {
 "importContext":
   {
      "fileType": "CSV",
      "uri": "gs://bucket_name/path_to_csv_file",
      "database": "database_name",
      "csvImportOptions":
       {
         "table": "table_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 importContext.importUser property.

    For the complete list of parameters for this request, see the instances:import page.
  5. If you do not need to retain the IAM permissions you set previously, remove the permissions.

If you get an error such as ERROR_RDBMS, ensure the table exists. If the table exists, confirm that you have the correct permissions on the bucket. For help configuring access control in Cloud Storage, see Create and Manage Access Control Lists.

To see how the underlying REST API request is constructed for this task, see the APIs Explorer on the instances:import page.

Importing data into Cloud SQL using pg_restore

You can use the pg_restore utility to import a database into Cloud SQL. pg_restore only works with archive files created by pg_dump. Learn more about pg_restore.

pg_restore -h [CLOUD_SQL_INSTANCE_IP] -U [USERNAME] --format=plain --no-owner --no-acl -d [DATABASE_NAME] [SQL_FILE].sql

To import in parallel, use the -j NUM_CORES flag. NUM_CORES is the number of cores on the source instance. Use the same flag with pg_dump to export in parallel.

Troubleshooting

Click the links in the table for details:

For this problem... The issue might be... Try this...
Can't see the operation status. The user interface only shows success or failure. Use these database commands to find out more.
408 Error (Timeout) during export. SQL export can take a long time depending on database size and export content. Use multiple CSV exports to reduce the size of each operation.
CSV export worked but SQL export failed. SQL export is more likely to encounter compatibility issues with Cloud SQL. 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. Learn more.
Import is taking too long. Too many active connections can interfere with import operations. Close unused connections, or restart the Cloud SQL instance before beginning an import operation.
Create Extension error. The dump file contains references to unsupported extension. Edit the dump file to remove the references.
Error using pg_dumpall. The tool requires superuser role. The superuser role is not supported.
Export operation times out before exporting anything. Query must produce data within first seven minutes. Try a manual export using the pg_dump tool.
Import fails. Exported file may contain database users who do not yet exist. Create the database users before doing the import.
Connection closed during the export operation. Query must produce data within first seven minutes. Test the query manually. Learn more.
Unknown error during export. Possible bandwidth issue. Ensure that both the instance and the Cloud Storage bucket are in the same region.
You want to automate exports. Cloud SQL does not provide a way to automate exports. Build your own pipeline to perform this functionality. Learn more.
ERROR_RDBMS: system error occurred. Cloud Storage permissions or non-existent table. Check permissions OR ensure table exists.

Can't see the operation status

You can't see the status of an ongoing operation.

The issue might be

The Google Cloud Console reports only success or failure when done, and is not designed to return warnings.

Things to try

Connect to the database and run SHOW WARNINGS.

408 Error (Timeout) during export

You see the error message 408 Error (Timeout) while performing an export job in Cloud SQL.

The issue might be

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,

Things to try

Use the CSV format, and run multiple, smaller export jobs to reduce the size and length of each operation.

CSV export worked but SQL export failed

CSV export worked but SQL export failed.

The issue might be

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,

Things to try

Use CSV exports to export only what you need.

Export is taking too long

Export is taking too long, blocking other operations.

The issue might be

Cloud SQL does not support concurrent synchronous operations.

Things to try

Try exporting smaller datasets at a time.

Import is taking too long

Import is taking too long, blocking other operations.

The issue might be

Too many active connections can interfere with import operations. Connections consume CPU and memory, limiting the resources available.

Things to try

Close unused operations. Check CPU and memory usage to make sure there are plenty of resources available. The best way to ensure maximum resources for the import operation is to restart the instance before beginning the operation. A restart:

  • Closes all connections.
  • Ends any tasks that may be consuming resources.

Create Extension error

You see the error message SET SET SET SET SET SET CREATE EXTENSION ERROR: must be owner of extension plpgsql

The issue might be

When importing a PostgreSQL dump and you see a similar error message, the dump file contains references to plpgsql

Things to try

Edit the dump file and commenting out all lines relating to plpgsql.

Error using pg_dumpall

You get an error when trying to use the external pg_dumpall command-line tool.

The issue might be

This tool requires the superuser role.

Things to try

Cloud SQL is a managed service and does not give users the superuser roles or permissions.

Connection reset by peer

The export operation times out before anything is exported. You see the error message Could not receive data from client: Connection reset by peer.

The issue might be

If Cloud Storage does not receive any data within a certain time frame, the connection resets.

Things to try

Do a manual export using the pg_dump tool.

Import fails

Import fails when one or more users referenced in the exported SQL dump file does not exist.

The issue might be

Before importing a SQL dump, all the database users who own objects or were granted permissions on objects in the dumped database must exist. If they do not, the restore fails to recreate the objects with the original ownership and/or permissions.

Things to try

Create the database users before importing the SQL dump.

Connection closed during the export operation

Connection closed during the export operation.

The issue might be

The connection to Cloud Storage may be timing out because the query running in the export is not producing any data within the first seven minutes since the export is initiated.

Things to try

Test the query manually by connecting from any client and sending the output of your query to STDOUT with the command below:

COPY (INSERT_YOUR_QUERY_HERE) TO STDOUT WITH ( FORMAT csv, DELIMITER ',', ENCODING 'UTF8', QUOTE '"', ESCAPE '"' ).

This is expected behavior since when the export is initiated, the client is expected to start sending data right away. Keeping the connection with no data sent ends up breaking the connection and eventually resulting in the export failing and leaving the operation in an uncertain state. Also, this is what the error message from gcloud is trying to say with this message:

operation is taking longer than expected.

Unknown error during export

You see the error message Unknown error while trying to export a database to a Cloud Storage bucket.

The issue might be

The transfer might be failing due to a bandwidth issue.

Things to try

The Cloud SQL instance may be located in a different region from the Cloud Storage bucket. Reading and writing data from one continent to another involves a lot of network usage, and can cause intermittent issues like this. Check the regions of your instance and bucket.

Want to automate exports

You want to automate exports.

The issue might be

Cloud SQL does not provide a way to automate exports.

Things to try

You could build your own automated export system using Google Cloud products such as Cloud Scheduler, Pub/Sub, and Cloud Functions.

ERROR_RDBMS system error occurred

You see the error message [ERROR_RDBMS] system error occurred.

The issue might be

  • The user might not have all the Cloud Storage permissions it needs.
  • The database table might not exist.

Things to try

  1. Check that you have at least WRITER permissions on the bucket and READER permissions on the export file. For more information on configuring access control in Cloud Storage, see Create and Manage Access Control Lists.
  2. Ensure the table exists. If the table exists, confirm that you have the correct permissions on the bucket.

What's next