Airflow administrationWork with the Airflow database

Use a MySQL or PostgreSQL database for metadata or storage

You can create Astro Private Cloud Deployments with the Houston API that use pre-created databases, external to the Airflow Deployment, as both a metadata storage and result storage backend.

Prerequisites

  • Workspace Admin user privileges and a Workspace ID
  • (Optional) A MySQL or PostgreSQL database
  • (Optional) An existing Deployment
If you create a new connection to an external database from a Deployment with existing Dag data, you must migrate that historic data to the new database. Information about your historic Deployment activity, such as task instances and Dag runs, won’t be displayed as the database where you stored that information has changed.

Step 1: Enable manual connection strings

In Astro Private Cloud 1.x, manualConnectionStrings.enabled is a deployments.* setting. Cluster configuration is the final layer for deployments.* keys, so the cluster value wins when both values.yaml and a cluster override are set. See Configure Astro Private Cloud for the precedence rules.

Choose one of the following options based on the scope you need:

  • To enable manual connection strings for one data plane cluster, update the cluster’s Configuration Override.
  • To enable manual connection strings as the platform default for clusters that don’t have their own saved value for this key, update values.yaml and run a Helm upgrade.

  1. In the Astro UI, open Clusters, select your data plane cluster, then click Edit on Configuration Override. To automate this with the Houston API, use the updateCluster mutation with deploymentsConfigOverride. See Update data plane cluster configurations.

  2. Add the following to Configuration Override:

    1{
    2  "manualConnectionStrings": {
    3    "enabled": true
    4  }
    5}

  3. Click Update cluster to apply the change. The override is deep-merged with the cluster’s existing configuration.

Option B: Update values.yaml (platform default)

Use this option only when no cluster has saved an override for deployments.manualConnectionStrings.enabled. If a cluster already has a saved value for this key, the cluster value wins and you must update the cluster configuration as in Option A.

  1. Open your values.yaml file.

  2. Add the following under astronomer.houston.config:

    1astronomer:
    2  houston:
    3    config:
    4      deployments:
    5        manualConnectionStrings:
    6          enabled: true

  3. Push the configuration change. See Apply a config change. If the astronomer-houston pods don’t roll automatically after the Helm upgrade, restart them manually so they pick up the new configuration.

Already-installed clusters

The values.yaml settings in this step only take effect during the initial cluster installation. For clusters that are already registered, Houston resolves Deployment configuration directly from the cluster’s database record (Cluster.config.deployments) and ignores the Helm-derived ConfigMap. To enable manual connection strings on an existing cluster, apply the change through System Admin → Clusters → Edit → Cluster Deployment Configuration in Astronomer instead.

Step 2: (Optional) Create your database

Substitute astro-db-name with your own database name, if you need to create a new database.

1CREATE DATABASE astro-db-name;

Step 3: Add a user account to your database for the connection

Substitute astro-user-name and astro-user-password with your information. You can use an existing database for this step.

PostgreSQL usernames must be lowercase.
  1. Create a user with a password for Astro Private Cloud to use to access the database.
1CREATE USER astro-user-name WITH PASSWORD 'astro-user-password';
  1. Grant all privileges on the database to the user.
1GRANT ALL PRIVILEGES ON DATABASE postgreSQL_linked_DB TO astro-user-name;
  1. Grant USAGE and CREATE privileges on the public schema to astro-user-name:
1GRANT USAGE, CREATE ON SCHEMA public TO astro-user-name;

Now, go into the database you created, which is astro-db-name in this example, and run the following queries

  1. Grant all privileges on all tables, sequences, and functions to the user.
1GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO astro-user-name;
2GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO astro-user-name;
3GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO astro-user-name;
  1. Set default privileges for the user, so any new tables, sequences, or functions automatically have the user’s access.
1ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO astro-user-name;
2ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO astro-user-name;
3ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO astro-user-name;
4GRANT USAGE, CREATE ON SCHEMA public TO astro-user-name;

Step 4: Retrieve database host information

Retrieve the connection information for your external database. For example, with AWS, you can retrieve your endpoint information by Finding the connection information for an RDS for MySQL DB instance.

