### Start Specific Stacks

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/08-docker-commands-reference.md

Launch only the required stack components.

```bash
# Airflow only
docker-compose --env-file ./.env -f ./airflow-docker-compose.yaml up -d

# Data warehouse and Jupyter only
docker-compose --env-file ./.env -f ./postgres-docker-compose.yaml up -d
```

--------------------------------

### List DAGs Example

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/06-airflow-api-reference.md

Example curl command to list DAGs with sorting and pagination parameters.

```bash
curl -X GET \
  "http://localhost:8080/api/v1/dags?order_by=dag_id&limit=10" \
  --user airflow:airflow
```

--------------------------------

### Verify Docker Installation

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/08-docker-commands-reference.md

Check that Docker and Docker Compose are installed and the daemon is responsive.

```bash
docker --version
docker-compose --version
docker ps  # Should run without errors
```

--------------------------------

### Get DAG Details Example

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/06-airflow-api-reference.md

Example curl command to fetch details for a specific DAG.

```bash
curl -X GET \
  "http://localhost:8080/api/v1/dags/my_etl_dag" \
  --user airflow:airflow
```

--------------------------------

### Start Specific Services

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/08-docker-commands-reference.md

Target individual services within a stack for startup.

```bash
# Start only webserver (and dependencies)
docker-compose --env-file ./.env -f ./airflow-docker-compose.yaml up -d webserver

# Start only Jupyter (and dependencies)
docker-compose --env-file ./.env -f ./postgres-docker-compose.yaml up -d jupyter_notebook
```

--------------------------------

### Start and Monitor Airflow Stack

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/04-setup-and-operations.md

Initialize the Airflow services and monitor the startup logs.

```bash
docker-compose --env-file ./.env -f ./airflow-docker-compose.yaml up -d
```

```bash
docker-compose -f ./airflow-docker-compose.yaml logs -f airflow-init
```

```bash
docker-compose -f ./airflow-docker-compose.yaml logs -f scheduler webserver
```

--------------------------------

### Start and Monitor Data Stack

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/04-setup-and-operations.md

Initialize the PostgreSQL and Jupyter services and monitor the startup logs.

```bash
docker-compose --env-file ./.env -f ./postgres-docker-compose.yaml up -d
```

```bash
docker-compose -f ./postgres-docker-compose.yaml logs -f database jupyter_notebook
```

--------------------------------

### Initialize Airflow services

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/README.md

Commands to build the Airflow image and start the Airflow containers.

```bash
$ docker build -f airflow-dockerfile .
```

```bash
$ docker-compose -f airflow-docker-compose.yaml up -d
```

--------------------------------

### Install Python Packages

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/04-setup-and-operations.md

Add dependencies to requirements.txt and rebuild the Airflow container.

```bash
echo "requests==2.28.1" >> requirements.txt
docker build -f airflow-dockerfile -t airflow-custom:2.3.0 .
docker-compose -f airflow-docker-compose.yaml down
docker-compose --env-file ./.env -f ./airflow-docker-compose.yaml up -d
```

--------------------------------

### GET /connections

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/06-airflow-api-reference.md

Lists all configured connections.

```APIDOC
## GET /connections

### Description
Retrieves a list of all connections configured in the system.

### Method
GET

### Endpoint
/connections

### Parameters
#### Query Parameters
- **limit** (integer) - Optional - Number of results (default: 100)
- **offset** (integer) - Optional - Pagination offset

### Response
#### Success Response (200)
- **connections** (array) - List of connection objects
- **total_entries** (integer) - Total number of entries

#### Response Example
{
  "connections": [
    {
      "connection_id": "my_postgres",
      "conn_type": "postgres",
      "description": "ETL data warehouse",
      "host": "database",
      "port": 5432,
      "schema": "etl_warehouse",
      "login": "etl_user",
      "password": "***"
    }
  ],
  "total_entries": 1
}
```

--------------------------------

### Initialize Postgresql and Jupyter services

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/README.md

Commands to create a network, build the Jupyter image, and start the PostgreSQL container.

```bash
$ docker network create etl_network
```

```bash
$ docker build -f jupyter-dockerfile .
```

```bash
$ docker-compose --env-file ./.env -f ./postgres-docker-compose.yaml up -d
```

```bash
$ docker ps
```

```bash
$ docker logs jupyter_notebook
```

--------------------------------

### Install dependencies in Dockerfile

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/05-python-libraries-reference.md

Dockerfile instructions to install system-level build tools and Python dependencies for Airflow.

```dockerfile
FROM apache/airflow:2.3.0

USER root
RUN apt-get update && apt-get -y install libpq-dev gcc
USER airflow

COPY requirements.txt /usr/local/airflow/requirements.txt
RUN pip install --user --no-cache-dir -r /usr/local/airflow/requirements.txt
```

--------------------------------

### PostgreSQL Connection String

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/03-docker-compose-services.md

Example connection string format for internal service communication.

```text
postgresql+psycopg2://airflow:airflow@postgres:5432/airflow
```

--------------------------------

### Resolve PostgreSQL Startup Issues

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/11-troubleshooting-and-maintenance.md

Commands to inspect logs and reset volumes when the PostgreSQL container fails to start.

```bash
# Check logs for errors
docker-compose -f ./airflow-docker-compose.yaml logs postgres

# Common issues:
# - Invalid credentials: check POSTGRES_USER, POSTGRES_PASSWORD
# - Existing volume: remove and restart
docker-compose -f ./airflow-docker-compose.yaml down --volumes
docker-compose --env-file ./.env -f ./airflow-docker-compose.yaml up -d postgres
```

--------------------------------

### Verify Docker Prerequisites

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/04-setup-and-operations.md

Check installed versions of Docker and Docker Compose to ensure compatibility.

```bash
docker --version
```

```bash
docker-compose --version
```

