https://prefect.io
Join Slack
Hi All, I’m working on moving away from using the Bitnami/Postgres subchart to an Azure DB for Post...
# ask-community
m
Hi All, I’m working on moving away from using the Bitnami/Postgres subchart to an Azure DB for Postgres instance. After fixing the dialect name, I’m currently running into this error (will post in thread).
✅ 1
Copy code
INFO:     Started server process [8]
INFO:     Waiting for application startup.
ERROR:    Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 645, in lifespan
    async with self.lifespan_context(app):
  File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 540, in __aenter__
    await self._router.startup()
  File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 622, in startup
    await handler()
  File "/usr/local/lib/python3.10/site-packages/prefect/orion/api/server.py", line 341, in run_migrations
    await db.create_db()
  File "/usr/local/lib/python3.10/site-packages/prefect/orion/database/interface.py", line 55, in create_db
    await self.run_migrations_upgrade()
  File "/usr/local/lib/python3.10/site-packages/prefect/orion/database/interface.py", line 63, in run_migrations_upgrade
    await run_sync_in_worker_thread(alembic_upgrade)
  File "/usr/local/lib/python3.10/site-packages/prefect/utilities/asyncutils.py", line 68, in run_sync_in_worker_thread
    return await anyio.to_thread.run_sync(call, cancellable=True)
  File "/usr/local/lib/python3.10/site-packages/anyio/to_thread.py", line 31, in run_sync
    return await get_asynclib().run_sync_in_worker_thread(
  File "/usr/local/lib/python3.10/site-packages/anyio/_backends/_asyncio.py", line 937, in run_sync_in_worker_thread
    return await future
  File "/usr/local/lib/python3.10/site-packages/anyio/_backends/_asyncio.py", line 867, in run
    result = context.run(func, *args)
  File "/usr/local/lib/python3.10/site-packages/prefect/orion/database/alembic_commands.py", line 29, in alembic_upgrade
    alembic.command.upgrade(alembic_config(), revision, sql=dry_run)
  File "/usr/local/lib/python3.10/site-packages/alembic/command.py", line 322, in upgrade
    script.run_env()
  File "/usr/local/lib/python3.10/site-packages/alembic/script/base.py", line 569, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/local/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 883, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/usr/local/lib/python3.10/site-packages/prefect/orion/database/migrations/env.py", line 147, in <module>
    apply_migrations()
  File "/usr/local/lib/python3.10/site-packages/prefect/utilities/asyncutils.py", line 197, in coroutine_wrapper
    return run_async_from_worker_thread(async_fn, *args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/prefect/utilities/asyncutils.py", line 148, in run_async_from_worker_thread
    return anyio.from_thread.run(call)
  File "/usr/local/lib/python3.10/site-packages/anyio/from_thread.py", line 49, in run
    return asynclib.run_async_from_thread(func, *args)
  File "/usr/local/lib/python3.10/site-packages/anyio/_backends/_asyncio.py", line 970, in run_async_from_thread
    return f.result()
  File "/usr/local/lib/python3.10/concurrent/futures/_base.py", line 458, in result
    return self.__get_result()
  File "/usr/local/lib/python3.10/concurrent/futures/_base.py", line 403, in __get_result
    raise self._exception
  File "/usr/local/lib/python3.10/site-packages/prefect/orion/database/migrations/env.py", line 137, in apply_migrations
    engine = await db_interface.engine()
  File "/usr/local/lib/python3.10/site-packages/prefect/orion/database/interface.py", line 73, in engine
    engine = await self.database_config.engine()
  File "/usr/local/lib/python3.10/site-packages/prefect/orion/database/configurations.py", line 112, in engine
    engine = create_async_engine(self.connection_url, echo=self.echo, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/ext/asyncio/engine.py", line 43, in create_async_engine
    sync_engine = _create_engine(*arg, **kw)
  File "<string>", line 2, in create_engine
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/deprecations.py", line 309, in warned
    return fn(*args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 548, in create_engine
    dbapi = dialect_cls.dbapi(**dbapi_args)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 811, in dbapi
    import psycopg2
ModuleNotFoundError: No module named 'psycopg2'
r
Hi Mark, Prefect uses 
asyncpg
instead of 
psycopg2
, which I believe is why you are seeing that error. Does your new connection string start with 
postgresql+psycopg2://
and if so, does changing it to 
postgresql+asyncpg://
fix the issue?
m
Hi Ryan, I’m using the postgresql:// dialect without specifying psycopg2. For transparency, I just grabbed the postgres connection string in azure portal and replaced the dialect from postgres to postgresql. postgres://USER:{your_password}@database-name.postgres.database.azure.com/postgres?sslmode=require TO postgresql://USER:{your_password}@database-name.postgres.database.azure.com/postgres?sslmode=require Is this the format we are expecting to put into the secret to pass?
r
Makes sense - we use SQLAlchemy as our ORM, and it will default to using 
psycopg2
for Postgres. The connection string needs to be in a format SQLAlchemy can work with, and I'm not sure if it will understand the ?sslmode=require parameter. But if I remember correctly Azure Postgres is set to require SSL/TLS connections anyway, so the connection will use SSL even without that parameter. I recommend trying:
Copy code
postgres:asyncpg//USER:{your_password}@database-name.postgres.database.azure.com/postgres
as your connection string
m
Just tried a few variants to the and got a:
Copy code
File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 645, in lifespan
    async with self.lifespan_context(app):
  File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 540, in __aenter__
    await self._router.startup()
  File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 622, in startup
    await handler()
  File "/usr/local/lib/python3.10/site-packages/prefect/orion/api/server.py", line 340, in run_migrations
    db = provide_database_interface()
  File "/usr/local/lib/python3.10/site-packages/prefect/orion/database/dependencies.py", line 49, in provide_database_interface
    dialect = get_dialect(connection_url)
  File "/usr/local/lib/python3.10/site-packages/prefect/orion/utilities/database.py", line 630, in get_dialect
    url = sa.engine.url.make_url(obj)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/url.py", line 732, in make_url
    return _parse_url(name_or_url)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/url.py", line 793, in _parse_url
    raise exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Could not parse SQLAlchemy URL from string
r
I see I had a typo in the original and it was missing the post; my apologies. I intended it to be:
Copy code
<postgresql+asyncpg://USER>:{your_password}@database-name.postgres.database.azure.com/postgres
It might need the port added as well:
Copy code
<postgresql+asyncpg://USER>:{your_password}@database-name.postgres.database.azure.com:5432/postgres
m
Tried it with postrgres+asyncpg I’ll try with the port.
Additionally will try postgresql+asyncpg
r
Yes, sorry; postgresql not postgres. I just edited them
m
Just wanted to Follow Up: The connection seems to be established but is running into this issue.
Copy code
sqlalchemy.exc.DBAPIError: (sqlalchemy.dialects.postgresql.asyncpg.Error) <class 'asyncpg.exceptions.FeatureNotSupportedError'>: extension "pg_trgm" is not allow-listed for "azure_pg_admin" users in Azure Database for PostgreSQL
HINT: to see the full allow list of extensions, please run: "show azure.extensions;"
[SQL: CREATE EXTENSION IF NOT EXISTS pg_trgm;]
(Background on this error at: <https://sqlalche.me/e/14/dbapi>)
Will be looking into what I can do to get this extension allowed. If no glaring solutions/insights, I can investigate and post again once I have a better understanding of what this is.
r
Some of the Postgres functionality we use requires the 
pg_trgm
extension. I spun up an Azure Postgres instance, so I will check and see if it supports enabling 
pg_trgm
. I believe it worked last time I tried
m
Sounds good. Let me know if there’s any insights or adjustments made that I should be mindful of. I’m using Azure DB for Postgresql (Flexible Server)
r
It doesn't work with the Flexible server default settings, but you if you follow these instructions and check off the PG_TGRM extension, you can then use pgAdmin or another Postgres client app to run 
CREATE EXTENSION IF NOT EXISTS pg_trgm;
m
Which instructions are you referring to? Assuming just the instruction to enable pg_trgm?
r
I used the instructions on how to do it via the Azure portal, but they show how to do it via CLI as well
m
Thanks, Ryan!
6 Views
Open in Slack
PreviousNext
Powered by

Prefect Community

Bring your towel and join one of the fastest growing data communities. Welcome to our second-generation open source orchestration platform, a completely rethought approach to dataflow automation.

Powered by