Exporting Data

This page describes how to export data from Cloud SQL instances, or from a database server not managed by Cloud SQL.

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

Exporting data to a SQL dump file

When you use Cloud SQL to perform an export, whether from the GCP Console, the gcloud command-line tool, or the API, you are using the pg_dump utility, with the options required to ensure that the resulting export file is valid for import back into Cloud SQL.

You can also run pg_dump manually, if you are exporting a database not managed by Cloud SQL. However, if you plan to import your data into Cloud SQL, you must follow the instructions provided in Exporting data from an external database server.

Exporting data using Cloud SQL

Console

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

    Go to the Cloud SQL Instances page

  2. Click the instance you want to export data from to open its Instance details page.
  3. Click Export in the button bar.
  4. Under Cloud Storage file, select a Cloud Storage bucket or folder for your export.
  5. In the Name field, provide a name for your export file and click Select.
  6. Enter the name of the database you want to export.
  7. Click Export to start the export.

gcloud

To export a dump file from an instance to a Cloud Storage bucket:

  1. Create a bucket for the export:
    gsutil mb -p [PROJECT_NAME] -l [LOCATION_NAME] gs://[BUCKET_NAME]
    

    This step is not required, but strongly recommended, so you do not open up access to any other data.

  2. Describe the instance you are exporting from:
    gcloud sql instances describe [INSTANCE_NAME]
    
  3. Copy the serviceAccountEmailAddress field.
  4. Add the service account to the bucket ACL as a writer:
    gsutil acl ch -u [SERVICE_ACCOUNT_ADDRESS]:W gs://[BUCKET_NAME]
    
  5. Export the database:
    gcloud sql export sql [INSTANCE_NAME] gs://[BUCKET_NAME]/sqldumpfile.gz \
                                --database=[DATABASE_NAME]
    

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

  6. If you do not need to retain the permissions provided by the ACL you set previously, remove the ACL:
    gsutil acl ch -d [SERVICE_ACCOUNT_ADDRESS] gs://[BUCKET_NAME]
    

cURL

  1. Create a bucket for the export:
    gsutil mb -p [PROJECT_NAME] -l [LOCATION_NAME] gs://[BUCKET_NAME]
    

    This step is not required, but strongly recommended, so you do not open up access to any other data.

  2. Provide your instance with write permissions to your bucket.

    For help with setting ACLs, see Setting ACLs.

  3. Export your database:
    ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
    curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         --header 'Content-Type: application/json' \
         --data '{"exportContext":
                    {"fileType": "SQL",
                     "uri": "gs://<BUCKET_NAME>/<PATH_TO_DUMP_FILE>",
                     "databases": ["<DATABASE_NAME1>", "<DATABASE_NAME2>"] }}' \
       -X POST \
       https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/export
    
  4. If you do not need to retain the permissions provided by the ACL you set previously, remove the ACL.
For the complete list of parameters for this request, see the instances:export page.

Exporting data from an externally-managed database server

To export a database that is not managed by Cloud SQL, you use the pg_dump utility with the following flags:

  • --no-owner

    Ownership change commands must not be included in the SQL dump file.

  • --format=plain

    Only plain SQL format is supported by Cloud SQL.

  • --no-acl

    This flag is required if your dump would otherwise contain statements to grant or revoke membership in a SUPERUSER role.

In addition, you must remove all extension-related statements for extensions not supported by Cloud SQL. See PostgreSQL Extensions for the list of supported extensions.

From a command line, run pg_dump:

pg_dump -U [USERNAME] --format=plain --no-owner --no-acl [DATABASE_NAME] \
    | sed -E 's/(DROP|CREATE|COMMENT ON) EXTENSION/-- \1 EXTENSION/g' > [SQL_FILE].sql

The sed post-processing comments out all extension statements in the SQL dump file.

What's next

Was this page helpful? Let us know how we did:

Send feedback about...

Cloud SQL for PostgreSQL