--------------------------------

### Create DAG Run Example

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/06-airflow-api-reference.md

cURL command to trigger a new DAG run with configuration parameters.

```bash
curl -X POST \
  -H "Content-Type: application/json" \
  -d '{
    "dag_run_id": "manual_run_123",
    "conf": {"table": "sales"},
    "note": "Triggered from API"
  }' \
  "http://localhost:8080/api/v1/dags/my_etl_dag/dagRuns" \
  --user airflow:airflow
```

--------------------------------

### Pause/Unpause DAG Example

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/06-airflow-api-reference.md

Example curl command to update the pause status of a DAG.

```bash
curl -X PATCH \
  -H "Content-Type: application/json" \
  -d '{"is_paused": false}' \
  "http://localhost:8080/api/v1/dags/my_etl_dag" \
  --user airflow:airflow
```

--------------------------------

### Service Lifecycle Commands

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/02-configuration-reference.md

Standard commands for stopping, starting, and monitoring services during configuration updates.

```bash
docker-compose down
```

```bash
docker-compose up -d
```

```bash
docker logs <container_name>
```

--------------------------------

### Configure Docker Volume Mounts

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/00-README.md

Examples of different volume mounting strategies for Docker Compose services.

```yaml
volumes:
  - ./dags:/opt/airflow/dags
```

```yaml
volumes:
  - pg_volume:/var/lib/postgresql/data
```

```yaml
pg_volume:
  driver: local
  driver_opts:
    type: none
    device: /var/lib/postgresql/data
```

--------------------------------

### Start Service Stacks

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/08-docker-commands-reference.md

Commands to launch the Airflow and Data stacks using specific environment files and compose configurations.

```bash
# Start Airflow stack
docker-compose --env-file ./.env -f ./airflow-docker-compose.yaml up -d

# Start Data stack in separate terminal
docker-compose --env-file ./.env -f ./postgres-docker-compose.yaml up -d

# Verify both running
docker ps -a
```

--------------------------------

### Create SQLAlchemy Engine

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/00-README.md

Example of creating a SQLAlchemy engine for connecting to the data warehouse from within the Docker network.

```python
create_engine('postgresql+psycopg2://etl_user:password@database:5432/etl_warehouse')
```

--------------------------------

### Configure Task Timeouts

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/11-troubleshooting-and-maintenance.md

Example of setting an execution timeout for a PythonOperator.

```python
# Increase task timeout
task = PythonOperator(
    task_id='long_task',
    python_callable=my_func,
    execution_timeout=timedelta(hours=2)
)
```

--------------------------------

### Get Task Instance Details

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/06-airflow-api-reference.md

Retrieve detailed information for a specific task instance.

```http
GET /dags/{dag_id}/dagRuns/{dag_run_id}/taskInstances/{task_id}
```

```json
{
  "task_id": "extract_data",
  "dag_id": "my_etl_dag",
  "execution_date": "2023-04-11T10:00:00+00:00",
  "state": "success",
  "start_date": "2023-04-11T10:05:00+00:00",
  "end_date": "2023-04-11T10:10:00+00:00",
  "duration": 300,
  "max_tries": 0,
  "hostname": "airflow-scheduler",
  "pool": "default_pool",
  "queue": "default",
  "priority_weight": 1
}
```

--------------------------------

### Airflow Init Command Execution

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/03-docker-compose-services.md

This command initializes the required directory structure, sets ownership permissions, and validates the Airflow installation.

```bash
mkdir -p /sources/logs /sources/dags /sources/plugins
chown -R "${AIRFLOW_UID}:0" /sources/{logs,dags,plugins}
exec /entrypoint airflow version
```

--------------------------------

### List DAG Runs Example

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/06-airflow-api-reference.md

cURL command to list DAG runs filtered by state and limit.

```bash
curl -X GET \
  "http://localhost:8080/api/v1/dags/my_etl_dag/dagRuns?state=running&limit=10" \
  --user airflow:airflow
```

--------------------------------

### Configure .env environment variables

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/README.md

Define essential configuration for the Airflow core, database connection, and initial user setup.

```text
# Meta-Database

POSTGRES_USER=airflow

POSTGRES_PASSWORD=airflow

POSTGRES_DB=airflow


# Airflow Core

AIRFLOW__CORE__FERNET_KEY=UKMzEm3yIuFYEq1y3-2FxPNWSVwRASpahmQ9kQfEr8E=

AIRFLOW__CORE__EXECUTOR=LocalExecutor

AIRFLOW__CORE__DAGS_ARE_PAUSED_AT_CREATION=True

AIRFLOW__CORE__LOAD_EXAMPLES=False

AIRFLOW_UID=0

  
# Backend DB

AIRFLOW__DATABASE__SQL_ALCHEMY_CONN=postgresql+psycopg2://airflow:airflow@postgres/airflow

AIRFLOW__DATABASE__LOAD_DEFAULT_CONNECTIONS=False


# Airflow Init

_AIRFLOW_DB_UPGRADE=True

_AIRFLOW_WWW_USER_CREATE=True

_AIRFLOW_WWW_USER_USERNAME=airflow

_AIRFLOW_WWW_USER_PASSWORD=airflow
```

--------------------------------

### SQLAlchemy Engine Configuration

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/07-postgres-schema-and-operations.md

Example of defining a database engine string for SQLAlchemy using the psycopg2 driver.

```python
engine = create_engine('postgresql+psycopg2://etl_user:etl_password@database:5432/etl_warehouse')
```

--------------------------------

### Manage ETL Services with Docker Compose

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/00-README.md

Commands to start, monitor, and stop the Airflow and PostgreSQL services using Docker Compose.

