Access the Airflow database

Cloud Composer 3 | Cloud Composer 2 | Cloud Composer 1

This page explains how to connect to a Cloud SQL instance that runs the Airflow database of your Cloud Composer environment and run SQL queries.

For example, you might want to run queries directly on the Airflow database, make database backups, gather statistics based on the database content, or retrieve any other custom information from the database.

Before you begin

Export Airflow database contents to a Cloud SQL instance

This approach includes saving an environment snapshot, which contains an Airflow database dump, and then importing the dump to a Cloud SQL instance. In this way, you can run queries on a snapshot of the Airflow database contents.

You can use this approach in all versions of Airflow supported by Cloud Composer 3, including Airflow 3 versions later than 3.1.7 where direct access to the Airflow database is no longer possible.

Save an environment snapshot

Run the following command to save a snapshot of your environment. After you save a snapshot, the operation's result will report the URI where the snapshot is saved in the snapshotPath field. You will use this URI later.

For more information about creating snapshots, see Save and load environment snapshots.

gcloud composer environments snapshots save \
  ENVIRONMENT_NAME \
  --location LOCATION \
  --snapshot-location "SNAPSHOTS_URI"

Replace the following:

  • ENVIRONMENT_NAME: the name of your environment.
  • LOCATION: the region where the environment is located.

  • (Optional) SNAPSHOTS_URI with the URI of a bucket folder in which to store the snapshot. If you omit this argument, Cloud Composer saves the snapshot in the /snapshots folder in your environment's bucket.

Example:

gcloud composer environments snapshots save \
  example-environment \
  --location us-central1 \
  --snapshot-location "gs://example-bucket/environment_snapshots"

Example result:

Response:
'@type': type.googleapis.com/google.cloud.orchestration.airflow.service.v1.SaveSnapshotResponse
snapshotPath: gs://example-bucket/environment_snapshots/example-environment_us-central1_2026-03-17T11-26-24

Prepare the destination database

If you don't have a Cloud SQL instance, create one. This instance will store the imported database.

Run the following command to create a Cloud SQL instance:

gcloud sql instances create SQL_INSTANCE_NAME \
  --database-version=POSTGRES_15 \
  --cpu=2 \
  --memory=4GB \
  --storage-size=100GB \
  --storage-auto-increase \
  --region=LOCATION \
  --root-password=PASSWORD

Replace the following:

  • SQL_INSTANCE_NAME: name of the Cloud SQL instance.
  • LOCATION: region where the instance must be created. We recommend to use the same region as the bucket where the snapshots are saved.

  • PASSWORD: password you will use to connect to the instance.

Example:

gcloud sql instances create example-instance \
  --database-version=POSTGRES_15 \
  --cpu=2 \
  --memory=4GB \
  --storage-size=100GB \
  --storage-auto-increase \
  --region=us-central1 \
  --root-password=example_password

Run the following command to create a database named airflow_db:

gcloud sql databases create airflow_db \
  --instance=SQL_INSTANCE_NAME

Replace the following:

  • SQL_INSTANCE_NAME: name of the Cloud SQL instance.

Example:

gcloud sql databases create airflow_db \
  --instance=example-instance

Grant IAM permissions to the Cloud SQL service account

On the bucket containing the snapshot, grant a role for importing data to the Cloud SQL service account of your Cloud SQL instance. For more information about IAM roles for importing data to Cloud SQL, see Import a SQL dump file to Cloud SQL for PostgreSQL.

Run the following command to obtain the Cloud SQL service account email:

gcloud sql instances describe SQL_INSTANCE_NAME \
  --format="value(serviceAccountEmailAddress)"

Replace the following:

  • SQL_INSTANCE_NAME: name of the Cloud SQL instance.

Example:

gcloud sql instances describe example-instance --format="value(serviceAccountEmailAddress)"

Example output:

p231236835740-kw9999@gcp-sa-cloud-sql.iam.gserviceaccount.com

