{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "11bf6437-3517-4fa0-bc9e-ebf5bc23f60c",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "# PostgreSQL Replication Slot Monitoring and Cleanup\n",
    "\n",
    "When you ingest from PostgreSQL into Databricks, each ingestion pipeline uses a\n",
    "PostgreSQL **replication slot** to track its position in the database's write-ahead log\n",
    "(WAL). If a pipeline stops consuming (for example it is paused, stopped, or removed) but\n",
    "its slot is left behind, the slot keeps holding on to WAL so it can never be reclaimed —\n",
    "which can fill up your database's disk.\n",
    "\n",
    "This notebook checks how much WAL each of your pipelines' replication slots is holding,\n",
    "and when a slot exceeds the allowed size it either **drops that slot** or **fails this\n",
    "notebook** so you are alerted (you choose which, using the `action` parameter). It only\n",
    "removes the replication *slot*; PostgreSQL then reclaims the WAL that slot was holding.\n",
    "\n",
    "Run it on a schedule and subscribe to the job's failure notifications so you are emailed\n",
    "whenever action is needed.\n",
    "\n",
    "### Supported pipelines\n",
    "This notebook supports PostgreSQL ingestion pipelines that connect **directly** to your\n",
    "database. Pipelines that use a separate ingestion gateway are not supported and are\n",
    "reported so you can exclude them.\n",
    "\n",
    "### How to configure it\n",
    "You describe your setup once per **database**, in the `monitored_instances` list below.\n",
    "For each database you provide its connection details and (optionally) a size limit, then\n",
    "list the ingestion pipelines running against it together with the replication slots each\n",
    "one uses. Grouping by database lets you run several pipelines against the same PostgreSQL\n",
    "server without repeating the connection details, and keeps the size limit — which is a\n",
    "property of the whole database — in one place.\n",
    "\n",
    "### What it does on each run\n",
    "Every database, pipeline and slot is handled **independently** — a problem with one is\n",
    "recorded and the rest are still checked. The notebook always prints a full report at the\n",
    "end (what it did, and anything that needs your attention) and only then fails, if the\n",
    "report contains any attention items.\n",
    "\n",
    "1. Reads your database username and password from **Databricks secrets** (so no\n",
    "   credentials are stored in this notebook).\n",
    "2. For each pipeline you listed, confirms it is a supported PostgreSQL ingestion pipeline\n",
    "   and that the slot names you provided still match the slots the pipeline is actually\n",
    "   using. If they differ (for example a source catalog was added or removed), it records\n",
    "   what to update.\n",
    "3. Checks every PostgreSQL ingestion pipeline in your workspace and records any that are\n",
    "   missing from `monitored_instances`, so a newly-created pipeline's slots cannot go\n",
    "   unmonitored.\n",
    "4. Measures how much WAL each replication slot you listed is currently holding.\n",
    "5. Compares it against the size limit and takes the action you selected (drop the slot,\n",
    "   or just report it). If a slot being dropped still has a connection attached, that\n",
    "   connection is terminated first so the slot can be removed; if a pipeline immediately\n",
    "   reconnects, that connection is terminated too, up to a few attempts.\n",
    "6. Prints a report of the actions taken and the items needing attention, and fails the\n",
    "   notebook if there is anything to act on (so the job's failure notification alerts you).\n",
    "\n",
    "### Which size limit is used (per database)\n",
    "The limit is PostgreSQL's `max_slot_wal_keep_size` — the maximum amount of WAL a slot is\n",
    "allowed to retain. This notebook uses, in order:\n",
    "1. the `max_slot_wal_keep_size` you set for that database in `monitored_instances`, if\n",
    "   you provided one (this takes precedence, so you can enforce your own limit); else\n",
    "2. the `max_slot_wal_keep_size` configured on the database itself, if it is set.\n",
    "\n",
    "If you provide a value **and** the database also sets one, this notebook uses your value\n",
    "(the report shows both so it is clear which limit is in force). This does not change any\n",
    "setting on the database — it only decides the limit this notebook enforces.\n",
    "\n",
    "If you do not provide a value **and** the database does not set one either (it reports\n",
    "`-1`, meaning unlimited), the limit cannot be determined: that database is reported for\n",
    "attention and its slots are skipped, while all other databases are still checked. To fix\n",
    "it, provide `max_slot_wal_keep_size` for that database in `monitored_instances` — or, for\n",
    "AWS RDS/Aurora databases, set `max_slot_wal_keep_size` on the database via its parameter\n",
    "group.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "showTitle": false,
     "title": ""
    },
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "%pip install psycopg2-binary\n",
    "%restart_python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "showTitle": true,
     "title": "Logging"
    },
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "import logging\n",
    "import time\n",
    "\n",
    "logging.basicConfig(level=logging.INFO, format=\"%(asctime)s %(levelname)s %(message)s\")\n",
    "logger = logging.getLogger(\"pg_slot_wal_cleanup\")\n",
    "logger.setLevel(logging.INFO)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "showTitle": true,
     "title": "Configuration and data classes"
    }
   },
   "outputs": [],
   "source": [
    "from dataclasses import dataclass, field\n",
    "from typing import Dict, List, Optional\n",
    "\n",
    "# --- Defaults -----------------------------------------------------------------\n",
    "DEFAULT_PG_PORT = 5432\n",
    "DEFAULT_DB_NAME = \"postgres\"\n",
    "\n",
    "# Give up (and report) if a database connection cannot be established within this many seconds.\n",
    "CONNECT_TIMEOUT_SECS = 30\n",
    "\n",
    "# Postgres sentinel meaning \"no limit / unset\" for max_slot_wal_keep_size.\n",
    "MAX_SLOT_WAL_KEEP_SIZE_UNSET = -1\n",
    "\n",
    "# Dropping a replication slot fails while a consumer is connected to it. We terminate that\n",
    "# backend and retry; if a consumer reconnects with a new PID we terminate that too. These\n",
    "# bound how many times we try.\n",
    "MAX_DROP_ATTEMPTS = 4\n",
    "DROP_RETRY_DELAY_SECS = 1\n",
    "\n",
    "# Safety cap on how many slots a single DROP_SLOTS run may drop. You MUST set this to a\n",
    "# positive number before using action=DROP_SLOTS (the notebook fails otherwise) -- this forces\n",
    "# a conscious choice for a destructive knob rather than shipping a silent default. Normal\n",
    "# cleanup is a handful of orphaned slots; a run that wants to drop more than this almost always\n",
    "# signals a misconfiguration (a too-low max_slot_wal_keep_size, or a workspace-wide WAL spike)\n",
    "# that would otherwise mass-drop production slots. Once the cap is hit, remaining over-limit\n",
    "# slots are reported as attention items instead of dropped, so the run fails for human review.\n",
    "# Left as -1 (unset) by default; a reasonable starting value is 5 (or up to ~10 if you\n",
    "# routinely clean up several slots at once). Only used for DROP_SLOTS.\n",
    "MAX_DROPS_PER_RUN = -1\n",
    "\n",
    "\n",
    "@dataclass\n",
    "class PipelineSlots:\n",
    "    \"\"\"A PostgreSQL ingestion pipeline and the replication slots it uses.\n",
    "\n",
    "    Attributes:\n",
    "        pipeline_id: the id of your PostgreSQL ingestion pipeline. You can find this in the\n",
    "               pipeline's page URL in the Databricks workspace.\n",
    "        slots: the replication slot names this pipeline uses, exactly as they appear in\n",
    "               PostgreSQL (pg_replication_slots). You provide them here; the notebook checks\n",
    "               them against the slots the pipeline is actually configured with and tells you\n",
    "               if they no longer match (for example after you add or remove a source catalog).\n",
    "    \"\"\"\n",
    "    pipeline_id: str\n",
    "    slots: List[str] = field(default_factory=list)\n",
    "\n",
    "\n",
    "@dataclass\n",
    "class MonitoredInstance:\n",
    "    \"\"\"One PostgreSQL database and the ingestion pipelines running against it.\n",
    "\n",
    "    The connection details and the size limit belong to the database, so you provide them\n",
    "    once here. The username and password are read from Databricks secrets rather than being\n",
    "    written in this notebook.\n",
    "\n",
    "    Attributes:\n",
    "        host / port / dbname: how to connect to your PostgreSQL database.\n",
    "        secret_scope: the Databricks secret scope that holds this database's credentials.\n",
    "        user_secret_key / password_secret_key: the secret keys (within that scope) for the\n",
    "               username and password.\n",
    "        pipelines: the ingestion pipelines running against this database, each with its slots.\n",
    "        max_slot_wal_keep_size: optional size limit in BYTES for how much WAL a slot on this\n",
    "               database may hold. This is only used when the database itself does not set\n",
    "               max_slot_wal_keep_size (it reports -1). If the database has no limit set and\n",
    "               you leave this empty, the notebook fails because there is no limit to enforce.\n",
    "    \"\"\"\n",
    "    host: str\n",
    "    secret_scope: str\n",
    "    user_secret_key: str\n",
    "    password_secret_key: str\n",
    "    port: int = DEFAULT_PG_PORT\n",
    "    dbname: str = DEFAULT_DB_NAME\n",
    "    pipelines: List[PipelineSlots] = field(default_factory=list)\n",
    "    max_slot_wal_keep_size: Optional[int] = None\n",
    "\n",
    "\n",
    "# =============================================================================\n",
    "# CONFIGURATION  --  EDIT THIS\n",
    "# =============================================================================\n",
    "# List your databases below. For each database, provide the connection details (and an\n",
    "# optional size limit) once, then list the ingestion pipelines running against it along with\n",
    "# the replication slots each pipeline uses.\n",
    "#\n",
    "# Example:\n",
    "# monitored_instances: List[MonitoredInstance] = [\n",
    "#     MonitoredInstance(\n",
    "#         host=\"prod-postgres.company.com\",\n",
    "#         port=5432,\n",
    "#         dbname=\"production_db\",\n",
    "#         secret_scope=\"pg_cdc\",\n",
    "#         user_secret_key=\"prod_user\",\n",
    "#         password_secret_key=\"prod_password\",\n",
    "#         max_slot_wal_keep_size=5 * 1024**3,  # optional: 5 GiB fallback for this instance\n",
    "#         pipelines=[\n",
    "#             PipelineSlots(\"<pipeline-id-1>\", [\"slot1\", \"slot2\"]),\n",
    "#             PipelineSlots(\"<pipeline-id-2>\", [\"slot3\", \"slot4\"]),\n",
    "#         ],\n",
    "#     ),\n",
    "# ]\n",
    "\n",
    "monitored_instances: List[MonitoredInstance] = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "showTitle": true,
     "title": "Widgets and input validation"
    },
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "dbutils.widgets.dropdown(\"action\", \"FAIL_ONLY\", [\"FAIL_ONLY\", \"DROP_SLOTS\"],\n",
    "                         \"When a slot exceeds the size limit: FAIL_ONLY = only alert you; \"\n",
    "                         \"DROP_SLOTS = drop the slot\")\n",
    "\n",
    "\n",
    "def _fail(msg: str):\n",
    "    \"\"\"Log and raise -- fails the notebook (and its job) so you are notified by email.\"\"\"\n",
    "    logger.error(msg)\n",
    "    raise Exception(msg)\n",
    "\n",
    "\n",
    "# --- Validate action ---\n",
    "action = dbutils.widgets.get(\"action\").strip()\n",
    "if action not in (\"FAIL_ONLY\", \"DROP_SLOTS\"):\n",
    "    _fail(f\"Invalid action '{action}'. Must be FAIL_ONLY or DROP_SLOTS.\")\n",
    "\n",
    "# --- Validate the drop cap (required for DROP_SLOTS) ---\n",
    "# DROP_SLOTS is destructive, so we require an explicit positive MAX_DROPS_PER_RUN rather than\n",
    "# a silent default -- the operator must consciously choose the per-run blast radius.\n",
    "if action == \"DROP_SLOTS\" and (not isinstance(MAX_DROPS_PER_RUN, int)\n",
    "                               or MAX_DROPS_PER_RUN <= 0):\n",
    "    _fail(\"MAX_DROPS_PER_RUN is not set. action=DROP_SLOTS requires an explicit positive cap \"\n",
    "          \"on how many slots a single run may drop (it defaults to -1 / unset). Set \"\n",
    "          \"MAX_DROPS_PER_RUN to a positive number (a reasonable starting value is 5, or up to \"\n",
    "          \"~10 if you routinely clean up several slots at once) and re-run.\")\n",
    "\n",
    "# --- Validate instance config ---\n",
    "if not monitored_instances:\n",
    "    _fail(\"monitored_instances is empty. Add at least one database (a MonitoredInstance) \"\n",
    "          \"with its pipelines before running this notebook.\")\n",
    "\n",
    "_seen_pipeline_ids = set()   # across ALL databases -- a pipeline belongs to exactly one DB\n",
    "_seen_instances = set()      # (host, port, dbname)\n",
    "for inst in monitored_instances:\n",
    "    if not inst.host or not inst.secret_scope or not inst.user_secret_key \\\n",
    "            or not inst.password_secret_key:\n",
    "        _fail(f\"Database {inst.host!r}: host, secret_scope, user_secret_key and \"\n",
    "              f\"password_secret_key are all required.\")\n",
    "    inst_key = (inst.host, inst.port, inst.dbname)\n",
    "    if inst_key in _seen_instances:\n",
    "        _fail(f\"Database {inst_key} is listed more than once in monitored_instances. \"\n",
    "              f\"Combine its pipelines under a single entry.\")\n",
    "    _seen_instances.add(inst_key)\n",
    "    if inst.max_slot_wal_keep_size is not None and inst.max_slot_wal_keep_size <= 0:\n",
    "        _fail(f\"Database {inst.host}: max_slot_wal_keep_size must be a positive number of bytes.\")\n",
    "    if not inst.pipelines:\n",
    "        _fail(f\"Database {inst.host}: list at least one pipeline in 'pipelines'.\")\n",
    "    for p in inst.pipelines:\n",
    "        if not p.pipeline_id:\n",
    "            _fail(f\"Database {inst.host}: a pipeline entry is missing its pipeline_id.\")\n",
    "        if p.pipeline_id in _seen_pipeline_ids:\n",
    "            _fail(f\"Pipeline {p.pipeline_id} is listed more than once in monitored_instances. \"\n",
    "                  f\"List each pipeline under a single database.\")\n",
    "        _seen_pipeline_ids.add(p.pipeline_id)\n",
    "        if not p.slots:\n",
    "            _fail(f\"Pipeline {p.pipeline_id}: list at least one replication slot in 'slots'.\")\n",
    "        _seen_slots = set()\n",
    "        for slot_name in p.slots:\n",
    "            if not slot_name:\n",
    "                _fail(f\"Pipeline {p.pipeline_id}: a slot name is empty.\")\n",
    "            if slot_name in _seen_slots:\n",
    "                _fail(f\"Pipeline {p.pipeline_id}: slot '{slot_name}' is listed more than once.\")\n",
    "            _seen_slots.add(slot_name)\n",
    "\n",
    "# Flat view of all monitored pipelines, for the coverage check and logging.\n",
    "all_monitored_pipeline_ids = {p.pipeline_id for inst in monitored_instances for p in inst.pipelines}\n",
    "\n",
    "logger.info(\"Action: %s\", action)\n",
    "logger.info(\"Monitored instances: %s\", [inst.host for inst in monitored_instances])\n",
    "logger.info(\"Monitored pipelines: %s\", sorted(all_monitored_pipeline_ids))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "showTitle": true,
     "title": "Databricks context and API helpers"
    },
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "from typing import Any\n",
    "\n",
    "_ctx = dbutils.notebook.entry_point.getDbutils().notebook().getContext()\n",
    "\n",
    "\n",
    "def _ctx_opt(opt):\n",
    "    \"\"\"Read an optional value from the notebook context, or None if absent.\"\"\"\n",
    "    try:\n",
    "        return opt.get() if opt.isDefined() else None\n",
    "    except Exception:\n",
    "        return None\n",
    "\n",
    "\n",
    "# Use the canonical API URL (e.g. https://<workspace>.cloud.databricks.com). The notebook\n",
    "# apiToken() is accepted there; the browser/vanity host\n",
    "# rejects it with 401 \"Credential was not sent or was of an unsupported type\". Fall back to\n",
    "# the browser host only if apiUrl() is unavailable.\n",
    "workspace_url = _ctx_opt(_ctx.apiUrl())\n",
    "if workspace_url:\n",
    "    workspace_url = workspace_url.rstrip(\"/\")\n",
    "else:\n",
    "    _browser_host = _ctx_opt(_ctx.browserHostName())\n",
    "    workspace_url = f\"https://{_browser_host}\" if _browser_host else None\n",
    "_api_token = _ctx_opt(_ctx.apiToken())\n",
    "if not workspace_url or not _api_token:\n",
    "    _fail(\"Failed to retrieve workspace URL or API token from Databricks context.\")\n",
    "api_headers = {\"Authorization\": f\"Bearer {_api_token}\", \"Content-Type\": \"application/json\"}\n",
    "logger.info(\"Workspace URL: %s\", workspace_url)\n",
    "\n",
    "\n",
    "def get_secret(scope: str, key: str) -> str:\n",
    "    # Raises on failure so the caller can capture it as one report entry rather than\n",
    "    # aborting the whole run.\n",
    "    try:\n",
    "        val = dbutils.secrets.get(scope=scope, key=key)\n",
    "    except Exception as e:\n",
    "        raise RuntimeError(f\"could not read secret {scope}/{key}: {e}\")\n",
    "    if not val:\n",
    "        raise RuntimeError(f\"secret {scope}/{key} is empty\")\n",
    "    return val\n",
    "\n",
    "\n",
    "def fetch_pipeline(pipeline_id: str) -> Dict[str, Any]:\n",
    "    \"\"\"Return the pipeline JSON. Does not raise on RESOURCE_DOES_NOT_EXIST (caller inspects).\"\"\"\n",
    "    try:\n",
    "        resp = requests.get(f\"{workspace_url}/api/2.0/pipelines/{pipeline_id}\",\n",
    "                            headers=api_headers, timeout=30)\n",
    "    except requests.exceptions.RequestException as e:\n",
    "        raise RuntimeError(f\"could not fetch pipeline {pipeline_id}: {e}\")\n",
    "    return resp.json()\n",
    "\n",
    "\n",
    "def list_managed_ingestion_pipelines() -> List[Dict[str, Any]]:\n",
    "    \"\"\"List MANAGED_INGESTION pipelines in the workspace (paginated).\n",
    "\n",
    "    The pipeline_type filter is pushed server-side so we skip ETL/other pipeline types\n",
    "    entirely; only MANAGED_INGESTION candidates come back (Direct CDC PG is a subset).\n",
    "    \"\"\"\n",
    "    pipelines: List[Dict[str, Any]] = []\n",
    "    params = {\"max_results\": 100, \"filter\": \"pipeline_type IN ('MANAGED_INGESTION')\"}\n",
    "    while True:\n",
    "        try:\n",
    "            resp = requests.get(f\"{workspace_url}/api/2.0/pipelines\",\n",
    "                                headers=api_headers, params=params, timeout=30)\n",
    "            resp.raise_for_status()\n",
    "        except requests.exceptions.RequestException as e:\n",
    "            raise RuntimeError(f\"could not list pipelines in the workspace: {e}\")\n",
    "        body = resp.json()\n",
    "        pipelines.extend(body.get(\"statuses\", []))\n",
    "        token = body.get(\"next_page_token\")\n",
    "        if not token:\n",
    "            break\n",
    "        params[\"page_token\"] = token\n",
    "    return pipelines"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "showTitle": true,
     "title": "Identify supported PostgreSQL ingestion pipelines"
    },
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "def _ingestion_definition(spec: Dict[str, Any]) -> Optional[Dict[str, Any]]:\n",
    "    return (spec or {}).get(\"ingestion_definition\")\n",
    "\n",
    "\n",
    "def is_two_pipeline_mode(ingestion_definition: Optional[Dict[str, Any]]) -> bool:\n",
    "    \"\"\"2-pipeline mode = ingestion pipeline references a separate gateway pipeline.\"\"\"\n",
    "    return bool(ingestion_definition and ingestion_definition.get(\"ingestion_gateway_id\"))\n",
    "\n",
    "\n",
    "def is_direct_cdc_postgres(spec: Dict[str, Any]) -> bool:\n",
    "    \"\"\"A single-pipeline Direct CDC PostgreSQL pipeline.\n",
    "\n",
    "    Direct CDC = source_type POSTGRESQL + CDC connector + non-empty source_configurations,\n",
    "    and NOT 2-pipeline gateway mode.\n",
    "    \"\"\"\n",
    "    if not spec:\n",
    "        return False\n",
    "    if spec.get(\"pipeline_type\") != \"MANAGED_INGESTION\":\n",
    "        return False\n",
    "    idef = _ingestion_definition(spec)\n",
    "    if not idef:\n",
    "        return False\n",
    "    if is_two_pipeline_mode(idef):\n",
    "        return False\n",
    "    source_configs = idef.get(\"source_configurations\") or []\n",
    "    if not source_configs:\n",
    "        return False\n",
    "    # source_type / connection type live on the ingestion definition (Direct CDC).\n",
    "    source_type = (idef.get(\"source_type\") or \"\").upper()\n",
    "    if source_type != \"POSTGRESQL\":\n",
    "        return False\n",
    "    connector_type = (idef.get(\"connector_type\") or idef.get(\"ingestion_source_type\") or \"\").upper()\n",
    "    # Prefer an explicit CDC marker when present; empty is acceptable for POSTGRESQL.\n",
    "    if connector_type and \"CDC\" not in connector_type:\n",
    "        return False\n",
    "    return True\n",
    "\n",
    "\n",
    "def slots_from_ingestion_definition(ingestion_definition: Dict[str, Any]) -> List[str]:\n",
    "    \"\"\"Extract configured replication slot names from a Direct CDC PG ingestion definition.\"\"\"\n",
    "    slots: List[str] = []\n",
    "    for source_config in ingestion_definition.get(\"source_configurations\", []) or []:\n",
    "        catalog = source_config.get(\"catalog\") or {}\n",
    "        pg = catalog.get(\"postgres\") or {}\n",
    "        slot_name = (pg.get(\"slot_config\") or {}).get(\"slot_name\")\n",
    "        if slot_name:\n",
    "            slots.append(slot_name)\n",
    "    return slots"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "showTitle": true,
     "title": "PostgreSQL WAL inspection helpers"
    },
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "\n",
    "\n",
    "def pg_connect(inst: MonitoredInstance):\n",
    "    # Raises on failure so one unreachable database is captured in the report and the other\n",
    "    # databases are still monitored. connect_timeout caps how long we wait to establish the\n",
    "    # TCP/auth connection; past it psycopg2 raises and we surface a clear \"timed out\" message.\n",
    "    user = get_secret(inst.secret_scope, inst.user_secret_key)\n",
    "    password = get_secret(inst.secret_scope, inst.password_secret_key)\n",
    "    target = f\"{inst.host}:{inst.port}/{inst.dbname}\"\n",
    "    try:\n",
    "        return psycopg2.connect(host=inst.host, port=inst.port, user=user, password=password,\n",
    "                                dbname=inst.dbname, sslmode=\"require\",\n",
    "                                connect_timeout=CONNECT_TIMEOUT_SECS)\n",
    "    except psycopg2.Error as e:\n",
    "        msg = str(e).lower()\n",
    "        if \"timeout\" in msg or \"timed out\" in msg:\n",
    "            raise RuntimeError(\n",
    "                f\"timed out while connecting to database {target} after \"\n",
    "                f\"{CONNECT_TIMEOUT_SECS} seconds. Check that the database is reachable from \"\n",
    "                f\"this workspace (network/security-group rules) and that the host and port \"\n",
    "                f\"are correct.\")\n",
    "        raise RuntimeError(f\"could not connect to database {target}: {e}\")\n",
    "\n",
    "\n",
    "def is_aws_postgres(conn) -> bool:\n",
    "    \"\"\"AWS RDS/Aurora expose rds_* roles / rds.extensions. Best-effort detection.\"\"\"\n",
    "    with conn.cursor() as cur:\n",
    "        try:\n",
    "            cur.execute(\"SELECT 1 FROM pg_roles WHERE rolname = 'rds_superuser' LIMIT 1;\")\n",
    "            if cur.fetchone():\n",
    "                return True\n",
    "        except psycopg2.Error:\n",
    "            conn.rollback()\n",
    "        try:\n",
    "            cur.execute(\"SELECT current_setting('rds.extensions', true);\")\n",
    "            val = cur.fetchone()\n",
    "            if val and val[0]:\n",
    "                return True\n",
    "        except psycopg2.Error:\n",
    "            conn.rollback()\n",
    "    return False\n",
    "\n",
    "\n",
    "def db_max_slot_wal_keep_size_bytes(conn) -> int:\n",
    "    \"\"\"Return max_slot_wal_keep_size in bytes, or MAX_SLOT_WAL_KEEP_SIZE_UNSET (-1) if unset.\n",
    "\n",
    "    Runs `SHOW max_slot_wal_keep_size`, which returns a formatted string such as '-1',\n",
    "    '8192kB', '512MB' or '10GB', and converts it to bytes. Postgres 13+ only.\n",
    "    \"\"\"\n",
    "    with conn.cursor() as cur:\n",
    "        try:\n",
    "            cur.execute(\"SHOW max_slot_wal_keep_size;\")\n",
    "            row = cur.fetchone()\n",
    "        except psycopg2.Error as e:\n",
    "            conn.rollback()\n",
    "            logger.warning(\"Could not read max_slot_wal_keep_size (PG < 13?): %s\", e)\n",
    "            return MAX_SLOT_WAL_KEEP_SIZE_UNSET\n",
    "    if not row or row[0] is None:\n",
    "        return MAX_SLOT_WAL_KEEP_SIZE_UNSET\n",
    "\n",
    "    value = str(row[0]).strip()\n",
    "    # 0 or a negative value means unlimited/unset: treat both as UNSET so a 0-byte limit is\n",
    "    # never enforced (which would otherwise flag/drop essentially every non-empty slot).\n",
    "    try:\n",
    "        if int(value) <= 0:\n",
    "            return MAX_SLOT_WAL_KEEP_SIZE_UNSET\n",
    "    except ValueError:\n",
    "        pass  # has a unit suffix; parsed below\n",
    "\n",
    "    # PostgreSQL emits binary units kB/MB/GB/TB (powers of 1024); a bare number is bytes.\n",
    "    units = {\"B\": 1, \"KB\": 1024, \"MB\": 1024**2, \"GB\": 1024**3, \"TB\": 1024**4}\n",
    "    upper = value.upper()\n",
    "    for suffix in (\"TB\", \"GB\", \"MB\", \"KB\", \"B\"):\n",
    "        if upper.endswith(suffix):\n",
    "            num = upper[: -len(suffix)].strip()\n",
    "            try:\n",
    "                return int(float(num) * units[suffix])\n",
    "            except ValueError:\n",
    "                logger.warning(\"Could not parse max_slot_wal_keep_size value %r.\", value)\n",
    "                return MAX_SLOT_WAL_KEEP_SIZE_UNSET\n",
    "    try:\n",
    "        return int(value)  # bare number = bytes\n",
    "    except ValueError:\n",
    "        logger.warning(\"Could not parse max_slot_wal_keep_size value %r.\", value)\n",
    "        return MAX_SLOT_WAL_KEEP_SIZE_UNSET\n",
    "\n",
    "\n",
    "def fetch_slot_wal(conn, slot_name: str):\n",
    "    \"\"\"Return slot info as a dict, or {\"exists\": False} if the slot is not present.\n",
    "\n",
    "    Keys: exists, active, active_pid, retained_bytes, restart_lsn, confirmed_flush_lsn.\n",
    "    retained_bytes = pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn). restart_lsn and\n",
    "    confirmed_flush_lsn are surfaced (and logged) so the slot's WAL position is recorded for\n",
    "    debugging / post-drop audit.\n",
    "    \"\"\"\n",
    "    with conn.cursor() as cur:\n",
    "        cur.execute(\n",
    "            \"\"\"\n",
    "            SELECT active,\n",
    "                   active_pid,\n",
    "                   restart_lsn,\n",
    "                   confirmed_flush_lsn,\n",
    "                   pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes\n",
    "            FROM pg_replication_slots\n",
    "            WHERE slot_name = %s\n",
    "            \"\"\",\n",
    "            (slot_name,),\n",
    "        )\n",
    "        row = cur.fetchone()\n",
    "    if not row:\n",
    "        return {\"exists\": False}\n",
    "    active, active_pid, restart_lsn, confirmed_flush_lsn, retained = row\n",
    "    return {\n",
    "        \"exists\": True,\n",
    "        \"active\": bool(active),\n",
    "        \"active_pid\": int(active_pid) if active_pid is not None else None,\n",
    "        \"retained_bytes\": int(retained) if retained is not None else None,\n",
    "        \"restart_lsn\": str(restart_lsn) if restart_lsn is not None else None,\n",
    "        \"confirmed_flush_lsn\": (str(confirmed_flush_lsn)\n",
    "                                if confirmed_flush_lsn is not None else None),\n",
    "    }\n",
    "\n",
    "\n",
    "def terminate_backend(conn, pid: int):\n",
    "    \"\"\"Terminate the PostgreSQL backend connection holding a slot, so the slot can be dropped.\"\"\"\n",
    "    with conn.cursor() as cur:\n",
    "        cur.execute(\"SELECT pg_terminate_backend(%s);\", (pid,))\n",
    "    conn.commit()\n",
    "\n",
    "\n",
    "def _is_slot_active_error(e) -> bool:\n",
    "    return \"is active for PID\" in str(e)\n",
    "\n",
    "\n",
    "def _pid_from_active_error(e):\n",
    "    \"\"\"Extract the PID from a 'replication slot ... is active for PID <n>' error, or None.\"\"\"\n",
    "    msg = str(e)\n",
    "    marker = \"is active for PID \"\n",
    "    i = msg.find(marker)\n",
    "    if i == -1:\n",
    "        return None\n",
    "    digits = \"\"\n",
    "    for ch in msg[i + len(marker):]:\n",
    "        if ch.isdigit():\n",
    "            digits += ch\n",
    "        else:\n",
    "            break\n",
    "    return int(digits) if digits else None\n",
    "\n",
    "\n",
    "def drop_slot(conn, slot_name: str, active_pid=None):\n",
    "    \"\"\"Drop a replication slot, terminating the consumer connection first if one is attached.\n",
    "\n",
    "    Terminates the known backend, then tries to drop; if the\n",
    "    drop reports the slot is still active, terminate the (possibly new) PID from the error and\n",
    "    retry, up to MAX_DROP_ATTEMPTS. Raises if it still cannot be dropped.\n",
    "    \"\"\"\n",
    "    terminated_pids = set()\n",
    "    if active_pid is not None:\n",
    "        terminate_backend(conn, active_pid)\n",
    "        terminated_pids.add(active_pid)\n",
    "\n",
    "    last_error = None\n",
    "    for attempt in range(1, MAX_DROP_ATTEMPTS + 1):\n",
    "        try:\n",
    "            with conn.cursor() as cur:\n",
    "                cur.execute(\"SELECT pg_drop_replication_slot(%s);\", (slot_name,))\n",
    "            conn.commit()\n",
    "            return\n",
    "        except psycopg2.Error as e:\n",
    "            conn.rollback()\n",
    "            last_error = e\n",
    "            if not _is_slot_active_error(e):\n",
    "                raise\n",
    "            # A consumer is (still/again) attached. Terminate that PID and retry.\n",
    "            pid = _pid_from_active_error(e)\n",
    "            if pid is not None and pid not in terminated_pids:\n",
    "                logger.warning(\"Slot %s still in use by PID %s; terminating and retrying \"\n",
    "                               \"(attempt %d/%d).\", slot_name, pid, attempt, MAX_DROP_ATTEMPTS)\n",
    "                terminate_backend(conn, pid)\n",
    "                terminated_pids.add(pid)\n",
    "            if attempt < MAX_DROP_ATTEMPTS:\n",
    "                time.sleep(DROP_RETRY_DELAY_SECS)\n",
    "    raise last_error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "showTitle": true,
     "title": "Report collector"
    },
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "# Every instance, pipeline and slot is processed independently: any failure is captured as a\n",
    "# report entry rather than stopping the run, so a problem with one database can never prevent\n",
    "# the others from being monitored. At the end we print a full report and fail the notebook\n",
    "# only if the report contains anything that needs your attention.\n",
    "def fmt_bytes(n) -> str:\n",
    "    if n is None:\n",
    "        return \"n/a\"\n",
    "    for unit in (\"B\", \"KB\", \"MB\", \"GB\", \"TB\"):\n",
    "        if abs(n) < 1024 or unit == \"TB\":\n",
    "            return f\"{n:.2f}{unit}\" if unit != \"B\" else f\"{int(n)}B\"\n",
    "        n /= 1024.0\n",
    "\n",
    "\n",
    "# Per-slot results (always informational; some statuses also add an attention item below).\n",
    "slot_rows = []            # dicts: database, pipeline_id, slot, status, retained, limit, detail\n",
    "# Things that require your attention -> the notebook fails at the end if this is non-empty.\n",
    "attention = []            # dicts: scope, detail\n",
    "# Actions actually performed this run (for the success report).\n",
    "actions_taken = []        # human-readable strings\n",
    "\n",
    "\n",
    "def add_slot_row(database, pipeline_id, slot, status, retained, limit, detail=\"\"):\n",
    "    slot_rows.append({\"database\": database, \"pipeline_id\": pipeline_id, \"slot\": slot,\n",
    "                      \"status\": status, \"retained\": retained, \"limit\": limit, \"detail\": detail})\n",
    "\n",
    "\n",
    "def add_attention(scope, detail):\n",
    "    attention.append({\"scope\": scope, \"detail\": detail})\n",
    "    logger.warning(\"ATTENTION [%s]: %s\", scope, detail)\n",
    "\n",
    "\n",
    "def resolve_threshold(inst, db_threshold, aws):\n",
    "    \"\"\"Return (limit_bytes, source) for an instance, or (None, reason) if none can be found.\n",
    "\n",
    "    Precedence: the value you set in monitored_instances wins; otherwise the database's own\n",
    "    max_slot_wal_keep_size is used. If neither is available, no limit can be enforced.\n",
    "    \"\"\"\n",
    "    if inst.max_slot_wal_keep_size is not None:\n",
    "        src = \"max_slot_wal_keep_size from monitored_instances\"\n",
    "        if db_threshold != MAX_SLOT_WAL_KEEP_SIZE_UNSET \\\n",
    "                and db_threshold != inst.max_slot_wal_keep_size:\n",
    "            # Note both values so it is never a surprise which limit is in force. This does\n",
    "            # not change any setting on the database; it only selects the limit this notebook\n",
    "            # enforces.\n",
    "            src += (f\" (the database's own max_slot_wal_keep_size is {fmt_bytes(db_threshold)}; \"\n",
    "                    f\"this notebook uses the value from monitored_instances)\")\n",
    "        return inst.max_slot_wal_keep_size, src\n",
    "    if db_threshold != MAX_SLOT_WAL_KEEP_SIZE_UNSET:\n",
    "        return db_threshold, \"database max_slot_wal_keep_size\"\n",
    "    # Neither source has a limit: no value was provided in monitored_instances and the source\n",
    "    # database reports max_slot_wal_keep_size = -1 (unlimited). We cannot determine a limit.\n",
    "    # Only AWS RDS/Aurora is offered the \"update the database\" option (its parameter group is\n",
    "    # customer-managed); for other databases we only offer providing it in monitored_instances.\n",
    "    if aws:\n",
    "        reason = (\n",
    "            \"cannot determine the WAL size limit: no max_slot_wal_keep_size was provided for \"\n",
    "            \"this database in monitored_instances, and the source database (AWS RDS/Aurora) \"\n",
    "            \"reports max_slot_wal_keep_size = -1 (unlimited). Either provide \"\n",
    "            \"max_slot_wal_keep_size for this database in monitored_instances, or set \"\n",
    "            \"max_slot_wal_keep_size on the source database via its RDS/Aurora parameter group.\")\n",
    "    else:\n",
    "        reason = (\n",
    "            \"cannot determine the WAL size limit: no max_slot_wal_keep_size was provided for \"\n",
    "            \"this database in monitored_instances, and the source database reports \"\n",
    "            \"max_slot_wal_keep_size = -1 (unlimited). Provide max_slot_wal_keep_size for this \"\n",
    "            \"database in monitored_instances.\")\n",
    "    return None, reason"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "showTitle": true,
     "title": "Check each listed pipeline is supported and its slots match"
    },
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "# Independent per pipeline. A problem here is recorded for the final report; the slots you\n",
    "# listed are still monitored regardless (that is the whole point of listing them).\n",
    "for inst in monitored_instances:\n",
    "    for p in inst.pipelines:\n",
    "        scope = f\"pipeline {p.pipeline_id}\"\n",
    "        try:\n",
    "            info = fetch_pipeline(p.pipeline_id)\n",
    "            error_code = info.get(\"error_code\")\n",
    "            if error_code == \"RESOURCE_DOES_NOT_EXIST\":\n",
    "                # A removed pipeline can't be inspected; its slots are exactly the ones we want\n",
    "                # to clean up, so we keep monitoring the slot names you listed. Not an error.\n",
    "                logger.info(\"Pipeline %s no longer exists; monitoring the slot names you listed.\",\n",
    "                            p.pipeline_id)\n",
    "                continue\n",
    "            if error_code:\n",
    "                add_attention(scope, f\"could not be read from Databricks: {info}\")\n",
    "                continue\n",
    "\n",
    "            spec = info.get(\"spec\", {})\n",
    "            idef = _ingestion_definition(spec)\n",
    "            if is_two_pipeline_mode(idef):\n",
    "                add_attention(scope, \"uses a separate ingestion gateway, which this notebook \"\n",
    "                                     \"does not support. Remove it from monitored_instances.\")\n",
    "                continue\n",
    "            if not is_direct_cdc_postgres(spec):\n",
    "                add_attention(scope, \"is not a PostgreSQL ingestion pipeline that connects \"\n",
    "                                     \"directly to the database. Remove it from monitored_instances.\")\n",
    "                continue\n",
    "\n",
    "            live_slots = set(slots_from_ingestion_definition(idef))\n",
    "            provided_slots = set(p.slots)\n",
    "            if live_slots != provided_slots:\n",
    "                missing = sorted(live_slots - provided_slots)  # used by pipeline, not listed\n",
    "                extra = sorted(provided_slots - live_slots)     # listed, not used by pipeline\n",
    "                add_attention(scope,\n",
    "                    \"the replication slots you listed do not match the slots the pipeline is \"\n",
    "                    \"actually using (usually after a source catalog was added or removed). \"\n",
    "                    f\"Add to 'slots': {missing}. Remove from 'slots': {extra}.\")\n",
    "            else:\n",
    "                logger.info(\"Pipeline %s: supported; %d slot(s) match.\",\n",
    "                            p.pipeline_id, len(provided_slots))\n",
    "        except Exception as e:\n",
    "            add_attention(scope, f\"could not be checked: {e}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "showTitle": true,
     "title": "Check that every PostgreSQL ingestion pipeline is listed"
    },
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "# Reported (not failed immediately) so that a newly-created, unlisted pipeline does not stop\n",
    "# the listed databases from being monitored. Missed pipelines are collected and reported as a\n",
    "# single attention item so it is clear which pipelines were left out. Their slots are NOT\n",
    "# monitored this run (we have no connection details for them) -- they are only monitored once\n",
    "# added to monitored_instances. A listing failure is likewise only reported.\n",
    "missed_pipelines = []\n",
    "try:\n",
    "    for status in list_managed_ingestion_pipelines():\n",
    "        pid = status.get(\"pipeline_id\")\n",
    "        if not pid or pid in all_monitored_pipeline_ids:\n",
    "            continue\n",
    "        try:\n",
    "            info = fetch_pipeline(pid)\n",
    "            error_code = info.get(\"error_code\")\n",
    "            if error_code == \"RESOURCE_DOES_NOT_EXIST\":\n",
    "                # Pipeline vanished between listing and fetch -> nothing to cover.\n",
    "                continue\n",
    "            if error_code:\n",
    "                # Could not determine the pipeline's type -> surface it rather than silently\n",
    "                # skipping, so a genuinely-unlisted PG pipeline is never hidden by a transient\n",
    "                # fetch error (which would otherwise produce a false ALL CLEAR).\n",
    "                add_attention(f\"pipeline {pid}\",\n",
    "                    f\"could not be read from Databricks during the coverage check, so it could \"\n",
    "                    f\"not be confirmed as listed: {info}\")\n",
    "                continue\n",
    "            if is_direct_cdc_postgres(info.get(\"spec\", {})):\n",
    "                missed_pipelines.append(pid)\n",
    "        except Exception as e:\n",
    "            add_attention(f\"pipeline {pid}\", f\"could not be checked for coverage: {e}\")\n",
    "except Exception as e:\n",
    "    add_attention(\"workspace\", f\"could not list pipelines to check coverage: {e}\")\n",
    "\n",
    "if missed_pipelines:\n",
    "    add_attention(\n",
    "        \"missing pipelines\",\n",
    "        f\"you missed adding {len(missed_pipelines)} PostgreSQL ingestion pipeline(s) to \"\n",
    "        f\"monitored_instances: {sorted(missed_pipelines)}. Their replication slots are NOT \"\n",
    "        f\"being monitored. Add each pipeline under its database (with the database connection \"\n",
    "        f\"details and the pipeline's replication slot names) so its slots are monitored.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "showTitle": true,
     "title": "Monitor WAL for every listed slot and take action"
    },
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "# One connection per database. The whole database is wrapped so an unreachable / misconfigured\n",
    "# database is captured and the others are still monitored. Within a database, each slot is\n",
    "# handled independently too.\n",
    "drops_done = 0  # global across all databases, capped by MAX_DROPS_PER_RUN\n",
    "for inst in monitored_instances:\n",
    "    logger.info(\"=\" * 70)\n",
    "    logger.info(\"Database %s:%d/%s (%d pipeline(s))\",\n",
    "                inst.host, inst.port, inst.dbname, len(inst.pipelines))\n",
    "    conn = None\n",
    "    try:\n",
    "        conn = pg_connect(inst)\n",
    "        # autocommit=True so each statement commits/aborts on its own: one slot's\n",
    "        # query error cannot leave the shared connection in an aborted-transaction\n",
    "        # state and cascade InFailedSqlTransaction to every later slot on this DB.\n",
    "        conn.autocommit = True\n",
    "        aws = is_aws_postgres(conn)\n",
    "        db_threshold = db_max_slot_wal_keep_size_bytes(conn)\n",
    "        limit, src = resolve_threshold(inst, db_threshold, aws)\n",
    "        if limit is None:\n",
    "            # No limit to enforce for this database -> record and skip its slots (still lets\n",
    "            # every other database be monitored). Note them as rows so they appear in the report.\n",
    "            add_attention(f\"database {inst.host}\", src)\n",
    "            for p in inst.pipelines:\n",
    "                for slot_name in p.slots:\n",
    "                    add_slot_row(inst.host, p.pipeline_id, slot_name, \"NO_LIMIT\",\n",
    "                                 None, None, \"no WAL size limit could be determined\")\n",
    "            continue\n",
    "\n",
    "        logger.info(\"WAL size limit for %s: %s (%s)\", inst.host, fmt_bytes(limit), src)\n",
    "\n",
    "        for p in inst.pipelines:\n",
    "            for slot_name in p.slots:\n",
    "                try:\n",
    "                    info = fetch_slot_wal(conn, slot_name)\n",
    "                    if not info[\"exists\"]:\n",
    "                        logger.info(\"  [%s] slot %s: not present (nothing to do).\",\n",
    "                                    p.pipeline_id, slot_name)\n",
    "                        add_slot_row(inst.host, p.pipeline_id, slot_name, \"MISSING\",\n",
    "                                     None, limit, \"slot does not exist in the database\")\n",
    "                        continue\n",
    "\n",
    "                    active = info[\"active\"]\n",
    "                    active_pid = info[\"active_pid\"]\n",
    "                    retained = info[\"retained_bytes\"]\n",
    "                    restart_lsn = info[\"restart_lsn\"]\n",
    "                    confirmed_flush_lsn = info[\"confirmed_flush_lsn\"]\n",
    "                    over = retained is not None and retained > limit\n",
    "                    # Surface the slot's WAL position (restart_lsn / confirmed_flush_lsn) for\n",
    "                    # debugging and as an audit record of where a slot was before any drop.\n",
    "                    logger.info(\"  [%s] slot %s: in_use=%s pid=%s holding=%s limit=%s over=%s \"\n",
    "                                \"restart_lsn=%s confirmed_flush_lsn=%s\",\n",
    "                                p.pipeline_id, slot_name, active, active_pid, fmt_bytes(retained),\n",
    "                                fmt_bytes(limit), over, restart_lsn, confirmed_flush_lsn)\n",
    "\n",
    "                    lsn_detail = (f\" [restart_lsn={restart_lsn}, \"\n",
    "                                  f\"confirmed_flush_lsn={confirmed_flush_lsn}]\")\n",
    "\n",
    "                    if not over:\n",
    "                        add_slot_row(inst.host, p.pipeline_id, slot_name, \"OK\", retained, limit,\n",
    "                                     \"within limit\" + lsn_detail)\n",
    "                        continue\n",
    "\n",
    "                    if action == \"FAIL_ONLY\":\n",
    "                        add_slot_row(inst.host, p.pipeline_id, slot_name, \"OVER_LIMIT\",\n",
    "                                     retained, limit, \"exceeds limit; action is FAIL_ONLY\")\n",
    "                        add_attention(f\"pipeline {p.pipeline_id}, slot {slot_name}\",\n",
    "                            f\"holds {fmt_bytes(retained)} of WAL (limit {fmt_bytes(limit)}). \"\n",
    "                            f\"Action is FAIL_ONLY, so it was not dropped. Re-run with action \"\n",
    "                            f\"DROP_SLOTS to drop it, or drop it in PostgreSQL with \"\n",
    "                            f\"SELECT pg_drop_replication_slot('{slot_name}');\")\n",
    "                    elif drops_done >= MAX_DROPS_PER_RUN:\n",
    "                        # Safety cap reached: do not drop more this run. Report the remaining\n",
    "                        # over-limit slots so the run fails for human review instead of\n",
    "                        # mass-dropping (guards against a too-low limit / WAL spike).\n",
    "                        add_slot_row(inst.host, p.pipeline_id, slot_name, \"OVER_LIMIT\",\n",
    "                                     retained, limit,\n",
    "                                     \"exceeds limit; per-run drop cap reached, not dropped\"\n",
    "                                     + lsn_detail)\n",
    "                        add_attention(f\"pipeline {p.pipeline_id}, slot {slot_name}\",\n",
    "                            f\"holds {fmt_bytes(retained)} of WAL (limit {fmt_bytes(limit)}) but \"\n",
    "                            f\"was NOT dropped because this run already dropped \"\n",
    "                            f\"{MAX_DROPS_PER_RUN} slot(s) (MAX_DROPS_PER_RUN). This many \"\n",
    "                            f\"over-limit slots usually signals a misconfiguration (a too-low \"\n",
    "                            f\"limit or a WAL spike). Review the configuration; if this is \"\n",
    "                            f\"expected, raise MAX_DROPS_PER_RUN and re-run. \"\n",
    "                            f\"restart_lsn={restart_lsn}, confirmed_flush_lsn={confirmed_flush_lsn}.\")\n",
    "                    else:  # DROP_SLOTS, under the cap\n",
    "                        # If a consumer is connected, drop_slot terminates it first (and retries\n",
    "                        # if it reconnects).\n",
    "                        drop_slot(conn, slot_name, active_pid)\n",
    "                        drops_done += 1\n",
    "                        detail = (\"exceeded limit and was dropped\"\n",
    "                                  + (\" (terminated its active connection first)\"\n",
    "                                     if active_pid is not None else \"\") + lsn_detail)\n",
    "                        logger.warning(\"  [%s] slot %s dropped (was holding %s > %s%s; \"\n",
    "                                       \"restart_lsn=%s confirmed_flush_lsn=%s).\",\n",
    "                                       p.pipeline_id, slot_name, fmt_bytes(retained),\n",
    "                                       fmt_bytes(limit),\n",
    "                                       \"; terminated PID %s\" % active_pid if active_pid else \"\",\n",
    "                                       restart_lsn, confirmed_flush_lsn)\n",
    "                        add_slot_row(inst.host, p.pipeline_id, slot_name, \"DROPPED\",\n",
    "                                     retained, limit, detail)\n",
    "                        actions_taken.append(\n",
    "                            f\"Dropped slot '{slot_name}' (pipeline {p.pipeline_id}, database \"\n",
    "                            f\"{inst.host}) holding {fmt_bytes(retained)} of WAL at \"\n",
    "                            f\"restart_lsn={restart_lsn}, confirmed_flush_lsn={confirmed_flush_lsn}\"\n",
    "                            + (f\"; terminated its active connection (PID {active_pid}) first.\"\n",
    "                               if active_pid is not None else \".\"))\n",
    "                except Exception as e:\n",
    "                    add_slot_row(inst.host, p.pipeline_id, slot_name, \"ERROR\", None, limit, str(e))\n",
    "                    add_attention(f\"pipeline {p.pipeline_id}, slot {slot_name}\",\n",
    "                                  f\"could not be checked or dropped: {e}\")\n",
    "    except Exception as e:\n",
    "        # Instance-level failure (e.g. cannot connect): record and move to the next database.\n",
    "        add_attention(f\"database {inst.host}\", str(e))\n",
    "        for p in inst.pipelines:\n",
    "            for slot_name in p.slots:\n",
    "                add_slot_row(inst.host, p.pipeline_id, slot_name, \"NOT_CHECKED\", None, None,\n",
    "                             \"database could not be processed (see attention items)\")\n",
    "    finally:\n",
    "        if conn is not None:\n",
    "            conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "showTitle": true,
     "title": "Report and exit"
    },
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "# The report is printed (not logged) so it reads cleanly without per-line timestamp/level\n",
    "# prefixes. Three sections: a summary, the per-slot table, and — if anything needs you to\n",
    "# act — a numbered \"ACTION REQUIRED\" list. The notebook fails at the end only if there are\n",
    "# action-required items.\n",
    "import textwrap as _tw\n",
    "\n",
    "_WIDTH = 96\n",
    "_BAR = \"=\" * _WIDTH\n",
    "_DASH = \"-\" * _WIDTH\n",
    "\n",
    "# Human-friendly labels + markers for each internal status.\n",
    "_STATUS_LABEL = {\n",
    "    \"OK\": \"OK  within limit\",\n",
    "    \"MISSING\": \"-   not present\",\n",
    "    \"DROPPED\": \"DROPPED\",\n",
    "    \"OVER_LIMIT\": \"OVER LIMIT\",\n",
    "    \"NO_LIMIT\": \"no limit set\",\n",
    "    \"NOT_CHECKED\": \"not checked\",\n",
    "    \"ERROR\": \"ERROR\",\n",
    "}\n",
    "\n",
    "\n",
    "def _p(line=\"\"):\n",
    "    print(line)\n",
    "\n",
    "\n",
    "def _section(title):\n",
    "    _p()\n",
    "    _p(_BAR)\n",
    "    _p(f\"  {title}\")\n",
    "    _p(_BAR)\n",
    "\n",
    "\n",
    "def _wrap_into(prefix, text, width=_WIDTH):\n",
    "    \"\"\"Wrap text under a hanging indent aligned to len(prefix).\"\"\"\n",
    "    indent = \" \" * len(prefix)\n",
    "    wrapped = _tw.wrap(text, width=width - len(prefix)) or [\"\"]\n",
    "    out = [prefix + wrapped[0]]\n",
    "    out += [indent + w for w in wrapped[1:]]\n",
    "    return \"\\n\".join(out)\n",
    "\n",
    "\n",
    "_dropped = [r for r in slot_rows if r[\"status\"] == \"DROPPED\"]\n",
    "_within = [r for r in slot_rows if r[\"status\"] in (\"OK\", \"MISSING\")]\n",
    "\n",
    "# ---- Header ----\n",
    "_p()\n",
    "_p(_BAR)\n",
    "_p(\"  PostgreSQL Replication Slot Monitoring & Cleanup\")\n",
    "_p(f\"  Result: {'ACTION REQUIRED' if attention else 'ALL CLEAR'}\"\n",
    "   f\"   |   Mode: {action}   |   Slots checked: {len(slot_rows)}\")\n",
    "_p(_BAR)\n",
    "\n",
    "# ---- Summary counts ----\n",
    "_p()\n",
    "_p(\"SUMMARY\")\n",
    "_p(f\"    Slots checked ....... {len(slot_rows)}\")\n",
    "_p(f\"    Within limit ........ {len(_within)}\")\n",
    "_p(f\"    Dropped ............. {len(_dropped)}\")\n",
    "_p(f\"    Need attention ...... {len(attention)}\")\n",
    "\n",
    "# ---- Per-slot detail table ----\n",
    "_p()\n",
    "_p(\"SLOT DETAILS\")\n",
    "_hdr = f\"    {'DATABASE':<34}{'PIPELINE':<26}{'SLOT':<14}{'HOLDING':>10}{'LIMIT':>10}\"\n",
    "_p(_hdr)\n",
    "_p(\"    \" + \"-\" * (len(_hdr) - 4))\n",
    "if slot_rows:\n",
    "    for r in slot_rows:\n",
    "        _db = (r[\"database\"][:31] + \"...\") if len(r[\"database\"]) > 34 else r[\"database\"]\n",
    "        _pid = (r[\"pipeline_id\"][:23] + \"...\") if len(r[\"pipeline_id\"]) > 26 else r[\"pipeline_id\"]\n",
    "        _p(f\"    {_db:<34}{_pid:<26}{r['slot']:<14}\"\n",
    "           f\"{fmt_bytes(r['retained']):>10}{fmt_bytes(r['limit']):>10}\")\n",
    "        _p(f\"        -> {_STATUS_LABEL.get(r['status'], r['status'])}: {r['detail']}\")\n",
    "else:\n",
    "    _p(\"    (no slots were checked)\")\n",
    "\n",
    "# ---- Actions taken ----\n",
    "_p()\n",
    "_p(\"ACTIONS TAKEN\")\n",
    "if actions_taken:\n",
    "    for a in actions_taken:\n",
    "        _p(_wrap_into(\"    - \", a))\n",
    "else:\n",
    "    _p(\"    - No changes were made (no slots needed to be dropped).\")\n",
    "\n",
    "# ---- Action required (fail the notebook if any) ----\n",
    "if attention:\n",
    "    _p()\n",
    "    _p(_BAR)\n",
    "    _p(f\"  ACTION REQUIRED — {len(attention)} item(s) need your attention\")\n",
    "    _p(_BAR)\n",
    "    for i, item in enumerate(attention, 1):\n",
    "        _p()\n",
    "        _p(_wrap_into(f\"  {i}. \", f\"[{item['scope']}] {item['detail']}\"))\n",
    "    _p()\n",
    "    _p(_BAR)\n",
    "    # Fail so the scheduled job's failure notification alerts you. Keep the exception itself\n",
    "    # short and point to the full, formatted report printed by this (the last) cell.\n",
    "    _fail(\n",
    "        f\"PostgreSQL replication slot check found {len(attention)} item(s) that need your \"\n",
    "        f\"attention. See the 'ACTION REQUIRED' section in the output of the last cell of this \"\n",
    "        f\"notebook for the details and the exact steps to fix each one.\"\n",
    "    )\n",
    "else:\n",
    "    _p()\n",
    "    _p(_BAR)\n",
    "    _p(\"  ALL CLEAR — every listed slot is within its WAL size limit and every PostgreSQL\")\n",
    "    _p(\"  ingestion pipeline is listed. No action required.\")\n",
    "    _p(_BAR)"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "computePreferences": null,
   "dashboards": [],
   "environmentMetadata": {
    "base_environment": "",
    "environment_version": "5"
   },
   "inputWidgetPreferences": null,
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 4
   },
   "notebookName": "PG Slot Monitoring and Cleanup",
   "widgets": {}
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
