CODEWITHSUNDEEP
postgresqlsqlalchemyfirewall
Dimitris
Dimitris

Reputation: 2531

sqlalchemy fails to connect to db after server is idle for a few hours

I have a fairly standard setup where the application server is separated by a firewall from the database server. The application us using sqlalchemy and the db is postgres.

The system works fine for a few hours, but once it remains idle for a long period it appears that the communication fails between the server and the db. The error log is:

WARNI [sqlalchemy.pool.QueuePool.0x...7310] [worker 26] Error closing cursor: cursor already closed

I set up the connection pool with the 'pool_recylce' setting set to 30mins, so that there are no connections lingering for more than 30 mins. The problem persists.

any ideas?

Upvotes: 4

Views: 2911

Answers (1)

Tometzky
Tometzky

Reputation: 23890

You probably have a stateful firewall between a client and a server, which is forgetting about connections that are idle for too long.

You need either to:

  • configure your firewall to remember connections longer — it may not be possible though;
  • configure keepalive on the server, for example like this: 
    tcp_keepalives_idle=600
tcp_keepalives_interval=30
tcp_keepalives_count=60
    Which means that if connection is idle for 10 minutes try to send keepalive probes every 30 seconds until response, close connection if 60 probes are ignored (connection is broken for 30 minutes);
  • configure keepalive in client using `keepalives_idle`, `keepalives_interval` and `keepalives_count` connection parameters; I don't know if it is possible in SQLAlchemy;

Disable pool_recycle as IMHO it works only if you close a connection.

Upvotes: 7

Related Questions