To back up database schemas and data as a plain-text script of SQL statements,
you can export data from AlloyDB for PostgreSQL clusters to a SQL dump file in a
Cloud Storage bucket.

For information about migrating an entire database from a supported database
server to a new AlloyDB instance, see [Migrate a database to AlloyDB by using Database Migration Service](https://docs.cloud.google.com/database-migration/docs/postgresql-to-alloydb/quickstart). If you're exporting because you want to
create a new instance from the exported file, consider
[restoring a cluster from a stored backup](https://docs.cloud.google.com/alloydb/docs/backup/restore.md.txt).

You can cancel the export of data from AlloyDB for PostgreSQL clusters. For more
information, see
[Cancel an export operation](https://docs.cloud.google.com/alloydb/docs/cancel-export.md.txt).

## Before you begin

- Before you begin an export operation, keep in mind that export operations use database resources, but they don't interfere with standard database operations unless the instance is underprovisioned.
- Inter-region data transfer charges apply when the target bucket is in a different region than the source cluster. For more information, see [AlloyDB for PostgreSQL pricing](https://docs.cloud.google.com/alloydb/pricing).
- Compression is enabled if the object name ends with `.gz` extension. The object is then exported in `.gz` format to Cloud Storage.
- Multiple export operations can run in parallel.

## Required roles and permissions for exporting from AlloyDB

To export data from AlloyDB into Cloud Storage, the
user initiating the export must have one of the following Identity and Access Management (IAM)
roles:

- The [AlloyDB Admin](https://docs.cloud.google.com/alloydb/docs/reference/iam-roles-permissions.md.txt) (`roles/alloydb.admin`) role
- A [custom role](https://docs.cloud.google.com/iam/docs/understanding-custom-roles), including the following permissions:
  - `alloydb.clusters.get`
  - `alloydb.clusters.export`

Additionally, the service account for the AlloyDB cluster must
have one of the following roles on the Cloud Storage bucket:

- The `storage.objectAdmin` or `storage.objectCreator` IAM role
- A custom role, including the `storage.objects.create` permission

For help with IAM roles, see
[Identity and Access Management](https://docs.cloud.google.com/storage/docs/access-control/iam.md.txt).

## Export AlloyDB data to a SQL dump file

When you use AlloyDB to perform an export, whether from the
[gcloud CLI](https://docs.cloud.google.com/sdk/gcloud.md.txt) or the API, you are using the
[`pg_dump`](https://www.postgresql.org/docs/current/static/app-pgdump.html)
utility, with the options required to ensure that the resulting export file is
valid for import back into AlloyDB.

> [!NOTE]
> **Note:** If your data contains large objects or blobs, the export can consume a large amount of memory, which can impact instance performance.

To export data from a database on a AlloyDB cluster to a SQL dump
file in a Cloud Storage bucket, follow these steps:

### gcloud

1. Ensure there's an existing Cloud Storage bucket to store the exported file, or [create a new bucket](https://docs.cloud.google.com/storage/docs/creating-buckets.md.txt).
2. Use
   [`gcloud storage buckets add-iam-policy-binding`](https://docs.cloud.google.com/sdk/gcloud/reference/storage/buckets/add-iam-policy-binding)
   to grant the `storage.objectAdmin`
   [IAM role](https://docs.cloud.google.com/storage/docs/access-control/iam-roles.md.txt)
   to the AlloyDB service account for the bucket. The format
   for the service account is as follows:

   ```bash
   service-PROJECT_NUMBER@gcp-sa-alloydb.iam.gserviceaccount.com
   ```

   For instructions on granting roles on buckets,
   see
   [Set and manage IAM policies on buckets](https://docs.cloud.google.com/storage/docs/access-control/using-iam-permissions.md.txt).

   If you're exporting the file into an existing Cloud Storage
   bucket that you didn't create, you might need additional
   IAM permissions in order to grant roles on the bucket. For
   details, see
   [Required roles](https://docs.cloud.google.com/storage/docs/access-control/using-iam-permissions.md.txt#required-roles-managed-buckets).

   > [!NOTE]
   > **Note:** If the Cloud Storage bucket already contains an object with the same name as the file being exported, granting a service account with the `storage.objectAdmin` role overwrites the existing object during the export operation. To avoid overwriting existing objects, grant a less privileged role such as `storage.objectCreator` to the service account.

3. Export the database to your Cloud Storage bucket. The following
   lists options for exporting data in a SQL dump format:

   - `--async` (Optional): return immediately, without waiting for the operation in progress to complete.
   - `--tables` (Optional): tables to export from.
   - `--schema-only` (Optional): if set, export only the schema.
   - `--clean-target-objects` (Optional): if set, output commands to `DROP` all the dumped database objects prior to outputting the commands for creating them.
   - `--if-exist-target-objects` (Optional): if set, use `DROP ... IF EXISTS` commands to check for the object's existence before dropping it in `--clean-target-objects` mode.

   To use these features, include these options in the `gcloud` command.
   If you want to export only
   object definitions (schema) and no data, use the `--schema-only` flag. To
   specify which tables are to be exported, use the `--tables=TABLE_NAMES`
   flag. You can specify comma-separated values of table names or wildcard
   patterns to specify multiple tables.

   Otherwise, remove these parameters from the following command:

   ```bash
   gcloud alloydb clusters export CLUSTER_NAME \
     --region=REGION \
     --database=DATABASE_NAME \
     --gcs-uri="gs://BUCKET_NAME/OBJECT_NAME" \
     --tables=TABLE_NAMES \
     --schema-only \
     --clean-target-objects \
     --if-exist-target-objects \
     --sql
   ```

   The `gcloud alloydb clusters export` command doesn't contain triggers or
   stored procedures, but it does contain views. To export triggers or
   stored procedures, use the
   [`pg_dump`](https://www.postgresql.org/docs/current/static/app-pgdump.html)
   utility.

   For more information about using the `gcloud alloydb clusters export` command,
   see the
   [`gcloud alloydb clusters export`](https://docs.cloud.google.com/sdk/gcloud/reference/alloydb/clusters/export.md.txt)
   command reference page.
4. If you don't need to retain the IAM role that you set
   previously,
   [revoke](https://docs.cloud.google.com/iam/docs/granting-changing-revoking-access.md.txt#revoking-console)
   the role.

### REST v1

1. Ensure there's an existing Cloud Storage bucket to store the
   exported file, or create a new bucket:

   ```bash
   gcloud storage buckets create gs://BUCKET_NAME --project=PROJECT_NAME --location=LOCATION_NAME
   ```
2. Use
   [`gcloud storage buckets add-iam-policy-binding`](https://docs.cloud.google.com/sdk/gcloud/reference/storage/buckets/add-iam-policy-binding)
   to grant the `storage.objectAdmin`
   [IAM role](https://docs.cloud.google.com/storage/docs/access-control/iam-roles.md.txt)
   to the AlloyDB service account for the bucket. The format
   for the service account is as follows:

   ```bash
    service-PROJECT_NUMBER@gcp-sa-alloydb.iam.gserviceaccount.com
   ```

   For instructions on granting roles on buckets,
   see
   [Set and manage IAM policies on buckets](https://docs.cloud.google.com/storage/docs/access-control/using-iam-permissions.md.txt).

   If you're exporting the file into an existing Cloud Storage
   bucket that you didn't create, you might need additional
   IAM permissions in order to grant roles on the bucket. For
   details, see
   [Required roles](https://docs.cloud.google.com/storage/docs/access-control/using-iam-permissions.md.txt#required-roles-managed-buckets).

   > [!NOTE]
   > **Note:** If the Cloud Storage bucket already contains an object with the same name as the file being exported, granting a service account with the `storage.objectAdmin` role overwrites the existing object during the export operation. To avoid overwriting existing objects, grant a less privileged role such as `storage.objectCreator` to the service account.

3. Export your database.  

   Use the following HTTP method and URL:

   ```bash
   POST https://alloydb.googleapis.com/v1/projects/PROJECT_ID/locations/REGION/clusters/CLUSTER_ID:export
   ```

   Before you use any of the request data, make the following replacements:
   - <var translate="no">PROJECT_ID</var>: the project ID.
   - <var translate="no">REGION</var>: the region in which the AlloyDB cluster is deployed.
   - <var translate="no">CLUSTER_ID</var>: the cluster ID.
   - <var translate="no">BUCKET_NAME</var>: the Cloud Storage bucket name.
   - <var translate="no">PATH_TO_SQL_FILE</var>: the path to the SQL dump file.
   - <var translate="no">DATABASE_NAME</var>: the name of a database inside the AlloyDB instance.
   - <var translate="no">TABLES</var>: tables to export from.
   - <var translate="no">SCHEMA_ONLY</var>: if `true`, export only the schema.
   - <var translate="no">CLEAN_TARGET_OBJECTS</var>: if `true`, output commands to `DROP` all the dumped database objects prior to outputting the commands for creating them.
   - <var translate="no">IF_EXIST_TARGET_OBJECTS</var>: If `true`, use `DROP ... IF EXISTS` commands to check for the object's existence before dropping it in `clean_target_objects` mode.

   To use these features, set the values of these parameters to `true`.
   Otherwise, set their values to `false`. If you want to export only
   object definitions (schema) and no data, use the `schema_only` flag. To
   specify which tables are to be exported, use the `tables` field.
   You can select multiple tables by providing a comma-separated list of
   table names or by writing wildcard characters in the pattern.

   Request JSON body:

   ```json
   {
     "gcs_destination": {
       "uri": "gs://BUCKET_NAME/PATH_TO_SQL_FILE"
     },
     "database": "DATABASE_NAME",
     "sql_export_options": {
       "schema_only": true,
       "tables": [
        "TABLE1",
        "TABLE2"
       ],
       "clean_target_objects": false,
       "if_exist_target_objects": true
     }
   }
   ```

   To send your request, expand one of these options:

   #### curl (Linux, macOS, or Cloud Shell)

   The following command assumes that you are signed into the Google Cloud CLI with your user account by running [`gcloud init`](https://docs.cloud.google.com/sdk/gcloud/reference/init) or [`gcloud auth login`](https://docs.cloud.google.com/sdk/gcloud/reference/auth/login.md.txt), or by using [Cloud Shell](https://docs.cloud.google.com/shell/docs.md.txt) which automatically signs you into the gcloud CLI. You can check the active account by running [`gcloud auth list`](https://docs.cloud.google.com/sdk/gcloud/reference/auth/list.md.txt).

   Save the request body in a file named `request.json`
   and execute the following command:

   ```
      curl -X POST \
            -H "Authorization: Bearer $(gcloud auth print-access-token)" \
            -H "Content-Type: application/json; charset=utf-8" \
            -d @request.json \
            "https://alloydb.googleapis.com/v1/projects/PROJECT_ID/locations/REGION/clusters/CLUSTER_ID:export"
      
   ```

   #### PowerShell (Windows)

   The following command assumes that you are signed into the Google Cloud CLI with your user account by running [`gcloud init`](https://docs.cloud.google.com/sdk/gcloud/reference/init) or [`gcloud auth login`](https://docs.cloud.google.com/sdk/gcloud/reference/auth/login.md.txt). You can check the active account by running [`gcloud auth list`](https://docs.cloud.google.com/sdk/gcloud/reference/auth/list.md.txt).

   Save the request body in a file named `request.json`
   and execute the following command:

   ```
      $cred = gcloud auth print-access-token
      $headers = @{ "Authorization" = "Bearer $cred" }

      Invoke-WebRequest `
        -Method POST `
        -Headers $headers `
        -ContentType: "application/json; charset=utf-8" `
        -InFile request.json `
      -Uri "https://alloydb.googleapis.com/v1/projects/PROJECT_ID/locations/REGION/clusters/CLUSTER_ID:export"| Select-Object -Expand Content
      
   ```

   You receive a JSON response similar to the following:

   #### Response

   ```
   {
     "name": "projects/PROJECT_ID/locations/REGION/operations/OPERATION_ID",
     "metadata": {
       "@type": "type.googleapis.com/google.cloud.alloydb.v1.OperationMetadata",
       "createTime": "2024-09-17T06:05:31.244428646Z",
       "target": "projects/PROJECT_ID/locations/REGION/clusters/CLUSTER_ID",
       "verb": "export",
       "requestedCancellation": false,
       "apiVersion": "v1"
     },
     "done": false
   }
   ```
4. If you don't need to retain the IAM role you set previously,
   remove it.

For the complete list of parameters for the request, see
[`clusters.export`](https://docs.cloud.google.com/alloydb/docs/reference/rest/v1/projects.locations.clusters/export.md.txt).

## What's next

- Learn how to [export a CSV file](https://docs.cloud.google.com/alloydb/docs/export-csv-file.md.txt).
- [Cancel a data export](https://docs.cloud.google.com/alloydb/docs/cancel-export.md.txt).