Skip to content
Tutorial Reference

Python Psycopg2: How to Resolve "psycopg2 OperationalError" in Python

Fix common PostgreSQL connection errors in Python with psycopg2, including wrong credentials, server issues, and SSL requirements, using clear solutions and diagnostic steps.

When connecting to a PostgreSQL database using Python's psycopg2 library, you may encounter psycopg2.OperationalError, a broad exception that indicates something went wrong with the database connection or operation. This error can be triggered by incorrect credentials, network problems, a stopped database server, or configuration issues. In this guide, we'll cover the most common causes of this error, show what each error message looks like, and provide clear solutions for each scenario.

What Is psycopg2.OperationalError?

psycopg2.OperationalError is an exception class in the psycopg2 library (Python's most popular PostgreSQL adapter) that covers a range of connection and operational failures. Unlike programming errors, operational errors are typically caused by external factors: wrong credentials, unreachable servers, or misconfigured databases.

The error usually includes a descriptive message that tells you exactly what went wrong. Learning to read these messages is the key to fixing the issue quickly.

Common Causes and Solutions

1. Incorrect Database Credentials

The most frequent cause is providing wrong values for the database name, username, password, host, or port.

❌ Wrong: Incorrect database name

import psycopg2

conn = psycopg2.connect(
    dbname="my_databse",  # Typo: should be "my_database"
    user="postgres",
    password="mypassword",
    host="localhost",
    port="5432"
)

Output:

psycopg2.OperationalError: FATAL:  database "my_databse" does not exist

❌ Wrong: Incorrect password

conn = psycopg2.connect(
    dbname="my_database",
    user="postgres",
    password="wrong_password",
    host="localhost",
    port="5432"
)

Output:

psycopg2.OperationalError: FATAL:  password authentication failed for user "postgres"

✅ Correct: Use verified credentials

import psycopg2

try:
    conn = psycopg2.connect(
        dbname="my_database",
        user="postgres",
        password="correct_password",
        host="localhost",
        port="5432"
    )
    print("Connected successfully!")
    conn.close()
except psycopg2.OperationalError as e:
    print(f"Connection failed: {e}")

Output:

Connected successfully!

2. PostgreSQL Server Is Not Running

If the PostgreSQL service isn't running, psycopg2 can't establish a connection.

Error output

psycopg2.OperationalError: could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?

Fix: Start the PostgreSQL service

sudo systemctl start postgresql
sudo systemctl status postgresql

brew services start postgresql

net start postgresql-x64-15  # Version number may vary

Verify the server is running:

pg_isready -h localhost -p 5432

Expected output

localhost:5432 - accepting connections

3. Wrong Host or Port

If your PostgreSQL server is running on a different host or port than what you've specified, the connection will fail.

Error output (wrong host)

psycopg2.OperationalError: could not translate host name "wrong-host" to address: Name or service not known

Error output (wrong port)

psycopg2.OperationalError: could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5433?

Fix: Find the correct host and port:

sudo ss -tlnp | grep postgres

cat /etc/postgresql/15/main/postgresql.conf | grep port

Then update your connection parameters:

conn = psycopg2.connect(
    dbname="my_database",
    user="postgres",
    password="mypassword",
    host="localhost",    # Or the actual server IP
    port="5432"          # Match the actual port
)

4. PostgreSQL Not Accepting TCP/IP Connections

By default, some PostgreSQL installations only accept local Unix socket connections and reject TCP/IP connections.

Error output

psycopg2.OperationalError: could not connect to server: Connection refused

Fix: Configure PostgreSQL to accept TCP/IP connections

Step 1: Edit postgresql.conf to listen on all addresses:

sudo -u postgres psql -c "SHOW config_file;"

sudo nano /etc/postgresql/15/main/postgresql.conf

Change:

To:

listen_addresses = '*'    # Or 'localhost' for local connections only

Step 2: Edit pg_hba.conf to allow password authentication:

sudo nano /etc/postgresql/15/main/pg_hba.conf

Add or modify:

host    all       all   127.0.0.1/32   md5
host    all       all   0.0.0.0/0      md5    # For remote connections

Step 3: Restart PostgreSQL:

sudo systemctl restart postgresql

5. Database Does Not Exist

If you specify a database name that hasn't been created, you'll get an error.

Error output

psycopg2.OperationalError: FATAL:  database "myapp_db" does not exist

Fix: Create the database

sudo -u postgres psql -c "CREATE DATABASE myapp_db;"

sudo -u postgres createdb myapp_db

Then connect:

conn = psycopg2.connect(
    dbname="myapp_db",
    user="postgres",
    password="mypassword",
    host="localhost",
    port="5432"
)
print("Connected!")

6. SSL Connection Required

Some PostgreSQL servers (especially cloud-hosted ones like AWS RDS, Heroku, or Supabase) require SSL connections.

Error output

psycopg2.OperationalError: FATAL:  no pg_hba.conf entry for host "x.x.x.x", user "postgres", database "mydb", SSL off

Fix: Enable SSL in the connection

import psycopg2

conn = psycopg2.connect(
    dbname="my_database",
    user="postgres",
    password="mypassword",
    host="your-cloud-host.amazonaws.com",
    port="5432",
    sslmode="require"  # Enable SSL
)
print("Connected with SSL!")

Common sslmode values:

Mode Description
disable No SSL
allow Try non-SSL first, then SSL
prefer Try SSL first, then non-SSL (default)
require Must use SSL (no certificate verification)
verify-ca Must use SSL with CA certificate verification
verify-full Must use SSL with full certificate verification

7. Too Many Connections

PostgreSQL has a maximum connection limit (default is usually 100). Exceeding it causes an error.

Error output

psycopg2.OperationalError: FATAL:  too many connections for role "postgres"

Fix: Close connections properly and use connection pooling:

import psycopg2
from psycopg2 import pool

connection_pool = pool.SimpleConnectionPool(
    minconn=1,
    maxconn=10,
    dbname="my_database",
    user="postgres",
    password="mypassword",
    host="localhost",
    port="5432"
)

conn = connection_pool.getconn()

try:
    cursor = conn.cursor()
    cursor.execute("SELECT version();")
    print(cursor.fetchone())
    cursor.close()
finally:
    connection_pool.putconn(conn)

connection_pool.closeall()

Robust Connection Template

Here's a production-ready connection pattern with proper error handling:

import psycopg2
import sys
import os

def get_db_connection():
    """Establish a connection to PostgreSQL with error handling."""
    try:
        conn = psycopg2.connect(
            dbname=os.environ.get("DB_NAME", "my_database"),
            user=os.environ.get("DB_USER", "postgres"),
            password=os.environ.get("DB_PASSWORD", ""),
            host=os.environ.get("DB_HOST", "localhost"),
            port=os.environ.get("DB_PORT", "5432"),
            connect_timeout=5,  # Timeout after 5 seconds
        )
        print("✅ Connected to PostgreSQL successfully.")
        return conn
    except psycopg2.OperationalError as e:
        error_message = str(e).strip()

        if "does not exist" in error_message:
            print(f"❌ Database not found: {error_message}")
        elif "password authentication failed" in error_message:
            print(f"❌ Authentication failed: {error_message}")
        elif "Connection refused" in error_message:
            print("❌ Connection refused. Is PostgreSQL running?")
        elif "could not translate host name" in error_message:
            print(f"❌ Invalid host: {error_message}")
        else:
            print(f"❌ Connection error: {error_message}")

        sys.exit(1)


conn = get_db_connection()
try:
    cursor = conn.cursor()
    cursor.execute("SELECT version();")
    print(f"PostgreSQL version: {cursor.fetchone()[0]}")
    cursor.close()
finally:
    conn.close()
    print("Connection closed.")

Output (successful connection):

✅ Connected to PostgreSQL successfully.
PostgreSQL version: PostgreSQL 15.4 on x86_64-pc-linux-gnu...
Connection closed.

Diagnostic Checklist

When you encounter psycopg2.OperationalError, work through these checks:

# Check Command
1 Read the full error message It tells you exactly what's wrong
2 Is PostgreSQL running? pg_isready -h localhost -p 5432
3 Can you connect via psql? psql -h localhost -U postgres -d mydb
4 Is the port correct? ss -tlnp | grep postgres
5 Is TCP/IP enabled? Check postgresql.conf → listen_addresses
6 Is authentication configured? Check pg_hba.conf
7 Does the database exist? psql -l to list databases
8 Is SSL required? Add sslmode="require" to connection
9 Are connections exhausted? Check with SELECT count(*) FROM pg_stat_activity;

Conclusion

The psycopg2.OperationalError is a catch-all exception for connection and operational failures with PostgreSQL.

The error message itself is your best diagnostic tool: it tells you whether the problem is wrong credentials, a stopped server, a network issue, a missing database, or an SSL requirement.

Always start by reading the full error message, verify your credentials with psql directly, check that PostgreSQL is running and accepting connections, and use proper error handling with try/except blocks in your code.

For production applications, use connection pooling and environment variables for credentials to build robust, maintainable database connections.

Navigation