cannot connect with Pgbouncer when using prefect

keven-huang commented 1 month ago

First check

[X] I added a descriptive title to this issue.
[X] I used the GitHub search to find a similar issue and didn't find it.
[X] I searched the Prefect documentation for this issue.
[X] I checked that this issue is related to Prefect and not one of its dependencies.

Bug summary

Recently I was trying to setup prefect server with Pgbouncer but failed. The output shows that Pgbouncer do not support jit argument, but when prefect server start, it will pass jit argument, which result in the Error.

Reproduction

1. Install Pgbouncer
2. install prefect == 2.19.7
3. prefect config set PREFECT_API_DATABASE_CONNECTION_URL="postgresql+asyncpg://{user}:{passwd}@{Pgbouncer_ip}:6432/{db_name}"
4. prefect config set PREFECT_API_URL="http://{prefect-location server ip}:4200/api"                                    
5. prefect server start

And we will found the output is

asyncpg.exceptions.ProtocolViolationError: unsupported startup parameter: jit

it shows that when prefect server start, it will pass argument jit to the database, but pgbouncer do not support command jit, so when we use pgbouncer as database backend of prefect, it will raise the Error.


### Error

```python3
Traceback (most recent call last):
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/prefect/_vendor/starlette/routing.py", line 736, in lifespan
    async with self.lifespan_context(app) as maybe_state:
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/contextlib.py", line 210, in __aenter__
    return await anext(self.gen)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/prefect/server/api/server.py", line 645, in lifespan
    await run_migrations()
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/prefect/server/api/server.py", line 529, in run_migrations
    await db.create_db()
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/prefect/server/database/interface.py", line 56, in create_db
    await self.run_migrations_upgrade()
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/prefect/server/database/interface.py", line 64, in run_migrations_upgrade
    await run_sync_in_worker_thread(alembic_upgrade)
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/prefect/utilities/asyncutils.py", line 136, in run_sync_in_worker_thread
    return await anyio.to_thread.run_sync(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/anyio/to_thread.py", line 33, in run_sync
    return await get_asynclib().run_sync_in_worker_thread(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/anyio/_backends/_asyncio.py", line 877, in run_sync_in_worker_thread
    return await future
           ^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/anyio/_backends/_asyncio.py", line 807, in run
    result = context.run(func, *args)
             ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/prefect/server/database/alembic_commands.py", line 24, in wrapper
    return fn(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/prefect/server/database/alembic_commands.py", line 53, in alembic_upgrade
    alembic.command.upgrade(alembic_config(), revision, sql=dry_run)
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/alembic/command.py", line 406, in upgrade
    script.run_env()
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/alembic/script/base.py", line 582, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
    module = load_module_py(module_id, path)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 113, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap_external>", line 940, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/prefect/server/database/migrations/env.py", line 175, in <module>
    apply_migrations()
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/prefect/utilities/asyncutils.py", line 292, in coroutine_wrapper
    return run_async_from_worker_thread(async_fn, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/prefect/utilities/asyncutils.py", line 222, in run_async_from_worker_thread
    return anyio.from_thread.run(call)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/anyio/from_thread.py", line 47, in run
    return asynclib.run_async_from_thread(func, *args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/anyio/_backends/_asyncio.py", line 906, in run_async_from_thread
    return f.result()
           ^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/concurrent/futures/_base.py", line 456, in result
    return self.__get_result()
           ^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/concurrent/futures/_base.py", line 401, in __get_result
    raise self._exception
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/prefect/server/database/migrations/env.py", line 168, in apply_migrations
    async with engine.connect() as connection:
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/ext/asyncio/base.py", line 121, in __aenter__
    return await self.start(is_ctxmanager=True)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/ext/asyncio/engine.py", line 273, in start
    await greenlet_spawn(self.sync_engine.connect)
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 201, in greenlet_spawn
    result = context.throw(*sys.exc_info())
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3276, in connect
    return self._connection_cls(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 146, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3300, in raw_connection
    return self.pool.connect()
           ^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 449, in connect
    return _ConnectionFairy._checkout(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
    rec = pool._do_get()
          ^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 179, in _do_get
    with util.safe_reraise():
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 177, in _do_get
    return self._create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
    return _ConnectionRecord(self)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 674, in __init__
    self.__connect()
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 900, in __connect
    with util.safe_reraise():
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 896, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/engine/create.py", line 643, in connect
    return dialect.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 620, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 932, in connect
    await_only(creator_fn(*arg, **kw)),
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 132, in await_only
    return current.parent.switch(awaitable)  # type: ignore[no-any-return,attr-defined] # noqa: E501
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 196, in greenlet_spawn
    value = await result
            ^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/asyncpg/connection.py", line 2329, in connect
    return await connect_utils._connect(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/asyncpg/connect_utils.py", line 991, in _connect
    conn = await _connect_addr(
           ^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/asyncpg/connect_utils.py", line 828, in _connect_addr
    return await __connect_addr(params, True, *args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jmhuang/anaconda3/envs/prefect2.19/lib/python3.11/site-packages/asyncpg/connect_utils.py", line 876, in __connect_addr
    await connected
asyncpg.exceptions.ProtocolViolationError: unsupported startup parameter: jit

Versions (`prefect version` output)

Version:             2.19.7
API version:         0.8.4
Python version:      3.11.9
Git commit:          60f05122
Built:               Fri, Jun 28, 2024 11:27 AM
OS/Arch:             linux/x86_64
Profile:             default
Server type:         server

Additional context

No response

WillRaphaelson commented 1 month ago

Thanks @keven-huang, we will accept this on our backlog to investigate, but would also welcome a contribution if you think you have a sense of what needs changing.

ekenheim commented 2 weeks ago

Having the same issue - no idea of solution

cicdw commented 2 weeks ago

Try setting ignore_startup_parameters=jit in your PGBouncer configuration.ini file as described here: https://www.pgbouncer.org/config.html. Let me know if that does the trick!

bakahoui commented 1 day ago

is also related to this issue

cicdw commented 1 day ago

To resolve this, we are going to look into exposing one or more Prefect settings that allow you to toggle the jit connection kwarg. We'll add this to both 2.x and 3.0.

PrefectHQ / prefect