🔧 Fix PostGIS & databases Pipelines & automation Spatial SQL & PostGIS · step 12 of 15 Updated

GeoPandas Cannot Connect to PostGIS: How to Fix It

Problem statement

The database is running. QGIS connects to it. Python does not.

from sqlalchemy import create_engine
import geopandas as gpd

engine = create_engine("postgresql://gis:[email protected]:5432/spatial")
gdf = gpd.read_postgis("SELECT * FROM parcels", engine, geom_col="geom")
ModuleNotFoundError: No module named 'psycopg2'
OperationalError: connection to server at "db.internal" (10.0.4.9), port 5432 failed:
    FATAL:  no pg_hba.conf entry for host "10.0.7.2", user "gis", SSL off
OperationalError: could not translate host name "db.internal" to an address
UndefinedObject: type "geometry" does not exist at character 42
sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgresql.psycopg

Five errors that all look like "cannot connect" and have five different causes — a missing driver, a server-side auth rule, DNS, a missing extension, and a dialect name that does not match the installed driver.

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

Quick answer

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

# 1. is a driver installed, and which one?
import importlib
for mod in ("psycopg", "psycopg2", "pg8000"):
    print(mod, "✓" if importlib.util.find_spec(mod) else "✗")

# 2. does the URL name the driver you actually have?
"postgresql+psycopg://…"    # psycopg 3   ← install with: pip install "psycopg[binary]"
"postgresql+psycopg2://…"   # psycopg 2   ← pip install psycopg2-binary
"postgresql://…"            # SQLAlchemy's default = psycopg2

# 3. can anything reach the server at all?
#    $ psql "postgresql://[email protected]:5432/spatial" -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
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
type "geometry" does not exist extension CREATE EXTENSION postgis; in this database 
# a connection that works
engine = create_engine("postgresql+psycopg://gis:[email protected]:5432/spatial")
with engine.connect() as conn:
    print(conn.execute(text("SELECT postgis_full_version()")).scalar())

Where the failure actually is

Stacked layers from GeoPandas through SQLAlchemy, the driver, the network and server auth to the database extension.
Six layers, six error messages. The message names the layer if you know what to listen for.

Step-by-step solution

Triage rows pairing each connection error with its layer and its fix.
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")            # defaults to psycopg2

Two mistakes here are extremely common:

  • postgresql:// with only psycopg 3 installed → No module named 'psycopg2', because the bare URL means psycopg2. Use postgresql+psycopg://.
  • 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.
import sqlalchemy
print(sqlalchemy.__version__)      # needs >= 2.0 for +psycopg

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://[email protected]:5432/spatial" -c "select 1"

If psql is not installed:

python -c "import socket; socket.create_connection(('db.internal', 5432), timeout=5); print('reachable')"
socket.gaierror: [Errno -2] Name or service not known   → DNS: hostname is wrong or unresolvable
ConnectionRefusedError: [Errno 111] Connection refused  → nothing listening on that port
TimeoutError                                            → firewall dropping packets silently
reachable                                               → the network is fine, move on

Those three 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 

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

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:[email protected]: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:

create_engine(
    "postgresql+psycopg://[email protected]:5432/spatial"
    "?sslmode=verify-full&sslrootcert=/etc/ssl/certs/ca.pem"
)

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 

UndefinedObject: type "geometry" does not exist

You connected successfully. The extension is missing — and extensions are per-database, not per-server, so a cluster can have PostGIS in one database and not another.

SELECT postgis_full_version();
-- ERROR: function postgis_full_version() does not exist

CREATE EXTENSION IF NOT EXISTS postgis;
SELECT postgis_full_version();
-- POSTGIS="3.4.2" [EXTENSION] PGSQL="160" GEOS="3.12.1" PROJ="9.3.1" ...

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 string in every example on this page has a password in it, which is fine for a snippet and wrong for a repository.

import os
from sqlalchemy import create_engine
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"],
)
engine = create_engine(url)

URL.create also escapes special characters correctly — a password containing @ or / breaks a hand-built URL string in a way that produces a baffling "could not translate host name" error, because the parser splits on the wrong character.

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, or you get SSL error: decryption failed from two processes using one socket.

Code examples

Example 1: a connection diagnostic that names the layer 

import importlib.util, os, socket, sys

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
    import sqlalchemy
    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

    from sqlalchemy import create_engine, text
    from sqlalchemy.engine import URL
    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}")

diagnose("db.internal", 5432, "spatial", os.environ["PGUSER"], os.environ.get("PGPASSWORD"))

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.

Example 2: the connection module worth having 

# src/db.py
import os
from functools import lru_cache
from sqlalchemy import create_engine, text
from sqlalchemy.engine import URL

@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

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: spatial
      POSTGRES_USER: gis
      POSTGRES_PASSWORD: secret
    ports: ["5433:5432"]        # host port 5433 → container port 5432

  job:
    build: .
    environment:
      PGHOST: db                # the service name, not localhost
      PGPORT: "5432"            # the *container* port
      PGDATABASE: spatial
      PGUSER: gis
      PGPASSWORD: secret
    depends_on: [db]

From inside job, the host is db on port 5432. From your laptop, it is localhost on port 5433. Using the wrong pair is the single most common cause of Connection refused in a containerised setup — and the postgis/postgis image already has the extension installed, which removes step 4.

Explanation

Anatomy of a PostGIS connection URL with each component labelled and its failure mode noted.
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:

  1. GeoPandas converts the result's geometry column from EWKB into Shapely objects.
  2. SQLAlchemy parses the URL, picks a dialect, and manages a connection pool.
  3. The DBAPI driver (psycopg) speaks the PostgreSQL wire protocol over a socket.
  4. 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:// selects psycopg2 by default — so a project that installed only psycopg 3 gets 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.

Edge cases or notes

  • 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 — the PostGIS convention is geom, GeoPandas defaults to geometry.
  • 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.

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'?

The bare scheme means psycopg2 to SQLAlchemy. If you installed psycopg 3, name it: postgresql+psycopg://.

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.