```bash
# Start all services
docker-compose --env-file ./.env -f ./airflow-docker-compose.yaml up -d
docker-compose --env-file ./.env -f ./postgres-docker-compose.yaml up -d

# View logs
docker-compose -f ./airflow-docker-compose.yaml logs -f scheduler

# Access services
curl http://localhost:8080/health  # Airflow health
psql -h localhost -p 5434 -U airflow -d airflow  # Metadata DB
docker logs jupyter_notebook | grep token  # Jupyter token

# Stop services
docker-compose -f ./airflow-docker-compose.yaml down
docker-compose -f ./postgres-docker-compose.yaml down
```

--------------------------------

### Optimize Container Startup

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/08-docker-commands-reference.md

Configure entrypoints to avoid shell wrappers and improve startup performance.

```bash
# Use custom entrypoint to avoid shell wrapper
docker run --entrypoint /bin/python airflow-scheduler -m my_module

# Use exec form in Dockerfile (no shell)
ENTRYPOINT ["python", "-m", "my_module"]  # Preferred
ENTRYPOINT ["python -m my_module"]        # Spawns shell (slower)
```

--------------------------------

### Initialize Environment File

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/08-docker-commands-reference.md

Prepare the .env file required for service configuration.

```bash
# Create from template
cp .env.example .env

# Or create manually
cat > .env << 'EOF'
AIRFLOW__CORE__FERNET_KEY=...
...
EOF

# Verify
cat .env | head -5
```

--------------------------------

### Connect to PostgreSQL with psycopg2

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/05-python-libraries-reference.md

Demonstrates establishing database connections using direct parameters, connection strings, or environment variables.

```python
import psycopg2
from psycopg2 import sql

# Basic connection
conn = psycopg2.connect(
    host='localhost',
    port=5432,
    database='my_database',
    user='my_user',
    password='my_password'
)

# Connection via connection string
conn = psycopg2.connect(
    "dbname=my_database user=my_user password=my_password host=localhost port=5432"
)

# From environment variables
import os
conn = psycopg2.connect(
    host=os.getenv('_POSTGRES_HOST'),
    port=int(os.getenv('_POSTGRES_PORT', 5432)),
    database=os.getenv('_POSTGRES_DB'),
    user=os.getenv('_POSTGRES_USER'),
    password=os.getenv('_POSTGRES_PASSWORD')
)
```

--------------------------------

### GET /dags

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/00-README.md

List all DAGs available in the environment.

```APIDOC
## GET /dags

### Description
List all DAGs available in the environment.

### Method
GET

### Endpoint
/dags
```

--------------------------------

### GET /dags/{dag_id}

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/00-README.md

Retrieve details for a specific DAG.

```APIDOC
## GET /dags/{dag_id}

### Description
Retrieve details for a specific DAG.

### Method
GET

### Endpoint
/dags/{dag_id}

### Parameters
#### Path Parameters
- **dag_id** (string) - Required - The ID of the DAG
```

--------------------------------

### GET /api/v1/dags

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/03-docker-compose-services.md

Retrieves a list of all DAGs defined in the environment.

```APIDOC
## GET /api/v1/dags

### Description
Lists all DAGs currently registered in the Airflow environment.

### Method
GET

### Endpoint
/api/v1/dags
```

--------------------------------

### Data stack startup sequence

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/03-docker-compose-services.md

Outlines the startup order for the data stack services.

```text
1. database starts
2. jupyter_notebook starts (waits for database reachable)
```

--------------------------------

### Connect to PostgreSQL from Host

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/08-docker-commands-reference.md

Connect to local PostgreSQL instances using the psql client.

```bash
# Connect to Airflow metadata DB
psql -h localhost -p 5434 -U airflow -d airflow

# Connect to Data warehouse
psql -h localhost -p 5433 -U etl_user -d etl_warehouse
```

--------------------------------

### GET /dags/{dag_id}/dagRuns

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/00-README.md

List all runs for a specific DAG.

```APIDOC
## GET /dags/{dag_id}/dagRuns

### Description
List all runs for a specific DAG.

### Method
GET

### Endpoint
/dags/{dag_id}/dagRuns

### Parameters
#### Path Parameters
- **dag_id** (string) - Required - The ID of the DAG
```

--------------------------------

### Create Required Directories

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/04-setup-and-operations.md

Set up local storage paths for logs, plugins, and data, ensuring they are writable by the Docker daemon.

```bash
mkdir -p dags logs plugins
mkdir -p /var/lib/postgresql/etl_data
mkdir -p /home/user/notebooks
chmod 777 /var/lib/postgresql/etl_data
chmod 777 /home/user/notebooks
```

--------------------------------

### GET /health

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/03-docker-compose-services.md

Performs a health check on the Airflow webserver service.

```APIDOC
## GET /health

### Description
Returns the health status of the Airflow webserver. Used primarily by Docker for container health monitoring.

### Method
GET

### Endpoint
/health
```

--------------------------------

### Initialize Postgres Database

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/07-postgres-schema-and-operations.md

Creates a new database using the environment variable for the database name.

```sql
CREATE DATABASE ${POSTGRES_DB};
```

--------------------------------

### Get DAG Details Request

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/06-airflow-api-reference.md

Retrieve details for a specific DAG by its ID.

```http
GET /dags/{dag_id}
```

--------------------------------

### Configure Engine and Connection Pooling

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/05-python-libraries-reference.md

Sets up database engines with optional connection pooling parameters.

```python
from sqlalchemy import create_engine, pool

# Basic engine
engine = create_engine('postgresql+psycopg2://user:password@host/database')

# With connection pooling
engine = create_engine(
    'postgresql+psycopg2://user:password@host/database',
    poolclass=pool.QueuePool,
    pool_size=20,
    max_overflow=40,
    pool_recycle=3600
)

# Get connection
with engine.connect() as conn:
    result = conn.execute(text('SELECT 1'))
    print(result.fetchone())
```

--------------------------------

### Perform Full PostgreSQL Backups

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/08-docker-commands-reference.md

