prefect
prefect copied to clipboard
PrefectHQ
cannot connect with Pgbouncer when using prefect
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
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.
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!
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.