GeoPandas Cannot Connect to PostGIS: How to Fix It

On this page

Problem statement

The database is running. QGIS connects to it. Python does not. Here are six attempts at one real PostGIS server — a local PostGIS 3.4 in Docker, listening on port 55432 — each failing somewhere different:

from sqlalchemy import create_engine, text


def attempt(url):
    try:
        with create_engine(url).connect() as con:
            return f"connected, PostGIS {con.execute(text('SELECT postgis_lib_version()')).scalar()}"
    except Exception as exc:
        message = str(getattr(exc, "orig", exc)).splitlines()[0]
        return f"{type(exc).__name__}: {message.split(' failed: ')[-1]}"


for url in ["postgresql+psycopg://user:spatialworkflow@db.internal:5432/gis",
            "postgresql+psycopg://user:spatialworkflow@localhost:5439/gis",
            "postgresql+psycopg://user:wrong@localhost:55432/gis",
            "postgresql+psycopg://user:spatialworkflow@localhost:55432/spatial",
            "postgresql+psycopg://user:spatialworkflow@localhost:55432/nopostgis",
            "postgresql+psycopg://user:spatialworkflow@localhost:55432/gis?sslmode=require"]:
    print(f"{url.split('@')[1]:36} {attempt(url)}")

db.internal:5432/gis                 OperationalError: failed to resolve host 'db.internal': [Errno -2] Name or service not known
localhost:5439/gis                   OperationalError: Connection refused
localhost:55432/gis                  OperationalError: FATAL:  password authentication failed for user "user"
localhost:55432/spatial              OperationalError: FATAL:  database "spatial" does not exist
localhost:55432/nopostgis            ProgrammingError: function postgis_lib_version() does not exist
localhost:55432/gis?sslmode=require  OperationalError: server does not support SSL, but SSL was required

Six errors that all look like "cannot connect", with six different causes — DNS, a port nobody is listening on, the password, the database name, a database without PostGIS (that one connects, then fails), and an SSL requirement the server cannot meet.

They are worth separating, because each has a distinct fix and only two of them are about the network.

Quick answer

Work outward from the driver, and let each error name its own layer:

import importlib.util
import sqlalchemy

# 1. is a driver installed, and which one?
for mod in ("psycopg", "psycopg2", "pg8000"):
    print(mod, "✓" if importlib.util.find_spec(mod) else "✗")
# 2. which driver does a bare postgresql:// URL use with this SQLAlchemy?
print("SQLAlchemy", sqlalchemy.__version__, "→ postgresql:// means", create_engine("postgresql://").dialect.driver)

psycopg ✓
psycopg2 ✗
pg8000 ✗
SQLAlchemy 2.1.1 → postgresql:// means psycopg

# 3. can anything reach the server at all?
#    $ psql "postgresql://user@localhost:55432/gis" -c "select 1"
# 4. is PostGIS installed in this database?
#    SELECT postgis_full_version();

Error	Layer	Fix
`No module named 'psycopg2'`	Python	`pip install "psycopg[binary]"` and use `+psycopg`
`Can't load plugin: …postgresql.psycopg`	SQLAlchemy	SQLAlchemy < 2.0 does not know psycopg 3 — upgrade it
`failed to resolve host` / `could not translate host name`	DNS	wrong hostname, or you are outside the network
`Connection refused`	network	wrong port, server not listening, firewall
`no pg_hba.conf entry`	server auth	server config must allow your IP/user/method
`password authentication failed`	credentials	wrong password, or the wrong user
`database "x" does not exist`	database	connected to the server, wrong database name
`server does not support SSL, but SSL was required`	TLS	the server has no certificate configured, or `sslmode` is wrong
`function postgis_…() does not exist` / `type "geometry" does not exist`	extension	`CREATE EXTENSION postgis;` in this database

Where the failure actually is

Six layers, six error messages. The message names the layer if you know what to listen for.

A named driver, a network check, read the error

Only two of these are fixed in Python. The rest are configuration.

