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()

Pattern 3: TCP Keepalive (Alternative)

Configure psycopg2 to send TCP keepalive packets:

conn = psycopg2.connect(
    database_url,
    keepalives=1,
    keepalives_idle=60,
    keepalives_interval=10,
    keepalives_count=5
)

Note: This may not work with all Cloud SQL proxy configurations.

Verification

1. Run the script with the long non-DB phase
2. Confirm no OperationalError or InterfaceError after the idle period
3. Verify data is being written to the DB during the fetch phase:

   SELECT COUNT(*) FROM your_table WHERE created_at > NOW() - INTERVAL '5 minutes';
   

Example

A Vine archive crawler that:

1. Scans Wayback Machine CDX for archived profiles (107 pages, ~6 minutes)
2. Then fetches and stores each profile in Cloud SQL

The CDX scan doesn't need the DB, so opening the connection before it means the connection sits idle for 6 minutes and gets killed by Cloud SQL. Fix: run CDX scan first, then open DB connection for the fetch-and-store phase.

Notes

• Cloud SQL default idle timeout is ~10 minutes but can vary
• The error message "could not receive data from server: Operation timed out" is misleading—it sounds like a network issue but is actually an idle timeout
• psycopg2.InterfaceError: connection already closed often follows the OperationalError
• For very long batch operations (hours), combine Pattern 1 and Pattern 2
• Also check Cloud SQL authorized networks if connection fails immediately (different error)
• When using nohup or background processes, ensure output is unbuffered (PYTHONUNBUFFERED=1)