Importing Data into Cloud SQL

MySQL   |  PostgreSQL

Beta

This is a Beta release of Cloud SQL for PostgreSQL. This product might be changed in backward-incompatible ways and is not subject to any SLA or deprecation policy.

This page describes how to import data into Google Cloud SQL using a SQL dump file.

Before you begin

Before you can import data into a Cloud SQL instance, you must meet the following requirements:

• You must have a Cloud SQL instance, with the default user configured.

  For help with creating a Cloud SQL instance, see Creating Instances.

• You must be able to connect to your instance with the psql client.

  The quickest way to connect to your instance is by using the Cloud Shell. For other methods, see Connecting psql Client Using IP Addresses or Connecting psql Client Using the Cloud SQL Proxy.

• The database you are importing into must already exist on your Cloud SQL instance.

  For help with creating a database, see Creating a database.

Importing SQL dump files to a Cloud SQL instance

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

Console

1. Create your SQL dump file.
2. Create a Google Cloud Storage bucket, if you haven't already.

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

3. Upload the dump file to your bucket.

   For help with uploading files to buckets, see Uploading an Object.

4. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

   Go to the Cloud SQL Instances page

5. Select the instance to open its Instance details page.
6. Click Import in the button bar.
7. Enter the path to the bucket and SQL dump file you uploaded, or browse to it by clicking Browse and doubleclicking the name of the bucket and then selecting the file and clicking Select.

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

8. Provide the name of the database you want to import the data into.
9. Click Import to start the import.

gcloud

1. Create your SQL dump file.
2. Create a Google Cloud Storage bucket, if you haven't already.

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

3. Upload the dump file to your bucket.

   For help with uploading files to buckets, see Uploading an Object.

4. Describe the instance you are importing to:

   gcloud sql instances describe [INSTANCE_NAME]
   

5. Copy the serviceAccountEmailAddress field.
6. Add the service account to the bucket ACL as a writer:

   gsutil acl ch -u [SERVICE_ACCOUNT_ADDRESS]:W gs://[BUCKET_NAME]
   

7. Add the service account to the import file as a reader:

   gsutil acl ch -u [SERVICE_ACCOUNT_ADDRESS]:R gs://[BUCKET_NAME]/[IMPORT_FILE_NAME]
   

8. Import the database:

   gcloud sql instances import [INSTANCE_NAME] gs://[BUCKET_NAME]/[IMPORT_FILE_NAME] --database [DATABASE_NAME]
   

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

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

9. Remove the service account from the file and bucket ACLs:

   gsutil acl ch -d [SERVICE_ACCOUNT_ADDRESS] gs://[BUCKET_NAME]/[IMPORT_FILE_NAME]
   gsutil acl ch -d [SERVICE_ACCOUNT_ADDRESS] gs://[BUCKET_NAME]
   

   This step is strongly recommended for security.

cURL

1. Create your SQL dump file.
2. Create a Google Cloud Storage bucket, if you haven't already.

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

3. Upload the dump file to a Google Cloud Storage bucket.

   For help with uploading files to buckets, see Uploading an Object.

4. Provide your instance with write permissions to your bucket, and read access to the file.

   For help with setting ACLs, see Setting ACLs.

5. Import your dump file:

   ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
   curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
        --header 'Content-Type: application/json' \
        --data '{"importContext":
                   {"fileType": "SQL",
                    "uri": "gs://[BUCKET_NAME]/[PATH_TO_DUMP_FILE]",
                    "database": "[DATABASE_NAME]" }}' \
      -X POST \
      https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/import
   

6. Remove the ACLs you set in the previous step.

   This step is strongly recommended for security.

For the complete list of parameters for this request, see the instances:import page.

What's next

• Learn how to check the status of import and export operations.