PostgreSQL MCP: Manage Postgres From Your AI Assistant

airflow-pg — PostgreSQL 18.6, plan developer-1 (1 CPU, 1GB RAM, 8GB disk), running in do-fra (DigitalOcean Frankfurt). Cost is $0.007/hour.

Status: RUNNING, single node ("airflow-pg-4"), created Sep 3, 2026. No termination protection, no VPC, no service integrations attached. Only database is defaultdb, single user avnadmin. IP filter is wide open (0.0.0.0/0 and ::/0). 4 backups on file, most recent from today (Sep 24). Maintenance window is Saturdays at 19:40.

The defaultdb database has one schema, public, with 71 tables — this is a standard Apache Airflow metadata database. The tables fall into a few groups:

DAG/scheduling core: dag, dag_run, dag_code, dag_tag, dag_version, dag_bundle, dag_owner_attributes, dag_warning, dag_priority_parsing_request, dag_favorite, serialized_dag

Nothing custom here — it's the vanilla schema Airflow creates on startup (the asset/hitl_detail/deadline tables suggest a fairly recent Airflow 3.x version).

There are only 3 DAG runs total in this database, all for the same DAG:

example_astronauts (most recent first):

• Manual run, 2026-09-17T15:00:07Z → 15:00:20Z (13s) — success
• Manual run, 2026-09-17T14:25:42Z → 14:26:13Z (31s) — success
• Scheduled run (logical date 2026-09-17), 14:25:42Z → 14:25:55Z (13s) — success
  All three succeeded, and everything happened within about 35 minutes on Sep 17. Nothing since — consistent with the mostly-idle picture we've seen elsewhere (empty ab_user, no vacuum activity, minimal WAL).

Nothing alarming — mostly routine internal noise. The 100 most recent entries (last ~30 seconds, all Sep 24) are just Aiven's own management agent connecting every couple seconds to run health checks (user=_aiven/_avnadmin_monitor, app=management-agent-v2), each connecting and disconnecting in milliseconds. That's expected background activity, not anything you or Airflow generated.

The one recurring item worth flagging: every single one of these connections logs a collation version mismatch warning on the _aiven database — "created using collation version 2.41, but the operating system provides version 2.42." This is a known benign-but-noisy Postgres warning (glibc got upgraded under the hood after the database was created) and isn't causing errors, but it's cluttering the logs on every connection. If you want it silenced, you'd run ALTER DATABASE _aiven REFRESH COLLATION VERSION — though note this is Aiven's internal _aiven monitoring database, not your defaultdb, so it's low priority.

No err/crit/emerg entries, no failed logins, no unusual client IPs — just the standard management/monitoring chatter. Want me to check further back (log retention is 4 days) or filter specifically for warning and above?

WAL looks healthy overall, with one thing worth watching:

Current WAL file is 00000004000000000000006C, wal_level is logical (needed for Aiven's internal replication/migration tooling), and archive_mode is on — all normal for an Aiven-managed service. Since the last stats reset (today, 14:01:59 — likely from a recent restart/config change), only 35 WAL records and ~119KB written, so activity is minimal, consistent with a mostly-idle database.

The one thing to note: there's an inactive physical replication slot (25832665) sitting well behind the current WAL position (restart_lsn 0/25000028 vs current 0/6C000000) with wal_status: extended, meaning Postgres is retaining WAL segments for it beyond what would normally be recycled. This is standard for Aiven-managed single-node services (it's typically a slot reserved for Aiven's internal tooling — backups/forking — not a customer replica), and since max_slot_wal_keep_size is unlimited (-1), it won't be dropped for being too far behind. It's not causing a problem now given the low WAL volume, but if write activity increased substantially, an inactive slot with unlimited retention is the classic cause of disk filling up with WAL. Given this service is nearly idle, I wouldn't call it a current problem — just something to keep an eye on if usage grows.

No excessive checkpoint pressure, no replication lag warnings, no err+ severity log entries related to WAL. Overall: WAL is behaving as expected for a lightly used instance.

Current connections are already at 18 — over the 15 that metadata.max_connections reported, so you're likely relying on Aiven's reserved/superuser connection slots already. That's the biggest flag here. A few things worth changing:

Connection handling — the main one. Airflow's webserver, scheduler, and each worker/executor process open their own DB connections, so it's easy to blow past a small max_connections limit as DAGs scale. On developer-1 you're capped low, and pgbouncer is currently disabled (pgbouncer: false in the service features). I'd enable Aiven's PgBouncer connection pooling and point Airflow's sql_alchemy_conn at the pooled endpoint rather than the direct one — this is the standard fix for Airflow's "too many connections" problem, since it lets many logical Airflow connections share a small number of real backend connections.

Not worth touching right now: WAL settings, autovacuum tuning, work_mem, etc. — the tables are essentially empty (0 vacuum/analyze activity, minimal WAL volume), so there's no evidence of load that would justify tuning those yet. That's a "revisit once there's real DAG traffic" set of knobs, not a now problem.

Want me to walk through enabling PgBouncer via aiven_pg_bouncer_create, or check current plan pricing for a Business-tier upgrade?

Sure — here's what backs each point, from the docs search above plus what we directly observed on the service:

PgBouncer / connection pooling — Aiven's connection-limits docs note max_connections scales by plan tier (25–60000 via the pg.max_connections parameter, changing it requires a restart). The observation that you're at 18 connections against a 15-limit metadata value is from your own aiven_service_get output and pg_stat_activity query above, not the docs — worth double-checking in the Aiven console since a metadata mismatch like that deserves verification rather than taking my number at face value.

• Connection limits per plan for Aiven for PostgreSQL®

Caveat on all of this: I explicitly asked Aiven's docs search for Airflow-specific tuning guidance and it came back empty — there's no Aiven doc that says "configure PostgreSQL this way for Airflow." The PgBouncer-for-Airflow recommendation and the "developer plan is too small for real DAG load" point are my own inference from how Airflow's connection pooling generally works, not something sourced from Aiven's documentation. Treat those two as reasoned judgment rather than a documented recommendation, and the WAL/autovacuum/backup-retention observations were pulled directly from your service's live config and stats, not from docs at all.

This will provision a new billable Kafka service — airflow-kafka, plan developer-2-1d, cloud do-fra, in project tibs-examples.

Topic created. Now producing the 12 astronaut records from the most recent example_astronauts DAG run.

Done — all 12 astronauts (from the most recent example_astronauts DAG run, pulled from the xcom table's return_value for the get_astronauts task) are now in the astronaut topic on airflow-kafka, offsets 0–11. Nine are on the ISS, three on Tiangong.

I used Aiven's Kafka REST API (Karapace) rather than a native Kafka producer client. Specifically:

I called aiven_kafka_topic_message_produce with format: "json" and a records array containing each astronaut's {name, craft} as a value (no key set, so messages went round-robin/default-partitioning, all landing in partition 0 since the topic only has 1 partition). That tool sends a single HTTP POST to the Kafka REST endpoint (.../kafka/rest/topics/astronaut/produce), which Karapace translates into actual Kafka protocol writes on your behalf.

One thing worth flagging now: each new row's value is the whole list of 12 astronauts as one JSON blob (that's how the DAG stores it), not one row per astronaut — so this connector will stream one message per DAG run (containing all current astronauts), not incremental per-astronaut diffs. Let me know if that's what you want, or if you'd rather I explore unpacking the JSON array into individual messages some other way.

Topic created. Before I create the connector, here's the plan — please confirm since this stands up ongoing infrastructure:

Connector: JDBC Source (io.aiven.connect.jdbc.JdbcSourceConnector) on airflow-kafka-connect, reading from airflow-pg (credentials auto-resolved, no manual password handling), writing to topic individual-astronaut-observations.

How it gets one message per astronaut: rather than reading the xcom table row-for-row (which would give one message per DAG run, containing all astronauts as a JSON blob), I'll use a custom query that unnests the JSON array in Postgres itself:

Loading code...

Kafka Connect tracks new rows using ts (the timestamp column) in incremental "timestamp" mode — each poll only picks up rows from DAG runs newer than the last one seen, and each astronaut in that run becomes its own Kafka message.

Confirm and I'll create it?

The connector astronaut-observations-source is created and RUNNING on airflow-kafka-connect, task 0 running cleanly.

It polls airflow-pg every 60 seconds, picking up any new example_astronauts DAG run from the xcom table and emitting one Kafka message per astronaut into individual-astronaut-observations. It should backfill the 12 astronauts from the existing run shortly (since ts starts from the earliest recorded value).

The last two messages (offsets 70 and 71) in individual-astronaut-observations:

Loading code...

Good news: the pipeline is genuinely live — these are from a scheduled DAG run that happened today (2026-09-24), not the old Sep 17 backfill, so the connector is picking up new runs automatically as intended.