1. Install a driver, and name it in the URL

GeoPandas talks to PostGIS through SQLAlchemy, which talks through a DBAPI driver. There are two in common use and they are not interchangeable in the URL:

pip install "psycopg[binary]"      # psycopg 3 — current, use this for new work
pip install psycopg2-binary        # psycopg 2 — still everywhere, still fine

create_engine("postgresql+psycopg://user:pw@host:5432/db")    # psycopg 3
create_engine("postgresql+psycopg2://user:pw@host:5432/db")   # psycopg 2
create_engine("postgresql://user:pw@host:5432/db")            # depends on the SQLAlchemy version

What the bare postgresql:// means changed between SQLAlchemy releases. Only psycopg 3 is installed here, so run the same line under SQLAlchemy 2.0 and under the installed version:

import subprocess
import sys

subprocess.run([sys.executable, "-m", "pip", "download", "--quiet", "--no-deps", "-d", "sa20",
                "sqlalchemy==2.0.36"], check=True)
subprocess.run([sys.executable, "-m", "zipfile", "-e", "sa20/SQLAlchemy-2.0.36-py3-none-any.whl", "sa20/lib"],
               check=True)
PROBE = ("import sqlalchemy\n"
         "try:\n"
         "    d = sqlalchemy.create_engine('postgresql://u@h/d').dialect.driver\n"
         "except Exception as exc:\n"
         "    d = f'{type(exc).__name__}: {exc}'\n"
         "print(sqlalchemy.__version__, d)")
for label, env in [("SQLAlchemy 2.0", {"PYTHONPATH": "sa20/lib"}), ("installed", {})]:
    out = subprocess.run([sys.executable, "-c", PROBE], capture_output=True, text=True,
                         env={**os.environ, **env}).stdout.strip()
    print(f"{label:15} {out}")

SQLAlchemy 2.0  2.0.36 ModuleNotFoundError: No module named 'psycopg2'
installed       2.1.1 psycopg

So two mistakes are common, and which one you meet depends on the version:

postgresql:// on SQLAlchemy 2.0 with only psycopg 3 installed → No module named 'psycopg2', because there the bare URL means psycopg2. SQLAlchemy 2.1 made psycopg 3 the default. Name the driver — postgresql+psycopg:// — and the question never arises.
postgresql+psycopg:// on SQLAlchemy 1.4 → Can't load plugin, because the psycopg 3 dialect arrived in SQLAlchemy 2.0. Upgrade SQLAlchemy, or use psycopg2.

The [binary] extra matters too: without it, pip builds psycopg from source and needs libpq-dev and a compiler, which is a different error entirely.

2. Prove the network works outside Python

Python is the worst place to debug a network problem, because every layer wraps the error. Go down a level:

psql "postgresql://user@localhost:55432/gis" -c "select 1"

If psql is not installed, a raw socket answers the network question on its own:

import socket

for host, port in [("db.internal", 5432), ("localhost", 5439), ("localhost", 55432)]:
    try:
        socket.create_connection((host, port), timeout=5).close()
        result = "reachable — the network is fine, move on"
    except socket.gaierror as exc:
        result = f"gaierror {exc} — DNS: hostname wrong or unresolvable"
    except ConnectionRefusedError as exc:
        result = f"{exc} — nothing listening on that port"
    except TimeoutError:
        result = "timed out — a firewall dropping packets"
    print(f"{host}:{port:<6} {result}")

db.internal:5432   gaierror [Errno -2] Name or service not known — DNS: hostname wrong or unresolvable
localhost:5439   [Errno 111] Connection refused — nothing listening on that port
localhost:55432  reachable — the network is fine, move on

Those failures are genuinely different. Refused means something answered and said no — usually the wrong port, or Postgres bound to localhost only. Timeout means nothing answered at all, which is a firewall or a security group. DNS means the name never resolved, which is common when a container hostname is used from outside the compose network.

3. Read `pg_hba.conf` errors literally

