cloudsql-idle-connection-timeout

Cloud SQL Idle Connection Timeout Fix

Problem

Cloud SQL (and other managed PostgreSQL services) kill idle connections after a timeout (typically 10 minutes). Long-running scripts that open a DB connection early, then perform non-DB work (HTTP requests, file I/O, CDX scanning), then try to use the DB connection again will get a misleading error.

Context / Trigger Conditions

• psycopg2.OperationalError: could not receive data from server: Operation timed out
• psycopg2.InterfaceError: connection already closed
• Script has phases: DB setup → long non-DB work → DB writes
• Using Google Cloud SQL, AWS RDS, or Azure Database for PostgreSQL
• Connection was working initially, fails after idle period
• The error appears AFTER a phase that doesn't use the DB (e.g., API crawling, file processing)

Solution

Pattern 1: Defer DB Connection (Preferred)

Structure code so the DB connection opens AFTER the non-DB phase:

# BAD: Connection opens before long CDX scan
with VineDatabase() as db:
    ensure_schema(db)
    results = long_running_api_scan()  # 5-10 minutes, no DB needed
    process_results(db, results)  # Connection dead here!

# GOOD: CDX scan first, then fresh DB connection
results = long_running_api_scan()  # No DB connection open

with VineDatabase() as db:  # Fresh connection when actually needed
    ensure_schema(db)
    process_results(db, results)

Pattern 2: Reconnection Logic (For Long Batch Operations)

For operations that DO use the DB but might exceed the idle timeout between writes:

import psycopg2

def reconnect_db():
    """Create a fresh database connection."""
    db = VineDatabase()
    cursor = db._cursor()
    return db, cursor

def process_batch(db, cursor, items):
    for item in items:
        data = fetch_from_api(item)  # Slow network call
        try:
            cursor.execute("INSERT INTO ...", data)
            db.conn.commit()
        except (psycopg2.OperationalError, psycopg2.InterfaceError):
            # Connection died, reconnect and retry
            try:
                db.close()
            except Exception:
                pass
            db, cursor = reconnect_db()
            cursor.execute("INSERT INTO ...", data)
            db.conn.commit()