Port could not be cast to integer value as '5432,pg_secondary:5432'

[changchichung]

show uri

postgres@hqs150:~$ pg_autoctl show uri
        Type |    Name | Connection String
-------------+---------+-------------------------------
     monitor | monitor | postgres://autoctl_node@pg_monitor:5432/pg_auto_failover?sslmode=require
   formation | default | postgres://pg_primary:5432,pg_secondary:5432/mydatabase?target_session_attrs=read-write&sslmode=require

then I modified settings in my python application , update DATABASE_URL

export DATABASE_URL='postgres://pg_primary:5432,pg_secondary:5432/mydatabase?target_session_attrs=read-write&sslmode=require'

but the application won't start due to

  File "/home/app2/app2/config/settings/base.py", line 46, in <module>
    "default": env.db("DATABASE_URL", default=os.environ.get('CONNECTION_STRING', 'postgresql://@:5432/app2'))
  File "/home/app2/.pyenv/versions/app2-3.10.2/lib/python3.10/site-packages/environ/environ.py", line 204, in db_url
    return self.db_url_config(self.get_value(var, default=default), engine=engine)
  File "/home/app2/.pyenv/versions/app2-3.10.2/lib/python3.10/site-packages/environ/environ.py", line 402, in db_url_config
    'PORT': _cast_int(url.port) or '',
  File "/home/app2/.pyenv/versions/3.10.2/lib/python3.10/urllib/parse.py", line 178, in port
    raise ValueError(message) from None
ValueError: Port could not be cast to integer value as '5432,pg_secondary:5432'

I guess pg_auto_failover is working , but some how , the application won't accept the databse uri generated from pg_autofailover ?

is the uri format ok to all applications ?

postgres://pg_primary:5432,pg_secondary:5432/mydatabase?target_session_attrs=read-write&sslmode=require

any suggestion to fix it ?

[s4ke]

What python library are you using to connect?

Using multiple hosts in the url has to be supported by all your client libraries. If you use psycopg2 directly the url parsing is handled by libpq.

[s4ke]

Assuming that you are using something like SQLAlchemy to do this:

https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#specifying-multiple-fallback-hosts

SQLAlchemy uses a different connection string format.

[DimCitus]

Hi! As @s4ke is saying the URI format that Postgres exposes is not always supported directly by drivers and connectors, so you might have to adjust to your local requirements. Closing the issue then, because it's not really a pg_auto_failover problem here.

[changchichung]

What python library are you using to connect?

Using multiple hosts in the url has to be supported by all your client libraries. If you use psycopg2 directly the url parsing is handled by libpq.

I used django default DATABASE config , and did you mean psycopg2 support the URI generated by pg_auto_failover ?

[s4ke]

@changchichung

For django it's a bit tricky, but this should work (but does not use a connection string):

DATABASES = {
    'default': {
        'ENGINE': _db_engine,
        'NAME': environment.DB,
        'USER': environment.DB_USER,
        'PASSWORD': environment.DB_PASSWD,
        'HOST': environment.DB_HOSTS,
        'CONN_MAX_AGE': 0,
        'PORT': environment.DB_PORTS,
        'OPTIONS': {
            'application_name': f'applicationname',
            'options': f'-c search_path={environment.DB_SCHEMA}',
            'target_session_attrs': 'read-write',
            'connect_timeout': '30',
            'keepalives_idle': '300',
            'keepalives_interval': '75'
        },
        'ATOMIC_REQUESTS': True
    }
}

Ports and hosts are both strings with comma separated arguments

[s4ke]

@DimCitus I think this is not the first time this was asked. I answered this question some time ago (also for django) on slack. Should we maybe add a FAQ section or do you think since this is out of scope of this git repo, this should not be handled in the context of pg_auto_failover at all.

[DimCitus]

It would be nice to have documentation coverage for it of course. I would even accept a PR that implements a pg_autoctl show uri --django switch, and then we could add other providers of course. It's just that I don't have enough knowledge or exposure to what's needed here to do it myself.

[s4ke]

Since this was linked to from another place, let me mention that CONN_MAX_AGE=0 only really makes sense for a django database backend that does connection pooling. Please check this parameter out :)

[DimCitus]

Hey @s4ke; thanks for precision. Could you work on a template for the Django connection string that we could automatically fill-in from the pg_autoctl command line with the information we have, and provide to the user? Something that's known to always just work?

[s4ke]

This should take care of everything:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': '<db to connect to>',
        'USER': '<user to connect with>',
        'PASSWORD': '<password for your user>',
        'HOST': '<ip1>,<ip2>',
        'PORT': '5433,5433', # or whatever your hosts are listening on
        'OPTIONS': {
            'application_name': '<your application name, useful for debugging>',
            'options': f'-c search_path=<your database schema, public for default>',
            'target_session_attrs': 'read-write',
            'connect_timeout': '30',
            'keepalives_idle': '300',
            'keepalives_interval': '75'
        },
        'ATOMIC_REQUESTS': True
    }
}

[s4ke]

The tcp configs were hand optimized as sometimes the linux defaults caused issues in failover.

[s4ke]

Tbh, nowadays I am leaning more into the direction of using haproxy in my setups as I need it anyways for other parts of our stack. The above works great if you dont want extra infrastructure, but this HAProxy config works nicely:

https://github.com/neuroforgede/pg_auto_failover_ansible/wiki/HAProxy