The server here accepts every host, so this one is not reproduced; the message has this shape:

FATAL: no pg_hba.conf entry for host "10.0.7.2", user "gis", database "spatial", SSL off

Every field in that line is a column of the rule table on the server.

This is not a password problem — the server refused before asking. Every field in the message is part of the rule that did not match: your IP, the user, the database, and whether SSL was on.

# pg_hba.conf on the server — most specific first
# TYPE  DATABASE  USER  ADDRESS         METHOD
hostssl spatial   gis   10.0.0.0/16     scram-sha-256
host    all       all   127.0.0.1/32    scram-sha-256

That first rule requires SSL, and the error says SSL off. From the client:

create_engine("postgresql+psycopg://gis:secret@db.internal:5432/spatial?sslmode=require")

sslmode=require encrypts without verifying the certificate; verify-full also checks the hostname against it and is what you want across an untrusted network. It only works if the server has SSL configured — the last attempt in the problem statement asked this server for SSL, and it has none.

Only a server administrator can change pg_hba.conf, and it needs a reload (SELECT pg_reload_conf();) — not a restart.

4. Check PostGIS is installed in this database

The fifth attempt in the problem statement connected and then failed: the database exists, the extension does not. Extensions are per-database, not per-server, so a cluster can have PostGIS in one database and not another:

nopostgis = create_engine("postgresql+psycopg://user:spatialworkflow@localhost:55432/nopostgis")
try:
    with nopostgis.connect() as con:
        con.execute(text("SELECT 'POINT(0 0)'::geometry"))
except Exception as exc:
    print(str(exc.orig).splitlines()[0])

with nopostgis.begin() as con:
    con.execute(text("CREATE EXTENSION IF NOT EXISTS postgis"))
    print(con.execute(text("SELECT postgis_full_version()")).scalar()[:60], "...")

type "geometry" does not exist
POSTGIS="3.4.3 e365945" [EXTENSION] PGSQL="160" GEOS="3.9.0- ...

CREATE EXTENSION needs superuser or a role with CREATE on the database. If you cannot run it, this is a request to whoever administers the server — and worth checking early, because everything else can be perfect and this still fails.

5. Keep the password out of the code

The connection strings in the problem statement have a password in them, which is fine for a snippet and wrong for a repository.

from sqlalchemy.engine import URL

url = URL.create(
    "postgresql+psycopg",
    username=os.environ["PGUSER"],
    password=os.environ["PGPASSWORD"],     # never a literal, never a default
    host=os.environ.get("PGHOST", "localhost"),
    port=int(os.environ.get("PGPORT", 5432)),
    database=os.environ["PGDATABASE"],
)
print(attempt(url))

# a password with an @ in it, built by hand and with URL.create
print("hand-built:", attempt("postgresql+psycopg://user:p@ss/word@localhost:55432/gis"))
print("URL.create:", URL.create("postgresql+psycopg", username="user", password="p@ss/word",
                                host="localhost", port=55432, database="gis").render_as_string(hide_password=False))

connected, PostGIS 3.4.3
hand-built: OperationalError: failed to resolve host 'ss': [Errno -3] Temporary failure in name resolution
URL.create: postgresql+psycopg://user:p%40ss%2Fword@localhost:55432/gis

URL.create also escapes special characters correctly — a password containing @ or / breaks a hand-built URL string in a way that produces a baffling host-name error, because the parser splits on the wrong character: here it went looking for a host called ss.

libpq reads PGHOST, PGUSER, PGPASSWORD, PGDATABASE and ~/.pgpass on its own, so a bare create_engine("postgresql+psycopg://") picks up a standard environment with no string at all. See how to handle credentials and secrets.

6. Create the engine once, and expect it to fail sometimes

engine = create_engine(
    url,
    pool_size=5, max_overflow=10,
    pool_pre_ping=True,        # test a pooled connection before handing it out
    pool_recycle=1800,         # drop connections older than 30 minutes
    connect_args={"connect_timeout": 10},
)