Step 5: Compose a connection string for your database

You need connection strings that define how Astro Private Cloud configures the connection to your external databases from your Airflow Deployment. The values of these strings are used when you define your metadataConnection or resultBackendConnection when you create, update, or upsert your Deployment.

Use the values for your astro-user-name, astro-user-password, astro-db-name, and the host information you retrieved to compose the connection strings in the following format, depending on whether you want to define a result backend connection or a metadata database connection.

PgBouncer is enabled by default

For PostgreSQL Deployments, PgBouncer is enabled by default in Astro Private Cloud. When PgBouncer is enabled, URI-style connection strings (postgresql://...) are rejected during upsert and you must use the JSON format (metadataConnectionJson and resultBackendConnectionJson). Use the URI tabs below only if you have explicitly disabled PgBouncer for your Deployment.

With PGBouncer disabled

  • metadataConnection:

    postgresql://astro-user-name:astro-user-password@host:5432/astro-db-name

  • resultBackendConnection:

    db+postgresql://astro-user-name:astro-user-password@host:5432/astro-db-name
Celery Executor

The connection string format validation regex don’t cover the resultbackend connection string format, which includes db+. This is specifically required for the Celery executor worker. If the connection string doesn’t include db+, then Celery worker pod fails. The regex validation is not implemented because it adds the complications on format validation logic in different scenarios.

With PGBouncer enabled

If you have PGBouncer enabled, and are using Postgres, you must configure metadataConnectionJson and resultBackendConnectionJson instead. PgBouncer is enabled by default in Astro Private Cloud, so this is the typical path.

Use the values for your astro-user-name, astro-user-password, astro-db-name, and the host information you retrieved to compose the connection strings in the following format, depending on whether you want to define a result backend connection or a metadata database connection.

  • metadataConnectionJson:

    1"metadataConnectionJson": {
    2    "user": "astro-user-name",
    3    "pass": "astro-user-password",
    4    "protocol": "postgresql",
    5    "host": "host",
    6    "port": 5432,
    7    "db": "astro-db-name"
    8    },

  • resultBackendConnectionJson:

    1"resultBackendConnectionJson": {
    2    "user": "astro-user-name",
    3    "pass": "astro-user-password",
    4    "protocol": "postgresql",
    5    "host": "host",
    6    "port": 5432,
    7    "db": "astro-db-name"
    8    },

Step 6: Add to Deployment configuration

Use the Houston API to create your Deployment configuration.

Required: skipAirflowDatabaseProvisioning

When you point a Deployment at an external database, you must set skipAirflowDatabaseProvisioning: true in the upsertDeployment mutation. Without this flag, Commander overwrites the host value in your metadataConnection or metadataConnectionJson with the data plane database URL before running helm install, regardless of the host you submitted. The Deployment then uses the platform database instead of your external database without reporting an error. Setting skipAirflowDatabaseProvisioning: true skips automatic database provisioning and preserves the host you provided.

The following example shows the mutation and queries for using upsertDeployment. See Houston API code examples for examples on how to use the update and upsert options for configuring your Deployment.

Create a new Deployment

1mutation upsertDeployment(
2  $workspaceUuid: Uuid!
3  $releaseName: String
4  $namespace: String!
5  $label: String!
6  $description: String
7  $version: String
8  $airflowVersion: String
9  $runtimeVersion: String
10  $executor: ExecutorType
11  $workers: Workers
12  $webserver: Webserver
13  $scheduler: Scheduler
14  $triggerer: Triggerer
15  $properties: JSON
16  $dagDeployment: DagDeployment
17  $rollbackEnabled: Boolean
18  $metadataConnection: String
19  $resultBackendConnection: String
20  $metadataConnectionJson: JSON
21  $resultBackendConnectionJson: JSON
22  $skipAirflowDatabaseProvisioning: Boolean
23) {
24  upsertDeployment(
25    workspaceUuid: $workspaceUuid
26    releaseName: $releaseName
27    namespace: $namespace
28    label: $label
29    airflowVersion: $airflowVersion
30    description: $description
31    version: $version
32    executor: $executor
33    workers: $workers
34    webserver: $webserver
35    scheduler: $scheduler
36    triggerer: $triggerer
37    properties: $properties
38    runtimeVersion: $runtimeVersion
39    dagDeployment: $dagDeployment
40    rollbackEnabled: $rollbackEnabled
41    metadataConnection: $metadataConnection
42    resultBackendConnection: $resultBackendConnection
43    metadataConnectionJson: $metadataConnectionJson
44    resultBackendConnectionJson: $resultBackendConnectionJson
45    skipAirflowDatabaseProvisioning: $skipAirflowDatabaseProvisioning
46  ) {
47    id
48    config
49    urls {
50      type
51      url
52      __typename
53    }
54    properties
55    description
56    label
57    releaseName
58    namespace
59    status
60    type
61    version
62    workspace {
63      id
64      label
65      __typename
66    }
67    airflowVersion
68    runtimeVersion
69    dagDeployment {
70      type
71      nfsLocation
72      repositoryUrl
73      branchName
74      syncInterval
75      syncTimeout
76      ephemeralStorage
77      dagDirectoryLocation
78      rev
79      sshKey
80      knownHosts
81      __typename
82    }
83    createdAt
84    updatedAt
85    __typename
86  }
87}

JSON Query example

1{
2  "workspaceUuid": "cm3g0cjd2000008l74jigb54y",
3  "skipAirflowDatabaseProvisioning": true,
4 "metadataConnectionJson": {
5    "user": "astro-user-name",
6    "pass": "astro-password",
7    "protocol": "postgresql",
8    "host": "host",
9    "port": 5432,
10    "db": "astro-db-name"
11    },
12  "resultBackendConnectionJson": {
13    "user": "astro-user-name",
14    "pass": "astro-password",
15    "protocol": "postgresql",
16    "host": "postgres-db-lb.external-postgres.svc.cluster.local",
17    "port": 5432,
18    "db": "astro-db-name"
19    },
20  "namespace": "",
21  "executor": "CeleryExecutor",
22  "workers": {},
23  "webserver": {},
24  "scheduler": {
25    "replicas": 1
26  },
27  "triggerer": {},
28  "label": "Rt1160-Celery-Pgbouncer-Enabled-Json-5",
29  "description": "",
30  "runtimeVersion": "11.6.0",
31  "properties": {
32    "extra_capacity": {
33      "cpu": 1000,
34      "memory": 3840
35    }
36  },
37  "rollbackEnabled": true,
38  "dagDeployment": {
39    "type": "dag_deploy",
40    "nfsLocation": "",
41    "repositoryUrl": "",
42    "branchName": "",
43    "syncInterval": 1,
44    "syncTimeout": 120,
45    "ephemeralStorage": 2,
46    "dagDirectoryLocation": "",
47    "rev": "",
48    "sshKey": "",
49    "knownHosts": ""
50  }
51}

Example query string variables

1{
2  "workspaceUuid": "cm3g0cjd2000008l74jigb54y",
3  "skipAirflowDatabaseProvisioning": true,
4"metadataConnection": "postgresql://astro-user-name:astro-user-password@host:5432/astro-db-name"
5"resultBackendConnection": "db+postgresql://astro-user-name:astro-user-password@host:5432/astro-db-name"
6  "namespace": "",
7  "executor": "CeleryExecutor",
8  "workers": {},
9  "webserver": {},
10  "scheduler": {
11    "replicas": 1
12  },
13  "triggerer": {},
14  "label": "Rt1160-Celery-Pgbouncer-Enabled-Json-5",
15  "description": "",
16  "runtimeVersion": "11.6.0",
17  "properties": {
18    "extra_capacity": {
19      "cpu": 1000,
20      "memory": 3840
21    }
22  },
23  "rollbackEnabled": true,
24  "dagDeployment": {
25    "type": "dag_deploy",
26    "nfsLocation": "",
27    "repositoryUrl": "",
28    "branchName": "",
29    "syncInterval": 1,
30    "syncTimeout": 120,
31    "ephemeralStorage": 2,
32    "dagDirectoryLocation": "",
33    "rev": "",
34    "sshKey": "",
35    "knownHosts": ""
36  }
37}