> ## Documentation Index
> Fetch the complete documentation index at: https://astronomer.io/docs/llms.txt
> Use this file to discover all available pages before exploring further.
# Access Airflow database
Each Airflow Deployment in Astro Private Cloud (APC) has its own metadata database. APC supports both PostgreSQL and MySQL as the database backend. You can connect to the database to run queries, troubleshoot issues, or perform maintenance tasks.
## Database architecture
Each Deployment has a single PostgreSQL database containing two schemas:
| Schema | Purpose |
| --------- | ---------------------------------------------------------------------------- |
| `airflow` | Airflow metadata, including Dags, task instances, connections, and variables |
| `celery` | Celery task results when using the Celery Executor |
Each Deployment has a single MySQL database. All Airflow metadata tables and Celery result tables exist in the same database without schema separation.
## Retrieve the connection string
Each Deployment stores its database connection string in a Kubernetes secret named `-metadata`. Run the following command to retrieve it:
```bash wrap theme={null}
kubectl get secret -n \
-metadata -o jsonpath='{.data.connection}' | base64 -d
```
This returns a connection URI in the following format:
```text wrap theme={null}
postgresql://:@:/?sslmode=prefer
```
```text wrap theme={null}
mysql://:@:/
```
## Connect to the database
### Use kubectl exec
Run the following command to open an Airflow metadata database shell from the scheduler pod:
```bash wrap theme={null}
kubectl exec -it -n \
deployment/-scheduler -c scheduler -- \
airflow db shell
```
This command uses the Airflow metadata database connection that is already configured in the Deployment.
### Connect from your local computer
If the database is accessible as a Kubernetes service, you can use `kubectl port-forward` to connect from your local computer. Identify the database service and namespace from the `` field in the connection string, then forward the port:
```bash wrap theme={null}
kubectl port-forward -n \
svc/ :
```
If PgBouncer is enabled, forward port 6543 from the PgBouncer service in the
Deployment namespace instead.
Then connect using the credentials from the connection string:
```bash wrap theme={null}
psql -h localhost -p -U -d
```
```bash wrap theme={null}
kubectl port-forward -n \
svc/ :
```
Then connect using the credentials from the connection string:
```bash wrap theme={null}
mysql -h 127.0.0.1 -P -u -p
```
If the database is an external service (for example, Amazon RDS or Google Cloud SQL), connect directly using the host and port from the connection string instead of port-forwarding.
## Common queries
After you connect to the database, use the following queries to inspect Airflow metadata.
### Dag information
```sql wrap theme={null}
SELECT dag_id, is_active, is_paused, last_parsed_time
FROM dag ORDER BY dag_id;
SELECT dag_id, run_id, state, start_date
FROM dag_run ORDER BY start_date DESC LIMIT 20;
```
```sql wrap theme={null}
SELECT dag_id, is_paused, last_parsed_time
FROM dag ORDER BY dag_id;
SELECT dag_id, run_id, state, start_date
FROM dag_run ORDER BY start_date DESC LIMIT 20;
```
### Task instance status
```sql wrap theme={null}
SELECT dag_id, task_id, state, start_date, try_number
FROM task_instance
ORDER BY start_date DESC LIMIT 50;
```
### Task failures
```sql wrap theme={null}
SELECT dag_id, task_id, state, start_date
FROM task_instance
WHERE state = 'failed'
AND start_date > NOW() - INTERVAL '24 hours';
```
```sql wrap theme={null}
SELECT dag_id, task_id, state, start_date
FROM task_instance
WHERE state = 'failed'
AND start_date > NOW() - INTERVAL 24 HOUR;
```
## External database configuration
To use an external database instead of the APC-managed database, create the Deployment using the `upsertDeployment` [APC API](/docs/astro-private-cloud/v-2-x/houston-api) mutation with the following fields:
* `skipAirflowDatabaseProvisioning`: Set to `true` so the deployment orchestrator doesn't provision a database for this Deployment.
* `metadataConnection` or `metadataConnectionJson`: The connection string or JSON object pointing to your external database.
* `resultBackendConnection` or `resultBackendConnectionJson`: The connection string or JSON object for the Celery result backend.
For a complete example of the `upsertDeployment` mutation payload with external database configuration, see [Bring your own Airflow database](/docs/astro-private-cloud/v-2-x/multi-db).
## Back up and restore
For production environments, run backup and restore commands from your local
computer or a dedicated admin computer. Don't run backup or restore commands
from inside Airflow containers.
Before you run a backup, estimate the database size and confirm that your local computer has enough free disk space.
```sql wrap theme={null}
SELECT
schemaname,
pg_size_pretty(SUM(pg_total_relation_size(format('%I.%I', schemaname, tablename)::regclass))) AS total_size
FROM pg_tables
GROUP BY schemaname
ORDER BY SUM(pg_total_relation_size(format('%I.%I', schemaname, tablename)::regclass)) DESC;
```
```sql wrap theme={null}
SELECT
table_schema,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;
```
### Back up the database
```bash wrap theme={null}
pg_dump -h -p -U -d -n airflow > backup.sql
```
To back up a MySQL database from your local computer, use port-forwarding or a direct connection to the database host:
```bash wrap theme={null}
mysqldump -h -P -u -p > backup.sql
```
### Restore the database
```bash wrap theme={null}
psql -h -p -U -d < backup.sql
```
To restore a MySQL database from your local computer, use port-forwarding or a direct connection to the database host:
```bash wrap theme={null}
mysql -h -P -u -p < backup.sql
```
Replace the placeholders with values from the connection string. See [Retrieve the connection string](#retrieve-the-connection-string).
## Security best practices
Direct database access bypasses Airflow's security model. Use with caution and
only for troubleshooting or maintenance tasks.
* Use read-only access for monitoring.
* Never expose database ports publicly.
* Use SSL for all connections.
* Rotate credentials regularly.