pool_pre_ping is the one that matters for scheduled jobs. A connection idle overnight is frequently killed by a firewall or the server, and without pre-ping the next query fails with server closed the connection unexpectedly — an error that looks like a network problem and is really a stale pool.

Create the engine once at module level, not per function. And never share one across fork() in a multiprocessing pool — each worker needs its own connections; two processes on one socket corrupt each other's traffic.

A diagnostic that names the failing layer

The data used on this page

A local PostGIS 3.4 in Docker (the postgis/postgis:16-3.4 image), published on port 55432; the server's own settings are the image defaults. The setup starts it, waits until it accepts connections, adds a second, empty database without PostGIS, and sets the standard PG* variables for step 5.

import os
import time

from sqlalchemy import create_engine, text

# the server: PostGIS 3.4 in Docker, published on localhost:55432
if os.system("docker start guide-postgis-55432 >/dev/null 2>&1") != 0:
    os.system("docker run -d --name guide-postgis-55432 -e POSTGRES_USER=user -e POSTGRES_PASSWORD=spatialworkflow "
              "-e POSTGRES_DB=gis -p 127.0.0.1:55432:5432 postgis/postgis:16-3.4 >/dev/null")
for _ in range(60):                                  # the image restarts once while it installs PostGIS
    if os.system("docker exec guide-postgis-55432 pg_isready -h 127.0.0.1 -U user -d gis >/dev/null 2>&1") == 0:
        break
    time.sleep(2)

os.environ.setdefault("PGPORT", "55432")
os.environ.setdefault("PGPASSWORD", "spatialworkflow")
os.environ.setdefault("PGUSER", "user")
os.environ.setdefault("PGDATABASE", "gis")
admin = create_engine("postgresql+psycopg://user@localhost/gis", isolation_level="AUTOCOMMIT")
with admin.connect() as con:
    con.execute(text("DROP DATABASE IF EXISTS nopostgis"))
    con.execute(text("CREATE DATABASE nopostgis"))
    print(con.execute(text("SELECT datname FROM pg_database WHERE datname IN ('gis', 'nopostgis') "
                           "ORDER BY 1")).scalars().all())

['gis', 'nopostgis']

Example 1: a connection diagnostic that names the layer

def diagnose(host, port, database, user, password=None):
    print(f"→ {user}@{host}:{port}/{database}")

    drivers = [m for m in ("psycopg", "psycopg2", "pg8000")
               if importlib.util.find_spec(m)]
    print(f"  drivers    {drivers or 'NONE — pip install \"psycopg[binary]\"'}")
    if not drivers:
        return
    print(f"  sqlalchemy {sqlalchemy.__version__}"
          + ("  (needs >=2.0 for +psycopg)" if sqlalchemy.__version__ < "2" else ""))

    try:
        socket.create_connection((host, port), timeout=5).close()
        print("  network    reachable")
    except socket.gaierror:
        print(f"  network    DNS cannot resolve {host!r}"); return
    except ConnectionRefusedError:
        print(f"  network    refused on :{port} — wrong port, or not listening"); return
    except OSError as exc:
        print(f"  network    {exc} — firewall?"); return

    driver = "psycopg" if "psycopg" in drivers else "psycopg2"
    url = URL.create(f"postgresql+{driver}", username=user, password=password,
                     host=host, port=port, database=database)
    try:
        with create_engine(url, connect_args={"connect_timeout": 10}).connect() as conn:
            print("  auth       ok")
            try:
                print(f"  postgis    {conn.execute(text('SELECT postgis_version()')).scalar()}")
            except Exception:
                print("  postgis    NOT INSTALLED — run: CREATE EXTENSION postgis;")
    except Exception as exc:
        msg = str(exc).lower()
        if "pg_hba" in msg:
            print("  auth       server rejected this host/user/ssl combination")
        elif "password" in msg:
            print("  auth       wrong password or user")
        elif "does not exist" in msg:
            print("  auth       connected, but that database does not exist")
        else:
            print(f"  auth       {type(exc).__name__}: {exc}")