Create full database backups in text or custom compressed formats.

```bash
# Backup Airflow metadata (text format)
docker exec postgres pg_dump -U airflow -d airflow > airflow_backup.sql

# Backup with custom format (compressed)
docker exec postgres pg_dump -U airflow -d airflow \
  --format=custom > airflow_backup.dump

# Backup data warehouse
docker exec database pg_dump -U etl_user -d etl_warehouse > warehouse_backup.sql
```

--------------------------------

### GET /dags/{dag_id}/dagRuns/{dag_run_id}

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/06-airflow-api-reference.md

Retrieve the details of a specific DAG run.

```APIDOC
## GET /dags/{dag_id}/dagRuns/{dag_run_id}

### Description
Retrieve the details of a specific DAG run.

### Method
GET

### Endpoint
/dags/{dag_id}/dagRuns/{dag_run_id}

### Parameters
#### Path Parameters
- **dag_id** (string) - Required - The ID of the DAG.
- **dag_run_id** (string) - Required - The ID of the DAG run.

### Response
#### Success Response (200)
- **dag_id** (string) - The ID of the DAG
- **dag_run_id** (string) - The ID of the DAG run
- **state** (string) - The state of the DAG run
- **start_date** (string) - Start timestamp
- **end_date** (string) - End timestamp
```

--------------------------------

### Configure Environment Variables

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/00-README.md

Define core Airflow, PostgreSQL, and volume settings in the .env file.

```bash
# Airflow Core
AIRFLOW__CORE__FERNET_KEY=<key>
AIRFLOW__CORE__EXECUTOR=LocalExecutor
AIRFLOW_UID=0

# PostgreSQL Metadata (Airflow)
POSTGRES_USER=airflow
POSTGRES_PASSWORD=airflow
POSTGRES_DB=airflow

# PostgreSQL Data Warehouse
_POSTGRES_USER=etl_user
_POSTGRES_PASSWORD=etl_password
_POSTGRES_DB=etl_warehouse
_POSTGRES_HOST=database
_POSTGRES_PORT=5432

# Volumes
PG_DATA=/var/lib/postgresql/etl_data
JUPYTER_NOTEBOOKS=/home/user/notebooks
```

--------------------------------

### Airflow stack startup sequence

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/03-docker-compose-services.md

Outlines the dependency-based startup order for the Airflow stack services.

```text
1. postgres starts
   └─ Health check passes
2. airflow-init starts (waits for postgres healthy)
   └─ Completes successfully
3. scheduler starts (waits for postgres healthy AND airflow-init complete)
4. webserver starts (waits for postgres healthy AND airflow-init complete)
```

--------------------------------

### Full Development Cycle Workflow

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/08-docker-commands-reference.md

Standard sequence for resetting the environment, deploying DAGs, and monitoring execution logs.

```bash
# 1. Start fresh
docker-compose -f ./airflow-docker-compose.yaml down --volumes
docker-compose --env-file ./.env -f ./airflow-docker-compose.yaml up -d

# 2. Deploy DAG
cp my_dag.py dags/

# 3. Check logs
docker-compose -f ./airflow-docker-compose.yaml logs -f scheduler

# 4. Trigger run
docker exec airflow-scheduler airflow dags trigger my_dag

# 5. Monitor
docker-compose -f ./airflow-docker-compose.yaml logs -f scheduler

# 6. Cleanup
docker-compose -f ./airflow-docker-compose.yaml down
```

--------------------------------

### SQLAlchemy ORM Usage

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/00-README.md

Example of defining a model and querying data using SQLAlchemy ORM.

```python
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, Session

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

engine = create_engine('postgresql+psycopg2://...')
with Session(engine) as session:
    users = session.query(User).filter_by(name='John').all()
```

--------------------------------

### Get DAG Run Details Request

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/06-airflow-api-reference.md

Endpoint to retrieve details for a specific DAG run.

```http
GET /dags/{dag_id}/dagRuns/{dag_run_id}
```

--------------------------------

### Navigate to Project Root

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/08-docker-commands-reference.md

Ensure commands are executed from the project directory where the compose files reside.

```bash
cd /path/to/etl-environment

# Verify location
ls -la | grep docker-compose.yaml
```

--------------------------------

### Get DAG Details Response

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/06-airflow-api-reference.md

JSON response structure for a specific DAG's details.

```json
{
  "dag_id": "my_etl_dag",
  "description": "ETL pipeline",
  "owner": "airflow",
  "is_paused": false,
  "is_active": true,
  "file_token": "...",
  "fileloc": "/opt/airflow/dags/my_dag.py",
  "tasks": [
    {
      "task_id": "extract_data",
      "task_type": "PythonOperator",
      "queue": "default",
      "pool": "default_pool"
    },
    {
      "task_id": "transform_data",
      "task_type": "PythonOperator",
      "queue": "default",
      "pool": "default_pool"
    }
  ],
  "timetable_description": "Daily at 00:00"
}
```

--------------------------------

### Initialize Host Volumes

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/02-configuration-reference.md

Creates and sets permissions for directories required by the Docker Compose volume mounts.

```bash
mkdir -p /var/lib/postgresql/etl_data
mkdir -p /home/user/notebooks
chmod 777 /var/lib/postgresql/etl_data
chmod 777 /home/user/notebooks
```

--------------------------------

### GET /dags/{dag_id}/dagRuns/{dag_run_id}/taskInstances/{task_id}

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/06-airflow-api-reference.md

Retrieves details for a specific task instance.

