Skip to content
Skills Library
Programming · Building & coding

cloudsql-idle-connection-timeout

/cloudsql-idle-connection-timeout

Fix psycopg2 "could not receive data from server: Operation timed out" or "connection already closed" errors when using Cloud SQL with long-running Python scripts. Use when: (1) psycopg2.OperationalError after a period of no DB activity, (2) DB connection works initially but fails after a non-DB phase (API calls, file processing, CDX scans), (3) Cloud SQL managed PostgreSQL kills idle connections. The fix is to defer DB connection opening until needed, or add reconnection logic for long-running batch processes.

Licence
AGPL-3.0
Last repository change
27.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 27.10.2026, without references to retired models or APIs.

Licence

AGPL-3.0, copyleft: przeróbki udostępniane innym muszą mieć tę samą licencję.

What it does and when to use it

Fix psycopg2 "could not receive data from server: Operation timed out" or "connection already closed" errors when using Cloud SQL with long-running Python scripts. Use when: (1) psycopg2.OperationalError after a period of no DB activity, (2) DB connection works initially but fails after a non-DB phase (API calls, file processing, CDX scans), (3) Cloud SQL managed PostgreSQL kills idle connections. The fix is to defer DB connection opening until needed, or add reconnection logic for long-running batch processes.

Triggers from the description

could not receive data from server: Operation timed outconnection already closed
Original author description

Fix psycopg2 "could not receive data from server: Operation timed out" or "connection already closed" errors when using Cloud SQL with long-running Python scripts. Use when: (1) psycopg2.OperationalError after a period of no DB activity, (2) DB connection works initially but fails after a non-DB phase (API calls, file processing, CDX scans), (3) Cloud SQL managed PostgreSQL kills idle connections. The fix is to defer DB connection opening until needed, or add reconnection logic for long-running batch processes.

How it works

  1. Problem
  2. Context / Trigger Conditions
  3. Solution
  4. Verification
  5. Example
  6. Notes
Original SKILL.md headings · 112 lines, 535 words
SKILL.md preview
---
name: cloudsql-idle-connection-timeout
description: |
  Fix psycopg2 "could not receive data from server: Operation timed out" or
  "connection already closed" errors when using Cloud SQL with long-running Python scripts.
  Use when: (1) psycopg2.OperationalError after a period of no DB activity, (2) DB connection
  works initially but fails after a non-DB phase (API calls, file processing, CDX scans),
  (3) Cloud SQL managed PostgreSQL kills idle connections. The fix is to defer DB connection
  opening until needed, or add reconnection logic for long-running batch processes.
author: Claude Code
version: 1.0.0
date: 2025-05-15
---

# 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:

```python
# 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:

```python
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:

```python
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:
   ```sql
   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`)

Versions

The database has no other skills named cloudsql-idle-connection-timeout.

Identical content appears in 1 other GitHub folders. We show one copy with the highest score.

Database snapshot: 13.11.2026 · package version 33b29987110e (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100pg-pool-cloudsql-reconnect
    Fix Node.js pg-pool not reconnecting after CloudSQL connection reset (ECONNRESET). Use when: (1) Retries fail with same ECONNRESET error despite retry logic, (2) "Connection terminated" or "connection already closed" errors persist across retries, (3) "remaining connection slots…
  • 100cloud-run-job-cloudsql-setup
    Set up Cloud Run Jobs with CloudSQL connection. Use when: (1) Deploying long-running batch jobs that need database access, (2) Errors like "Cloud SQL Proxy not found" in container, (3) "password authentication failed" despite correct credentials, (4) Job creation fails with…
  • 100fai-sql-code-review-skill
    Review SQL code for correctness, performance, security, and maintainability across PostgreSQL, SQL Server, and MySQL. Use when auditing SQL in PRs or reviewing database migration scripts.
  • 100azuresql-db-connections
    Makes an app's database connections reliable against the local Azure SQL Database container (Private Preview) and, unchanged, against Azure SQL Database in the cloud: connection pooling plus retry/transient-fault handling. Use when the user mentions "connection pooling", "retry…
  • 100verify-service-connection
    Check the local OpenMeld Service connection loop. The computer is shown as connected only while the session is online, stopping is idle, and quitting does not reconnect. Use when changing the service run loop, toHealthProjection, or classifyConnectionFailureReason.
  • 100optimizing-database-connection-pooling
    Process use when you need to work with connection management. This skill provides connection pooling and management with comprehensive guidance and automation. Trigger with phrases like "manage connections", "configure pooling", or "optimize connection usage".

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.