Correction du délai d'inactivité de Cloud SQL

Problème

Cloud SQL (et autres services PostgreSQL gérés) ferment les connexions inactives après un délai d'expiration (généralement 10 minutes). Les scripts de longue durée qui ouvrent une connexion DB tôt, puis effectuent du travail non-DB (requêtes HTTP, I/O fichier, scan CDX), puis tentent d'utiliser la connexion DB à nouveau recevront une erreur trompeuse.

Contexte / Conditions de déclenchement

• psycopg2.OperationalError: could not receive data from server: Operation timed out
• psycopg2.InterfaceError: connection already closed
• Script avec phases : configuration DB → long travail non-DB → écritures DB
• Utilisation de Google Cloud SQL, AWS RDS, ou Azure Database for PostgreSQL
• La connexion fonctionnait initialement, échoue après la période d'inactivité
• L'erreur apparaît APRÈS une phase qui n'utilise pas la DB (ex : crawling API, traitement fichier)

Solution

Pattern 1 : Différer la connexion DB (Préféré)

Structurez le code pour que la connexion DB s'ouvre APRÈS la phase non-DB :

# MAUVAIS : La connexion s'ouvre avant le long scan CDX
with VineDatabase() as db:
    ensure_schema(db)
    results = long_running_api_scan()  # 5-10 minutes, pas besoin de DB
    process_results(db, results)  # Connexion morte ici !

# BON : Scan CDX d'abord, puis connexion DB fraîche
results = long_running_api_scan()  # Pas de connexion DB ouverte

with VineDatabase() as db:  # Connexion fraîche quand vraiment nécessaire
    ensure_schema(db)
    process_results(db, results)

Pattern 2 : Logique de reconnexion (Pour les opérations batch longues)

Pour les opérations qui utilisent la DB mais pourraient dépasser le délai d'inactivité entre les écritures :

import psycopg2

def reconnect_db():
    """Crée une nouvelle connexion à la base de données."""
    db = VineDatabase()
    cursor = db._cursor()
    return db, cursor

def process_batch(db, cursor, items):
    for item in items:
        data = fetch_from_api(item)  # Appel réseau lent
        try:
            cursor.execute("INSERT INTO ...", data)
            db.conn.commit()
        except (psycopg2.OperationalError, psycopg2.InterfaceError):
            # La connexion est morte, se reconnecter et réessayer
            try:
                db.close()
            except Exception:
                pass
            db, cursor = reconnect_db()
            cursor.execute("INSERT INTO ...", data)
            db.conn.commit()

Pattern 3 : TCP Keepalive (Alternatif)

Configurez psycopg2 pour envoyer des paquets TCP keepalive :

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

Note : Cela peut ne pas fonctionner avec toutes les configurations du proxy Cloud SQL.

Vérification

1. Exécutez le script avec la longue phase non-DB
2. Confirmez qu'il n'y a pas de OperationalError ou InterfaceError après la période d'inactivité
3. Vérifiez que les données sont écrites dans la DB pendant la phase de fetch :

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

Exemple

Un crawler d'archive Vine qui :

1. Scan Wayback Machine CDX pour les profils archivés (107 pages, ~6 minutes)
2. Récupère ensuite et stocke chaque profil dans Cloud SQL

Le scan CDX n'a pas besoin de la DB, donc ouvrir la connexion avant signifie que la connexion reste inactive pendant 6 minutes et se fait tuer par Cloud SQL. Correction : exécuter le scan CDX d'abord, puis ouvrir la connexion DB pour la phase fetch-and-store.

Notes

• Le délai d'inactivité par défaut de Cloud SQL est ~10 minutes mais peut varier
• Le message d'erreur « could not receive data from server: Operation timed out » est trompeur—il ressemble à un problème réseau mais est en réalité un délai d'inactivité
• psycopg2.InterfaceError: connection already closed suit souvent l'OperationalError
• Pour les très longues opérations batch (heures), combinez Pattern 1 et Pattern 2
• Vérifiez aussi les réseaux autorisés Cloud SQL si la connexion échoue immédiatement (erreur différente)
• Lors de l'utilisation de nohup ou de processus en arrière-plan, assurez-vous que la sortie n'est pas bufférisée (PYTHONUNBUFFERED=1)