```APIDOC
## GET /dags/{dag_id}/dagRuns/{dag_run_id}/taskInstances/{task_id}

### Description
Retrieves the detailed information for a specific task instance within a DAG run.

### Method
GET

### Endpoint
/dags/{dag_id}/dagRuns/{dag_run_id}/taskInstances/{task_id}

### Parameters
#### Path Parameters
- **dag_id** (string) - Required - The ID of the DAG
- **dag_run_id** (string) - Required - The ID of the DAG run
- **task_id** (string) - Required - The ID of the task

### Response
#### Success Response (200)
- **task_id** (string) - Task ID
- **dag_id** (string) - DAG ID
- **state** (string) - Current state of the task

#### Response Example
{
  "task_id": "extract_data",
  "dag_id": "my_etl_dag",
  "execution_date": "2023-04-11T10:00:00+00:00",
  "state": "success",
  "start_date": "2023-04-11T10:05:00+00:00",
  "end_date": "2023-04-11T10:10:00+00:00",
  "duration": 300,
  "max_tries": 0,
  "hostname": "airflow-scheduler",
  "pool": "default_pool",
  "queue": "default",
  "priority_weight": 1
}
```

--------------------------------

### GET /dags/{dag_id}/dagRuns/{dag_run_id}/taskInstances

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/00-README.md

List task instances for a specific DAG run.

```APIDOC
## GET /dags/{dag_id}/dagRuns/{dag_run_id}/taskInstances

### Description
List task instances for a specific DAG run.

### Method
GET

### Endpoint
/dags/{dag_id}/dagRuns/{dag_run_id}/taskInstances

### Parameters
#### Path Parameters
- **dag_id** (string) - Required - The ID of the DAG
- **dag_run_id** (string) - Required - The ID of the DAG run
```

--------------------------------

### Service Dependency Tree

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/01-project-overview.md

Visual representation of the startup order and dependencies for the Airflow and Data stacks.

```text
Airflow Webserver/Scheduler
  └── PostgreSQL (Metadata DB)
       └── airflow-init (one-time bootstrap)

Jupyter Lab
  └── PostgreSQL (Data Warehouse)
```

--------------------------------

### Execute Queries and Fetch Results

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/05-python-libraries-reference.md

Shows how to use a cursor to execute SQL, retrieve data, and inspect column metadata.

```python
import psycopg2

conn = psycopg2.connect('dbname=mydb user=myuser')
cursor = conn.cursor()

# Execute query
cursor.execute("SELECT * FROM users WHERE id = %s", (1,))

# Fetch results
one_row = cursor.fetchone()  # Tuple or None
all_rows = cursor.fetchall()  # List of tuples
many_rows = cursor.fetchmany(10)  # Fetch N rows

# Get description
for desc in cursor.description:
    print(f"Column: {desc[0]}, Type: {desc[1]}")

# Close
cursor.close()
conn.close()
```

--------------------------------

### List DAG Runs Response

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/06-airflow-api-reference.md

Example JSON response structure for a successful DAG run list request.

```json
{
  "dag_runs": [
    {
      "dag_id": "my_etl_dag",
      "dag_run_id": "manual__2023-04-11T10:00:00+00:00",
      "execution_date": "2023-04-11T10:00:00+00:00",
      "state": "running",
      "start_date": "2023-04-11T10:05:00.123456+00:00",
      "end_date": null,
      "external_trigger": false,
      "conf": {}
    }
  ],
  "total_entries": 1
}
```

--------------------------------

### GET /health

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/06-airflow-api-reference.md

Retrieves the current health status of the Airflow system components, including the metadata database and the scheduler.

```APIDOC
## GET /health

### Description
Retrieves the current health status of the Airflow system components, including the metadata database and the scheduler.

### Method
GET

### Endpoint
/health

### Response
#### Success Response (200)
- **metadb** (object) - Status of the metadata database
- **scheduler** (object) - Status of the scheduler

#### Response Example
{
  "metadb": {
    "status": "healthy"
  },
  "scheduler": {
    "status": "healthy"
  }
}
```

--------------------------------

### Create connection Table

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/07-postgres-schema-and-operations.md

Stores credentials and connection parameters for external systems.

```sql
CREATE TABLE connection (
    id INTEGER PRIMARY KEY,
    conn_id VARCHAR(250) UNIQUE,
    conn_type VARCHAR(500),
    description TEXT,
    host VARCHAR(500),
    schema VARCHAR(500),
    login VARCHAR(500),
    password VARCHAR(500),
    port INTEGER,
    is_encrypted BOOLEAN,
    is_extra_encrypted BOOLEAN,
    extra TEXT
);
```

--------------------------------

### Get DAG Run Details Response

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/06-airflow-api-reference.md

JSON response containing the full details of a specific DAG run.

```json
{
  "dag_id": "my_etl_dag",
  "dag_run_id": "manual__2023-04-11T10:00:00+00:00",
  "execution_date": "2023-04-11T10:00:00+00:00",
  "state": "success",
  "start_date": "2023-04-11T10:05:00.123456+00:00",
  "end_date": "2023-04-11T10:15:00.123456+00:00",
  "external_trigger": false,
  "conf": {}
}
```

--------------------------------

### Check Database Host and Port Configuration

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/11-troubleshooting-and-maintenance.md

Commands to verify the connection string used by the scheduler, noting the difference between host and container network paths.

```bash
# Connection string uses container name, not localhost
# From within Docker: postgresql+psycopg2://airflow:airflow@postgres:5432/airflow
# From host: postgresql+psycopg2://airflow:airflow@localhost:5434/airflow

# Check what scheduler is trying
docker exec airflow-scheduler airflow config get-value database sql_alchemy_conn
```

--------------------------------

### Manage Docker Volume Capacity

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/11-troubleshooting-and-maintenance.md

Steps to stop containers, verify host storage, and restart the environment.

```bash
# Stop containers
docker-compose down

# Expand volume (depends on storage driver)
# For bind mount: ensure host has space
df -h /path/to/data

# Restart
docker-compose up -d
```

--------------------------------

### Identify Long-Running Queries

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/07-postgres-schema-and-operations.md