Grant read permissions to this service account:

gcloud storage buckets add-iam-policy-binding gs://BUCKET_NAME \
  --member=serviceAccount:SQL_SERVICE_ACCOUNT \
  --role=roles/storage.objectAdmin

Replace the following:

  • BUCKET_NAME: name of the Cloud Storage bucket. This is the part of the SNAPSHOTS_URI immediately after gs://.
  • SQL_SERVICE_ACCOUNT: email of the Cloud SQL instance's service account. You obtained it with the previous command.

Example:

gcloud storage buckets add-iam-policy-binding gs://example-bucket \
  --member=serviceAccount:p231236835740-kw9999@gcp-sa-cloud-sql.iam.gserviceaccount.com \
  --role=roles/storage.objectAdmin

Import the database dump

Run the following command to import the database dump file from the previously saved snapshot into your Cloud SQL instance's airflow_db database.

The airflow_db database will be unavailable during the import process.

gcloud sql import sql SQL_INSTANCE_NAME \
  $(gcloud storage ls SNAPSHOTS_URI/*.sql.gz) \
  --database=airflow_db \
  --user=postgres

Replace the following:

  • SQL_INSTANCE_NAME: name of the Cloud SQL instance.
  • SNAPSHOT_PATH with the URI of the specific bucket folder where the snapshot is stored. This URI is returned when you save a snapshot.

Example:

gcloud sql import sql example-instance \
  $(gcloud storage ls gs://example-bucket/environment_snapshots/example-environment_us-central1_2026-03-17T11-26-24/*.sql.gz) \
  --database=airflow_db \
  --user=postgres

(Recommended) Revoke the bucket access after the import is complete

We recommend revoking Cloud Storage bucket access permissions from the service account of your Cloud SQL instance after the import is completed.

Run the following command to do so:

gcloud storage buckets remove-iam-policy-binding gs://BUCKET_NAME \
  --member=serviceAccount:SQL_SERVICE_ACCOUNT \
  --role=roles/storage.objectAdmin

Replace the following:

  • BUCKET_NAME: name of the Cloud Storage bucket. This is the part of the SNAPSHOTS_URI immediately after gs://.
  • SQL_SERVICE_ACCOUNT: email of the Cloud SQL instance's service account. You obtained it with the previous command.

Example:

gcloud storage buckets revoke-iam-policy-binding gs://example-bucket \
  --member=serviceAccount:p231236835740-kw9999@gcp-sa-cloud-sql.iam.gserviceaccount.com \
  --role=roles/storage.objectAdmin

Run a SQL query on the imported database

After the import is completed, you can run queries on it. For example, you can run a query with Google Cloud CLI.

Run a SQL query on the Airflow database from a DAG

To connect to the Airflow database:

  1. Create a DAG with one or more SQLExecuteQueryOperator operators. To get started, you can use the example DAG.

  2. In the sql parameter of the operator, specify your SQL query.

  3. Upload this DAG to your environment.

  4. Trigger the DAG, for example, you can do it manually or wait until it runs on a schedule.

Example DAG:

import datetime
import os

import airflow
from airflow.providers.common.sql.operators.sql import SQLExecuteQueryOperator

SQL_DATABASE = os.environ["SQL_DATABASE"]

with airflow.DAG(
    "airflow_db_connection_example",
    start_date=datetime.datetime(2025, 1, 1),
    schedule=None,
    catchup=False) as dag:

    SQLExecuteQueryOperator(
        task_id="run_airflow_db_query",
        dag=dag,
        conn_id="airflow_db",
        database=SQL_DATABASE,
        sql="SELECT * FROM dag LIMIT 10;",
    )

For more information about using the SQLExecuteQueryOperator, see the How-to Guide for Postgres using SQLExecuteQueryOperator in the Airflow documentation.

Dump database contents and transfer them to a bucket

What's next