postgres_scanner

postgres_scanner copied to clipboard

Hi all,

I'm having some issues connecting to Google Cloud Platform Cloud SQL postgress database using postgres scanner extension for Alpine.

To start with, I cannot install musle supported postgres scanner extension using 'INSTALL postgres' method (I have an open issue for this: https://github.com/duckdb/duckdb/issues/16511), so I went ahead and downloaded one from here: https://github.com/duckdb/duckdb-postgres/actions/runs/13164449162

I could then install and load the extension, but when I try to use ATTACH method to connect to a postgres database on Google Cloud Platform, I get this error:

duckdb.duckdb.IOException: IO Error: Unable to connect to Postgres at dbname=dbname user=user host=host password=password: connection to server at "host", port 5432 failed: FATAL: no PostgreSQL user name specified in startup packet

I get the same error if I use Duckdb COPY method to copy data to postgres database on GCP. I tried configure via secrets as well as using URI formatted connection strings - all resulting in the same error.

I can connect fine and copy data with the postgres scanner extension run on Debian system.

Thank you!

To reproduce:

1. create a docker file using this code:

FROM alpine:latest

ENV PYTHONUNBUFFERED=1 \
    PIP_NO_CACHE_DIR=1 \
    PATH="/root/.local/bin:$PATH"

RUN apk add --no-cache \
    python3 py3-pip py3-setuptools py3-wheel \
    gcompat \
    postgresql16 postgresql16-client postgresql16-contrib \
    postgis \
    g++ gcc libpq-dev musl-dev \
    py3-psycopg2

RUN pip install --upgrade pip --break-system-packages \
    && pip install flask pandas duckdb flask-cors sqlalchemy --break-system-packages

WORKDIR /app

COPY app/ /app/

COPY postgres_scanner.duckdb_extension /app/postgres_scanner.duckdb_extension

EXPOSE 5000

CMD ["python3", "server.py"]

2. Create server.py file inside /app directory and paste this code:

import duckdb

con = duckdb.connect(config = {"allow_unsigned_extensions": "true"})
con.execute('SET allow_extensions_metadata_mismatch=true')
con.execute(f"FORCE INSTALL '/app/postgres_scanner.duckdb_extension'; LOAD postgres_scanner;")
con.execute(f"""ATTACH 'dbname={dbname} user={user} host={host} password={password}' AS postgres_db (TYPE POSTGRES);""")