Lists non-idle queries ordered by their start time to help identify potential bottlenecks.

```sql
SELECT pid, usename, application_name, state, query, query_start
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;
```

--------------------------------

### Create task_instance Table

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/07-postgres-schema-and-operations.md

Tracks individual task executions within a DAG run, including state and resource usage.

```sql
CREATE TABLE task_instance (
    task_id VARCHAR(250) NOT NULL,
    dag_id VARCHAR(250) NOT NULL,
    run_id VARCHAR(250) NOT NULL,
    map_index INTEGER,
    state VARCHAR(50),
    try_number INTEGER,
    max_tries INTEGER,
    start_date TIMESTAMP,
    end_date TIMESTAMP,
    duration FLOAT,
    pool VARCHAR(50),
    pool_slots INTEGER,
    queue VARCHAR(50),
    hostname VARCHAR(1000),
    PRIMARY KEY(dag_id, task_id, run_id, map_index),
    FOREIGN KEY(dag_id, run_id) REFERENCES dag_run(dag_id, run_id)
);
```

--------------------------------

### Configure PostgreSQL Buffer Pool

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/02-configuration-reference.md

Add these arguments to the .env file to optimize PostgreSQL performance for production systems.

```bash
POSTGRES_INITDB_ARGS=-c shared_buffers=256MB -c work_mem=16MB
```

--------------------------------

### Manage Docker Images

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/08-docker-commands-reference.md

Commands for listing, building, tagging, pushing, and removing Docker images.

```bash
# List all images
docker images

# List images by project
docker images | grep airflow

# Remove image
docker rmi airflow-custom:2.3.0

# Build image
docker build -f airflow-dockerfile -t airflow-custom:2.3.0 .

# Push to registry (requires login)
docker tag airflow-custom:2.3.0 myregistry/airflow:2.3.0
docker push myregistry/airflow:2.3.0

# View image history
docker history airflow-custom:2.3.0
```

--------------------------------

### Inspect Configuration

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/08-docker-commands-reference.md

Validate and view the resolved configuration of compose files.

```bash
# Show merged compose file
docker-compose -f ./airflow-docker-compose.yaml config

# Show config for specific service
docker-compose -f ./airflow-docker-compose.yaml config | grep -A 20 "scheduler:"

# Validate compose file syntax
docker-compose -f ./airflow-docker-compose.yaml config > /dev/null && echo "Valid"
```

--------------------------------

### Configure PostgreSQL SSL Settings

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/10-networking-and-security.md

Enables SSL in postgresql.conf and reloads the configuration.

```sql
-- In postgresql.conf
ssl = on
ssl_cert_file = 'postgresql.crt'
ssl_key_file = 'postgresql.key'

-- Require SSL for connections
ALTER SYSTEM SET ssl = on;
SELECT pg_reload_conf();
```

--------------------------------

### Project File Structure

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/01-project-overview.md

Visual representation of the project root directory and its core components.

```text
/
├── README.md                       # Setup and usage documentation
├── LICENCE                         # Apache 2.0 license
├── requirements.txt                # Python package dependencies
├── airflow-dockerfile              # Airflow image with dependencies
├── jupyter-dockerfile              # Jupyter Lab image
├── airflow-docker-compose.yaml    # Airflow + metadata DB orchestration
├── postgres-docker-compose.yaml   # Data warehouse + Jupyter orchestration
├── init-db.sql                    # Initial database schema creation
├── dags/                           # (Runtime) Airflow DAG definitions
├── logs/                           # (Runtime) Airflow execution logs
├── plugins/                        # (Runtime) Airflow custom operators/hooks
└── .env                            # (Local) Environment configuration
```

--------------------------------

### Create .env Configuration File

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/04-setup-and-operations.md

Generate the environment variables file required for Airflow and database connectivity.

```bash
cat > .env << 'EOF'
# Airflow Core
AIRFLOW__CORE__FERNET_KEY=UKMzEm3yIuFYEq1y3-2FxPNWSVwRASpahmQ9kQfEr8E=
AIRFLOW__CORE__EXECUTOR=LocalExecutor
AIRFLOW__CORE__DAGS_ARE_PAUSED_AT_CREATION=True
AIRFLOW__CORE__LOAD_EXAMPLES=False
AIRFLOW_UID=0

# Airflow Metadata DB
POSTGRES_USER=airflow
POSTGRES_PASSWORD=airflow
POSTGRES_DB=airflow

# Airflow Database Connection
AIRFLOW__DATABASE__SQL_ALCHEMY_CONN=postgresql+psycopg2://airflow:airflow@postgres/airflow
AIRFLOW__DATABASE__LOAD_DEFAULT_CONNECTIONS=False

# Airflow Initialization
_AIRFLOW_DB_UPGRADE=True
_AIRFLOW_WWW_USER_CREATE=True
_AIRFLOW_WWW_USER_USERNAME=airflow
_AIRFLOW_WWW_USER_PASSWORD=airflow

# ETL Data Warehouse
_POSTGRES_DB=etl_warehouse
_POSTGRES_USER=etl_user
_POSTGRES_PASSWORD=etl_password
_POSTGRES_HOST=database
_POSTGRES_PORT=5432

# Volumes
PG_DATA=/var/lib/postgresql/etl_data
JUPYTER_NOTEBOOKS=/home/user/notebooks
EOF
```

--------------------------------

### Connect to PostgreSQL with SSL

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/10-networking-and-security.md

Establishes a secure connection to PostgreSQL using sslmode.

```python
import psycopg2

conn = psycopg2.connect(
    host='database',
    port=5432,
    sslmode='require',
    user='etl_user',
    password='etl_password',
    database='etl_warehouse'
)
```

--------------------------------

### Troubleshoot Service Communication

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/10-networking-and-security.md

