Export PostgreSQL database from GCP Cloud SQL

Published: | 1 min read

As part of data migration I needed to export a PostgreSQL database from Google Cloud Platform Cloud SQL instance. This was quite easy to do with the following steps, and of course I run the export in a serverless way. Serverless exports cost money, so make sure to review the pricing. I run all the commands on Cloud Shell, so no local installs needed.

  1. Make sure you set the correct project my-project before running any other commands:

    gcloud config set project my-project

  2. Create a Cloud Storage bucket for the export data, let’s call it postgres-export-bucket:

    gcloud storage buckets create gs://postgres-export-bucket \
--default-storage-class=STANDARD \
--location=EUROPE-NORTH1 \
--uniform-bucket-level-access

  3. Get the Cloud SQL instance my-psql-instance service account:

    gcloud sql instances describe my-psql-instance

    From the response, look for the service account, which looks something like this: some-random-string@gcp-sa-cloud-sql.iam.gserviceaccount.com.

  4. Give the service account IAM role which allows it to write to the bucket:

    # All one line
gsutil iam ch serviceAccount:some-random-string@gcp-sa-cloud-sql.iam.gserviceaccount.com:roles/storage.objectAdmin gs://postgres-export-bucket

  5. Finally, run the export job for the database my-db:

    gcloud sql export sql my-psql-instance \
gs://postgres-export-bucket/sqldumpfile.gz \
--database=my-db \
--offload

  6. Download the database dump from the bucket, and do what you need with it.

Further reading