Reconnection Strategies for Psycopg in High-Usage Scenarios After Connection Failures

[matthew-k2001]

Hello everyone,

Our team is currently utilizing psycopg2, and we're considering a transition to psycopg3. We've encountered issues with our database during periods of high API usage, particularly with an increased number of read operations. The error message we receive is as follows: "Could not establish connection from the local to the remote side of the tunnel: open new channel ssh error: SSH session not active connection to server at 'localhost', port failed: server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request."

Despite employing SimplePoolConnection, we've found that all our connections become stale following this error. I'm reaching out to inquire about potential reconnection strategies that could help us maintain a fresh connection in the event that the current one goes stale.

Thanks

[dvarrazzo]

Check the pool documentation for this topic. You can use check() to check all the connections of the database and/or the check parameter in the pool constructor if you are ok to pay the price of an extra query per connection.

[matthew-k2001]

Thanks dvarrazzo for the link. So i did read the documentation for that. So just to reconfirm if the entire pool gets staled would the pool get replaced by a new connections?

[matthew-k2001]

Also could you confirm that this is the way to use the check parameter

Class Database: 
...
...
...
    async def async_connection_check(conn):
            async with conn.cursor() as cur:
                await cur.execute("SELECT 1") 
        
        def create_connection_pool(self):
            self._connection_pool = AsyncConnectionPool(conninfo=self.get_conn_str(), min_size=5, max_size=50, check = self.async_connection_check)
            return self._connection_pool

Thanks

[dvarrazzo]

Also would it be possible to show me how you implement the check parameter

It's written in the documentation page I linked, at < https://www.psycopg.org/psycopg3/docs/advanced/pool.html#connection-quality>. An implementation is already available: A simple implementation is available as the static method ConnectionPool.check_connection <https://www.psycopg.org/psycopg3/docs/api/pool.html#psycopg_pool.ConnectionPool.check_connection>, which can be used as: with ConnectionPool( ..., check=ConnectionPool.check_connection, ... ) as pool: ...

…

[matthew-k2001]

Hi dvarrazoo,
I am still facing an issue where SSH tunnels stop working after some time. Not sure if it's a psycopg3 error or sshtunnelling error. Is there way to AutoSSH to keep the SSH connection of the tunnel or restart the conection if lost.

[dvarrazzo]

It is a tunnel error.

[matthew-k2001]

Would you happen to know how to restart the tunnel without restarting the entire api?

[dvarrazzo]

Wrong project, sorry.

[matthew-k2001]

All good, Thanks for the help @dvarrazzo !