Verify connectivity, DNS resolution, and port accessibility between containers.

```bash
# Check network connectivity
docker exec scheduler ping postgres

# Check if DNS works
docker exec scheduler getent hosts postgres

# Check port accessibility
docker exec scheduler nc -zv postgres 5432
```

--------------------------------

### Build Docker Images

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/04-setup-and-operations.md

Build custom images for Airflow and Jupyter services.

```bash
docker build -f airflow-dockerfile -t airflow-custom:2.3.0 .
```

```bash
docker images | grep airflow-custom
```

```bash
docker build -f jupyter-dockerfile -t jupyter-custom:latest .
```

```bash
docker images | grep jupyter-custom
```

--------------------------------

### Monitor Container Resource Usage

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/08-docker-commands-reference.md

Inspect CPU, memory, and detailed container configuration stats.

```bash
# CPU and memory usage
docker stats --no-stream

# Specific container
docker stats airflow-scheduler --no-stream

# Detailed stats
docker inspect airflow-scheduler | grep -A 20 MemoryLimit
```

--------------------------------

### Connect to Custom Host via Python

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/10-networking-and-security.md

Access manually defined hosts using standard database connection libraries.

```python
conn = psycopg2.connect(host='custom-host', port=5432, ...)
```

--------------------------------

### Define project dependencies

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/05-python-libraries-reference.md

List of required library versions for the ETL project.

```text
sqlalchemy==1.4.4
psycopg2==2.8.6
```

--------------------------------

### Optimize Database Queries

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/11-troubleshooting-and-maintenance.md

Use these SQL commands to identify slow queries, enable logging, and create indexes for performance improvements.

```sql
-- Enable query logging
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- Log queries >1 second
SELECT pg_reload_conf();

-- Find slow queries
SELECT query, calls, mean_time, max_time
FROM pg_stat_statements
ORDER BY mean_time DESC LIMIT 10;

-- Create missing indexes
CREATE INDEX CONCURRENTLY idx_fact_sales_user_key ON fact_sales(user_key);

-- Analyze execution plan
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM fact_sales WHERE user_key = 123;
```

--------------------------------

### Create Airflow Connection

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/04-setup-and-operations.md

Configure external system credentials via REST API or environment variables.

```bash
curl -X POST \
  -H "Content-Type: application/json" \
  -d '{
    "conn_id": "my_postgres",
    "conn_type": "postgres",
    "host": "database",
    "port": 5432,
    "database": "etl_warehouse",
    "login": "etl_user",
    "password": "etl_password"
  }' \
  "http://localhost:8080/api/v1/connections" \
  --user airflow:airflow
```

```bash
# Add to .env
AIRFLOW_CONN_MY_POSTGRES=postgresql://etl_user:etl_password@database:5432/etl_warehouse
```

--------------------------------

### Restore from Backup

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/04-setup-and-operations.md

Restore the ETL warehouse database from a local SQL dump file.

```bash
docker cp ./warehouse_backup.sql database:/tmp/

docker exec database psql \
  -U etl_user \
  -d etl_warehouse \
  -f /tmp/warehouse_backup.sql
```

--------------------------------

### List Connections

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/06-airflow-api-reference.md

Retrieve a list of configured connections with optional pagination parameters.

```http
GET /connections
```

```json
{
  "connections": [
    {
      "connection_id": "my_postgres",
      "conn_type": "postgres",
      "description": "ETL data warehouse",
      "host": "database",
      "port": 5432,
      "schema": "etl_warehouse",
      "login": "etl_user",
      "password": "***"
    }
  ],
  "total_entries": 1
}
```

--------------------------------

### Create xcom Table

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/07-postgres-schema-and-operations.md

Stores serialized Python objects for inter-task communication.

```sql
CREATE TABLE xcom (
    id INTEGER PRIMARY KEY,
    key VARCHAR(512),
    value BYTEA,  -- serialized Python object
    timestamp TIMESTAMP,
    execution_date TIMESTAMP,
    task_id VARCHAR(250),
    dag_id VARCHAR(250),
    run_id VARCHAR(250),
    map_index INTEGER,
    UNIQUE(dag_id, task_id, execution_date, key, map_index)
);
```

--------------------------------

### View Container Information

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/08-docker-commands-reference.md

Retrieve detailed metadata, health status, environment variables, and network configuration for containers.

```bash
# Container details
docker inspect airflow-scheduler

# Health status
docker inspect airflow-scheduler | grep -A 10 '"Health"'

# Environment variables
docker inspect airflow-scheduler | jq '.[0].Config.Env'

# Port mappings
docker port airflow-scheduler

# Network information
docker network inspect etl_network
```

--------------------------------

### Manage Docker Networks

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/08-docker-commands-reference.md

Commands for creating, inspecting, removing, and connecting containers to custom Docker networks.

```bash
# Create custom network
docker network create etl_network

# List networks
docker network ls

# Inspect network
docker network inspect etl_network

# Remove network
docker network rm etl_network

# Connect running container to network
docker network connect my_network my_container

# Disconnect container from network
docker network disconnect my_network my_container

# Check DNS resolution
docker exec airflow-scheduler nslookup postgres
```

--------------------------------

### Diagnose PostgreSQL Disk Space Issues

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/11-troubleshooting-and-maintenance.md

Commands to inspect Docker volumes, database sizes, table sizes, and host disk availability.

```bash
# Check volume space
docker volume ls
docker volume inspect <volume_name>

# Check database size
docker exec postgres psql -U airflow -d airflow -c \
  "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;"

# Check table sizes
docker exec postgres psql -U airflow -d airflow -c \
  "SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) 
   FROM pg_tables WHERE schemaname='public' 
   ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC LIMIT 10;"

# Check host disk
df -h $(mount | grep postgres | awk '{print $3}')
```

--------------------------------

### Clone Repository

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/04-setup-and-operations.md

