Can you write a script that uses psycopg2 directly and see if you can replicate the issue in that way? this would elimiante SQLAlchemy as part of the issue and you can interact with the psycopg2 developers directly:
import psycopg2 connection = psycopg2.connect( user="scott", password="tiger", host="localhost", database="test", connect_timeout=2, ) There's no need to do pdb inside of psycopg2 itself. Within SQLAlchemy you could do this at lib/sqlalchemy/dialects/postgresql/psycopg2.py however only if using raw psycopg2 above proves that it solves your connection issue. On Wed, Feb 26, 2020, at 11:19 AM, Karim Gillani wrote: > Thanks. I don't believe this is a Red Hat issue as much as a configuration > issue. This is because I can replicate the issue using PSQL and using the > connect_timeout argument in PSQL, I can fix it. With the complexity of using > flask-sqlalchemy which uses sqlalchemy to pass arguments to psycopg2 to > libpq, it is difficult to see where the issue actually is. Based on the links > you provided, I am passing the connect_timeout parameter correctly. I am not > sure what to do now. I am guessing I will need to somehow figure out if the > parameter is being passed. I think I will play with getting > connection.get_dsn_parameters() function to display the parameters. > > Karim > > On Wed, 26 Feb 2020 at 07:30, Mike Bayer <mike...@zzzcomputing.com> wrote: >> __ >> This would be a psycopg2 level setting which are documented at >> https://www.psycopg.org/docs/module.html#psycopg2.connect and the available >> values are ultimately at >> https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS >> . On the SQLAlchemy side you pass these to create_engine using >> connect_args: >> https://docs.sqlalchemy.org/en/13/core/engines.html?highlight=connect_args#custom-dbapi-args >> >> >> >> On Wed, Feb 26, 2020, at 12:11 AM, Karim Gillani wrote: >>> We are using openshift which uses HAProxy. What I am looking for is why the >>> connect_timeout may not being applied so that we can retry the call after >>> re-connect to the other database pod. >>> >>> It is taking **up to two minutes** before I get the >>> psycopg2.OperationalError. It's very strange. The code will automatically >>> retry and work after this error shows up. I am guessing but I don't think >>> my setup is passing the connect_timeout to libpq. >>> >>> Thanks >>> >>> On Tuesday, 25 February 2020 10:37:19 UTC-8, Mike Bayer wrote: >>>> "could not connect" means your server is unreachable from a network >>>> standpoint. You want to make sure the server is running and that it is >>>> routable over the network you are trying to connect from. >>>> >>>> in an HA scenario, depending on what software is in use and how it does >>>> switchover, there can be long network lags, such as if it is moving a VIP >>>> from one machine to another. You'd want to be using a proxy server such as >>>> HAProxy or PGBouncer rather than relying upon changing network routing. >>>> >>>> >>>> >>>> On Tue, Feb 25, 2020, at 12:07 PM, Karim Gillani wrote: >>>>> Please note I am using a Pool. And some other connections seem to recover >>>>> fine. After two minutes I get this message and things start working again: >>>>> >>>>> ==> AE K:11oh392; T:1; E:(psycopg2.OperationalError) could not connect to >>>>> server: Connection timed out> Is the server running on host >>>>> "patroni-mastertheq" (172.50.46.180) and accepting> TCP/IP connections on >>>>> port 5432?>>(Background on this error at: http://sqlalche.me/e/e3q8 ) >>>>> Thanks >>>>> Karim >>>>> >>>>> On Tuesday, 25 February 2020 08:49:19 UTC-8, Karim Gillani wrote: >>>>>> I am having an issue where when my HA postgres database drops and is >>>>>> automatically moved to a backup database, the timeout doesn't seem to be >>>>>> working. >>>>>> >>>>>> My config.py shows the following: >>>>>> >>>>>> SQLALCHEMY_DATABASE_URI = >>>>>> '{engine}://{user}:{password}@{host}:{port}/{name}'.format( >>>>>> >>>>>> engine=DB_ENGINE, >>>>>> >>>>>> user=DB_USER, >>>>>> >>>>>> password=DB_PASSWORD, >>>>>> >>>>>> host=DB_HOST, >>>>>> >>>>>> port=DB_PORT, >>>>>> >>>>>> name=DB_NAME, >>>>>> >>>>>> ) >>>>>> SQLALCHEMY_ENGINE_OPTIONS >>>>>> = { 'pool_size' : pool_size, 'max_overflow' : max_overflow, >>>>>> 'pool_pre_ping' : True, 'pool_timeout': DB_POOL_TIMEOUT, 'pool_recycle': >>>>>> 3600, 'connect_args': { 'connect_timeout': 5, 'options' : '-c >>>>>> statement_timeout=5000' } } >>>>>> >>>>>> Is there something I set wrong? My timeout seems to be about 2 minutes >>>>>> instead of 5 secs. >>>>>> >>>>>> Any help would be appreciated, >>>>>> >>>>>> Thanks >>>>>> Karim >>>>>> >>>>> >>>>> -- >>>>> SQLAlchemy - >>>>> The Python SQL Toolkit and Object Relational Mapper >>>>> >>>>> http://www.sqlalchemy.org/ >>>>> >>>>> To post example code, please provide an MCVE: Minimal, Complete, and >>>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>>>> description. >>>>> --- >>>>> You received this message because you are subscribed to the Google Groups >>>>> "sqlalchemy" group. >>>>> To unsubscribe from this group and stop receiving emails from it, send an >>>>> email to sqlal...@googlegroups.com. >>>>> To view this discussion on the web visit >>>>> https://groups.google.com/d/msgid/sqlalchemy/ab3e5681-d9be-468f-8160-97bddcca57fb%40googlegroups.com >>>>> >>>>> <https://groups.google.com/d/msgid/sqlalchemy/ab3e5681-d9be-468f-8160-97bddcca57fb%40googlegroups.com?utm_medium=email&utm_source=footer>. >>>> >>> >>> -- >>> SQLAlchemy - >>> The Python SQL Toolkit and Object Relational Mapper >>> >>> http://www.sqlalchemy.org/ >>> >>> To post example code, please provide an MCVE: Minimal, Complete, and >>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>> description. >>> --- >>> You received this message because you are subscribed to the Google Groups >>> "sqlalchemy" group. >>> To unsubscribe from this group and stop receiving emails from it, send an >>> email to sqlalchemy+unsubscr...@googlegroups.com. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/sqlalchemy/06c8e91e-4f36-4192-90d0-acbe840478ef%40googlegroups.com >>> >>> <https://groups.google.com/d/msgid/sqlalchemy/06c8e91e-4f36-4192-90d0-acbe840478ef%40googlegroups.com?utm_medium=email&utm_source=footer>. >> >> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> >> To post example code, please provide an MCVE: Minimal, Complete, and >> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >> description. >> --- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to sqlalchemy+unsubscr...@googlegroups.com. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/sqlalchemy/82280db0-b63e-4e31-9657-66eb8bcd79b7%40www.fastmail.com >> >> <https://groups.google.com/d/msgid/sqlalchemy/82280db0-b63e-4e31-9657-66eb8bcd79b7%40www.fastmail.com?utm_medium=email&utm_source=footer>. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAOpL_VFw3U7vZhiLnQ8gB7-zUe%3Dc14QU019w9bQ25B4jPs6MvA%40mail.gmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/CAOpL_VFw3U7vZhiLnQ8gB7-zUe%3Dc14QU019w9bQ25B4jPs6MvA%40mail.gmail.com?utm_medium=email&utm_source=footer>. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/7b9ca4ba-320b-45c1-b731-e2605cb26150%40www.fastmail.com.
