aidp-postgresql

name	aidp-postgresql
description	Read or write PostgreSQL from an AIDP notebook via the AIDP `aidataplatform` Spark format handler. Use when the user mentions PostgreSQL, Postgres, "psql", or has a Postgres host/port to connect to. Prefer the official `POSTGRESQL` connector sample; use native Spark JDBC only as an SSL-required fallback when the built-in connector cannot express sslmode=require.
allowed-tools	Read, Write, Edit, Bash

aidp-postgresql — PostgreSQL via AIDP aidataplatform

When to use

• User wants to read or write a PostgreSQL database from an AIDP notebook.
• Mentioned: "PostgreSQL", "Postgres", "psql".

When NOT to use

• For MySQL / HeatWave → aidp-mysql.
• For SQL Server → aidp-sqlserver.
• For arbitrary JDBC-only DBs → aidp-jdbc-custom.

Read

Option A: AIDP aidataplatform format (default)

Use the official AIDP PostgreSQL connector sample first.

import os
from oracle_ai_data_platform_connectors.aidataplatform import (
    AIDP_FORMAT, aidataplatform_options,
)

opts = aidataplatform_options(
    type="POSTGRESQL",
    host=os.environ["PG_HOST"],
    port=int(os.environ.get("PG_PORT", "5432")),
    user=os.environ["PG_USER"],
    password=os.environ["PG_PASSWORD"],
    schema=os.environ.get("PG_SCHEMA", "public"),
    table=os.environ["PG_TABLE"],
)
df = spark.read.format(AIDP_FORMAT).options(**opts).load()
df.show()

Write

opts = aidataplatform_options(
    type="POSTGRESQL",
    host=os.environ["PG_HOST"],
    port=int(os.environ.get("PG_PORT", "5432")),
    user=os.environ["PG_USER"],
    password=os.environ["PG_PASSWORD"],
    schema=os.environ.get("PG_SCHEMA", "public"),
    table=os.environ["PG_TARGET_TABLE"],
    extra={"write.mode": "CREATE"},   # CREATE | APPEND | OVERWRITE
)
df.write.format(AIDP_FORMAT).options(**opts).save()

Pushdown SQL

opts = aidataplatform_options(
    type="POSTGRESQL",
    host=os.environ["PG_HOST"],
    port=int(os.environ.get("PG_PORT", "5432")),
    user=os.environ["PG_USER"],
    password=os.environ["PG_PASSWORD"],
    extra={
        "pushdown.sql": (
            f"SELECT * FROM {os.environ.get('PG_SCHEMA', 'public')}.{os.environ['PG_TABLE']} "
            "WHERE updated_at >= DATE '2024-01-01'"
        ),
    },
)
df = spark.read.format(AIDP_FORMAT).options(**opts).load()
df.show()

Option B: Native Spark JDBC fallback for SSL-required endpoints

Use this only when the target PostgreSQL service rejects non-TLS connections and the AIDP connector cannot pass the required SSL option. The observed failure mode is [PostgreSQL]connection is insecure (try using sslmode=require). In that case, use Spark native JDBC with URL-embedded sslmode=require.

The cluster may not have org.postgresql.Driver pre-installed, so runtime-load it the same way aidp-jdbc-custom does.

import os, urllib.request
from py4j.java_gateway import java_import

JAR_PATH = "/tmp/postgresql-42.7.4.jar"
if not os.path.exists(JAR_PATH):
    urllib.request.urlretrieve(
        "https://repo1.maven.org/maven2/org/postgresql/postgresql/42.7.4/postgresql-42.7.4.jar",
        JAR_PATH,
    )

# Register driver on driver JVM
gw = spark._sc._gateway
url = spark._jvm.java.io.File(JAR_PATH).toURI().toURL()
arr = gw.new_array(spark._jvm.java.net.URL, 1); arr[0] = url
ucl = spark._jvm.java.net.URLClassLoader(arr, spark._jvm.java.lang.ClassLoader.getSystemClassLoader())
spark._jvm.java.lang.Thread.currentThread().setContextClassLoader(ucl)
DriverCls = spark._jvm.java.lang.Class.forName("org.postgresql.Driver", True, ucl)
spark._jvm.java.sql.DriverManager.registerDriver(DriverCls.newInstance())
spark._jsc.addJar(JAR_PATH)  # distribute to executors

# Now read — note sslmode=require URL-embedded
JDBC_URL = (
    f"jdbc:postgresql://{os.environ['PG_HOST']}:{os.environ.get('PG_PORT','5432')}"
    f"/{os.environ['PG_DB']}?sslmode=require"
)
df = (spark.read.format("jdbc")
      .option("url", JDBC_URL)
      .option("driver", "org.postgresql.Driver")
      .option("user", os.environ["PG_USER"])
      .option("password", os.environ["PG_PASSWORD"])
      .option("dbtable", f"{os.environ.get('PG_SCHEMA','public')}.{os.environ['PG_TABLE']}")
      .load())
df.show(5)

Gotchas

• Use aidataplatform first. It is the official sample path and has its own bundled driver.
• SSL fallback — if the AIDP POSTGRESQL handler cannot pass SSL options and the server requires sslmode=require, use Option B with native Spark JDBC. Verified live 2026-04-27 against Neon serverless 17.8.
• No bundled driver for native Spark JDBC — if you use Option B, runtime-load the PostgreSQL jar and register it via URLClassLoader + DriverManager.registerDriver + spark._jsc.addJar.
• Network reachability — Postgres must be reachable from the AIDP cluster's NAT egress IP. Public-internet endpoints (Neon, Supabase, RDS public) work via the cluster's NAT path. Self-hosted Postgres in user-managed VCNs typically does NOT work — the cluster's pod CIDR has no route to user VCNs without explicit VCN peering. Smoke-test with a Python socket: socket.create_connection((host, 5432), timeout=8).
• schema is the Postgres logical schema (e.g. public), not the database name. The database name is a separate PG_DB env var that goes into the JDBC URL.
• Write modes — CREATE (fail if exists), APPEND, OVERWRITE. Default is CREATE.

References

• Helper: scripts/oracle_ai_data_platform_connectors/aidataplatform.py
• Official sample: data-engineering/ingestion/Read_Write_External_Ecosystem_Connectors/PostgreSQL.ipynb