with admin.connect() as con:
    con.execute(text("DROP DATABASE IF EXISTS empty_db"))
    con.execute(text("CREATE DATABASE empty_db"))
for host, port, database, password in [("db.internal", 5432, "gis", "spatialworkflow"),
                                       ("localhost", 55432, "gis", "wrong"),
                                       ("localhost", 55432, "empty_db", "spatialworkflow"),
                                       ("localhost", 55432, "gis", "spatialworkflow")]:
    diagnose(host, port, database, "user", password)

→ user@db.internal:5432/gis
  drivers    ['psycopg']
  sqlalchemy 2.1.1
  network    DNS cannot resolve 'db.internal'
→ user@localhost:55432/gis
  drivers    ['psycopg']
  sqlalchemy 2.1.1
  network    reachable
  auth       wrong password or user
→ user@localhost:55432/empty_db
  drivers    ['psycopg']
  sqlalchemy 2.1.1
  network    reachable
  auth       ok
  postgis    NOT INSTALLED — run: CREATE EXTENSION postgis;
→ user@localhost:55432/gis
  drivers    ['psycopg']
  sqlalchemy 2.1.1
  network    reachable
  auth       ok
  postgis    3.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

Run this before reading any more error messages. It reports the first layer that fails and stops, which is the opposite of what a stack trace does.

Step 1 ran the bare URL under both releases; the named-driver row is the fix.

Example 2: the connection module worth having

from functools import lru_cache


@lru_cache(maxsize=1)
def get_engine():
    url = URL.create(
        "postgresql+psycopg",
        username=os.environ["PGUSER"],
        password=os.environ.get("PGPASSWORD"),
        host=os.environ.get("PGHOST", "localhost"),
        port=int(os.environ.get("PGPORT", 5432)),
        database=os.environ["PGDATABASE"],
    )
    engine = create_engine(
        url, pool_pre_ping=True, pool_recycle=1800,
        connect_args={"connect_timeout": 10,
                      "options": "-c statement_timeout=300000"},   # 5 min
    )
    with engine.connect() as conn:                                  # fail fast at startup
        conn.execute(text("SELECT postgis_version()"))
    return engine


print(get_engine() is get_engine())
with get_engine().connect() as con:
    print("statement_timeout", con.execute(text("SHOW statement_timeout")).scalar())

True
statement_timeout 5min

lru_cache gives one engine per process. statement_timeout stops a runaway spatial query holding a connection all night — a real risk once a job is unattended.

Example 3: connecting from Docker, where hostnames differ

services:
  db:
    image: postgis/postgis:16-3.4
    environment:
      POSTGRES_DB: gis
      POSTGRES_USER: user
      POSTGRES_PASSWORD: secret
    ports: ["55432:5432"]       # host port 55432 → container port 5432
  job:
    build: .
    environment:
      PGHOST: db                # the service name, not localhost
      PGPORT: "5432"            # the *container* port
      PGDATABASE: gis
      PGUSER: user
      PGPASSWORD: secret
    depends_on: [db]

From inside job, the host is db on port 5432. From your laptop, it is localhost on port 55432 — the setup this page used. Using the wrong pair is the common cause of Connection refused in a containerised setup — and the postgis/postgis image already has the extension installed in its default database, which removes step 4 until you create a second one.

Four layers that fail independently

Every component of the URL has its own error message when it is wrong.

gpd.read_postgis sits on top of four things, and each can fail independently:

GeoPandas converts the result's geometry column from EWKB into Shapely objects.
SQLAlchemy parses the URL, picks a dialect, and manages a connection pool.
The DBAPI driver (psycopg) speaks the PostgreSQL wire protocol over a socket.
The server decides whether to accept the connection, then whether the database has PostGIS.

