OK so that script I gave you, the exact SQLAlchemy equivalent is:
from sqlalchemy import create_engine
e = create_engine("postgresql+psycopg2://scott:tiger@localhost/test",
connect_args={"connect_timeout": 2})
c = e.connect()
it also will accept it as:
e =
create_engine("postgresql+psycopg2://scott:tiger@localhost/test?connect_timeout=2")
however, in the above case the timeout is passed as a string "2" and not an
integer, which may interfere with it working, im not sure. the separate
connect_args approach is safer.
On Thu, Feb 27, 2020, at 12:57 PM, Karim Gillani wrote:
> Thank you for the great idea. I did not see the forest through the trees.
> Haha.
>
> We have the pscopyg2 script running and things seems to be working fine with
> that. We can see without the connect_timeout that the delay is about 2
> minutes and with the connect_timeout set, the delay is only 2 seconds.
>
> Next, we are going to wrap the script (if possible) with Alchemy only and
> test.
> Then after that, we will wrap flask-alchemy.
>
> Thanks
> Karim
>
>
>
> On Thursday, 27 February 2020 06:48:00 UTC-8, Mike Bayer wrote:
>> 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 <[email protected]> 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 [email protected].
>>>>>>> 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 [email protected].
>>>>> 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 [email protected].
>>>> 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 [email protected].
>>> 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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/c9d76a8f-a4cb-4219-8994-9594b25a0084%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/c9d76a8f-a4cb-4219-8994-9594b25a0084%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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/cce578e8-683e-41ac-94ba-6ff87e9d84bb%40www.fastmail.com.