Download the project source code from the remote repository.

```bash
git clone https://github.com/sergiomelgarejo/etl-environment.git
cd etl-environment
```

--------------------------------

### Resolve Airflow webserver issues

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/11-troubleshooting-and-maintenance.md

Commands to restart containers, verify database connections, handle port conflicts, and adjust resource limits.

```bash
docker-compose -f ./airflow-docker-compose.yaml up -d webserver
docker-compose -f ./airflow-docker-compose.yaml logs -f webserver
```

```bash
# Check PostgreSQL is running
docker-compose -f ./airflow-docker-compose.yaml ps postgres

# Check PostgreSQL health
docker-compose -f ./airflow-docker-compose.yaml logs postgres | grep healthy

# Test connection from webserver
docker exec airflow-webserver \
  psql -h postgres -U airflow -d airflow -c "SELECT 1"
```

```bash
# Find what's using port 8080
lsof -i :8080

# Change port in docker-compose.yaml
ports:
  - "8081:8080"

# Restart
docker-compose -f ./airflow-docker-compose.yaml up -d --force-recreate
```

```bash
# Check Docker resources
docker stats --no-stream

# Increase Docker memory
# Docker Desktop: Preferences → Resources → Memory
# Or in docker-compose.yaml:
deploy:
  resources:
    limits:
      memory: 2G
```

--------------------------------

### PostgreSQL Connection Strings

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/07-postgres-schema-and-operations.md

Connection URI formats for internal Docker services and external host machine access.

```text
postgresql://user:password@service_name:5432/database
postgresql://etl_user:etl_password@database:5432/etl_warehouse
```

```text
postgresql://user:password@localhost:5433/database
postgresql://etl_user:etl_password@localhost:5433/etl_warehouse
```

--------------------------------

### Troubleshoot Database Connection

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/04-setup-and-operations.md

Commands to check database health and test connectivity using Python.

```bash
docker-compose -f postgres-docker-compose.yaml ps database
```

```bash
docker-compose -f postgres-docker-compose.yaml logs database
```

```python
import psycopg2
try:
    conn = psycopg2.connect(host="database", port=5432, user="etl_user", password="etl_password")
    print("Connected successfully")
except Exception as e:
    print(f"Connection failed: {e}")
```

--------------------------------

### Troubleshoot Container Networking

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/08-docker-commands-reference.md

Test connectivity, DNS resolution, and port availability within a running container.

```bash
# Test DNS from container
docker exec airflow-scheduler ping postgres

# Check port connectivity
docker exec airflow-scheduler nc -zv postgres 5432

# View network connections
docker exec airflow-scheduler netstat -tuln

# Inspect network DNS
docker exec airflow-scheduler cat /etc/hosts
```

--------------------------------

### POST /connections

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/00-README.md

Create a new connection.

```APIDOC
## POST /connections

### Description
Create a new connection.

### Method
POST

### Endpoint
/connections
```

--------------------------------

### Backup Database via Docker

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/07-postgres-schema-and-operations.md

Creates a custom format backup of the Postgres database using pg_dump inside a Docker container.

```bash
docker exec database pg_dump \
  -U etl_user \
  -d etl_warehouse \
  --format=custom \
  --file=/tmp/warehouse_backup.dump
```

--------------------------------

### Resolve DAG visibility issues

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/11-troubleshooting-and-maintenance.md

Steps to fix syntax errors, file permissions, volume mounts, and parsing intervals.

```bash
# Check syntax
docker exec airflow-scheduler \
  python -m py_compile /opt/airflow/dags/my_dag.py

# View error
docker compose -f ./airflow-docker-compose.yaml logs scheduler | tail -50

# Fix and restart
docker-compose -f ./airflow-docker-compose.yaml restart scheduler
```

```bash
# Check ownership
docker exec airflow-scheduler ls -la /opt/airflow/dags/ | head

# Fix in Dockerfile if needed
# RUN chown -R airflow:airflow /opt/airflow/dags
```

```bash
# Verify volume mount in docker-compose
docker inspect airflow-scheduler | grep -A 5 Mounts

# Check if mounted on host
ls -la ./dags/

# If folder doesn't exist
mkdir -p dags logs plugins
docker-compose -f ./airflow-docker-compose.yaml up -d --force-recreate
```

```bash
# Default is 30 seconds. Increase if needed:
# In .env or airflow.cfg
AIRFLOW__CORE__DAG_DIR_LIST_INTERVAL=10
```

--------------------------------

### Connect to PostgreSQL Data Warehouse

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/04-setup-and-operations.md

Use these commands or snippets to establish a connection to the data warehouse from different environments.

```bash
psql -h localhost -p 5433 -U etl_user -d etl_warehouse
```

```python
import psycopg2

conn = psycopg2.connect(
    host="database",
    port=5432,
    database="etl_warehouse",
    user="etl_user",
    password="etl_password"
)

cursor = conn.cursor()
cursor.execute("SELECT version();")
print(cursor.fetchone())
```

```python
from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime
import psycopg2

def query_warehouse():
    conn = psycopg2.connect(
        host="database",
        port=5432,
        database="etl_warehouse",
        user="etl_user",
        password="etl_password"
    )
    cursor = conn.cursor()
    cursor.execute("SELECT count(*) FROM some_table;")
    result = cursor.fetchone()
    print(f"Table count: {result[0]}")
    conn.close()

with DAG('example_dag', start_date=datetime(2023, 1, 1)) as dag:
    task = PythonOperator(task_id='query', python_callable=query_warehouse)
```

--------------------------------

### Manage Docker Network

Source: https://github.com/sergiomelgarejo/etl-environment/blob/main/_autodocs/04-setup-and-operations.md

Create and verify the custom network required for inter-container communication.

```bash
docker network create etl_network
```

```bash
docker network ls | grep etl_network
```