The most confusing errors come from the boundary between 2 and 3. SQLAlchemy's URL scheme names both the database and the driver: postgresql+psycopg2 versus postgresql+psycopg. Writing bare postgresql:// lets SQLAlchemy choose: psycopg2 up to 2.0, psycopg 3 from 2.1 — so the same project with only psycopg 3 installed works on one version and fails on the other with No module named 'psycopg2', an error about a package nobody mentioned. Going the other way, +psycopg on SQLAlchemy 1.4 fails with Can't load plugin, because that dialect did not exist yet.

The pg_hba.conf error is worth reading carefully rather than pattern-matching to "auth problem". PostgreSQL evaluates that file top to bottom and uses the first matching line for the connection type, source address, database and user. "No entry" means no line matched at all — the server never even asked for a password. Every field in the error message is a field it was matching on, including SSL off, which is the one people miss.

Finally, type "geometry" does not exist is not a connection error at all — the connection succeeded. PostgreSQL extensions are installed per-database, so a server can host ten databases with PostGIS in three of them. The error surfaces on the first query that mentions a geometry column, which makes it look like a query problem rather than a setup one.

Passwords with symbols and localhost

A password containing @, / or : breaks a hand-built URL. Use URL.create, which escapes properly.
localhost and 127.0.0.1 can match different pg_hba.conf lines — one may resolve to IPv6 ::1, matching a host all all ::1/128 rule that does not exist.
Do not share an engine across fork(). Each multiprocessing worker must create its own, or connections get corrupted.
pool_pre_ping costs one round trip per checkout and is worth it for any job that idles.
read_postgis needs geom_col to match the column name. It defaults to geom, the PostGIS convention — but to_postgis writes a column called geometry, so a table GeoPandas wrote needs geom_col="geometry" to be read back.
A read-only replica rejects writes with a permission error that reads like an auth problem.
statement_timeout is set per session, so it belongs in connect_args, not in a query.
PgBouncer in transaction mode breaks prepared statements used by psycopg 3 — set prepare_threshold=None in connect_args.
Connection strings end up in logs. Build them from environment variables and never log the URL object directly.

Internal links

How to connect GeoPandas to PostGIS — the working setup this page debugs
PostGIS explained: when a spatial database beats a folder of files — whether to be here at all
How PostGIS stores geometry: SRID, EWKB and the typed column — why CREATE EXTENSION matters
How to write a GeoDataFrame to PostGIS — the next thing that fails
How to run spatial SQL queries from Python with PostGIS — once the connection works
How to handle credentials and secrets in an automated GIS job — where the password belongs
How to set up a Python GIS environment that actually works — installing the driver alongside everything else
How to load and write PostGIS layers from PyQGIS — the same database from QGIS

FAQ

psycopg2 or psycopg 3?

psycopg 3 for new work — it is current, and pip install "psycopg[binary]" needs no compiler. It requires SQLAlchemy 2.0 and the postgresql+psycopg:// URL scheme.

Why does `postgresql://` say `No module named 'psycopg2'`?

On SQLAlchemy 2.0 and earlier the bare scheme means psycopg2; 2.1 switched the default to psycopg 3. Name the driver — postgresql+psycopg:// — and the version stops mattering.

What does `no pg_hba.conf entry` mean?

The server refused before authenticating, because no rule matched your source address, user, database and SSL state. Only a server admin can add one — and the error lists every field it matched on.

QGIS connects but Python does not. Why?

QGIS bundles its own libpq and often its own certificates and service definitions. Compare host, port, database and SSL mode exactly; usually the difference is sslmode or a ~/.pg_service.conf entry QGIS is using.

`type "geometry" does not exist` — is that a connection problem?

No, the connection worked. PostGIS is not installed in that specific database. Run CREATE EXTENSION postgis; while connected to it.

Should I create the engine once or per query?

Once, at module level. An engine manages a pool; creating one per query opens and closes connections constantly and defeats pooling entirely.

My scheduled job fails overnight but works when I run it. Why?

An idle connection was closed by the server or a firewall. Add pool_pre_ping=True and pool_recycle so stale connections are replaced rather than used.