PostgreSQL Migration: Resolving 'user_roles' Table Not Found Error

·Jan 28, 2025 12:23 PM

Hi, good morning. I am trying to migrate the persistance db from default sqlite to postgresql. I am using a custom schema in the target db, which is being specified as an env var, but I am getting the error I share bleow. Is it needed to create a predefined set of tables in the target schema? In this case the user in the connection URI has admin privileges, so it could generate the tables if needed by phoenix code. Please, I would appreciate your help if you found this problem before.

C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Scripts\python.exe C:\Users\USUARIO\Desktop\Repos\clay-rag\utils\run_phoenix_app.py 
C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\phoenix\otel\otel.py:227: UserWarning: Could not infer collector endpoint protocol, defaulting to HTTP.
  warnings.warn("Could not infer collector endpoint protocol, defaulting to HTTP.")
OpenTelemetry Tracing Details
|  Phoenix Project: gracia-chatbot
|  Span Processor: SimpleSpanProcessor
|  Collector Endpoint: http://localhost:4317/
|  Transport: HTTP
|  Transport Headers: {'api_key': '****'}
|  
|  Using a default SpanProcessor. `add_span_processor` will overwrite this default.
|  
|  `register` has set this TracerProvider as the global OpenTelemetry default.
|  To disable this behavior, call `register` with `set_global_tracer_provider=False`.

⚠️ PHOENIX_COLLECTOR_ENDPOINT is set to http://localhost:6006/.
⚠️ This means that traces will be sent to the collector endpoint and not this app.
⚠️ If you would like to use this app to view traces, please unset this environmentvariable via e.g. `del os.environ['PHOENIX_COLLECTOR_ENDPOINT']` 
⚠️ You will need to restart your notebook to apply this change.
ERROR:    Traceback (most recent call last):
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 514, in _prepare_and_execute
    prepared_stmt, attributes = await adapt_connection._prepare(
                                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 760, in _prepare
    prepared_stmt = await self._connection.prepare(
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\asyncpg\connection.py", line 635, in prepare
    return await self._prepare(
           ^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\asyncpg\connection.py", line 653, in _prepare
    stmt = await self._get_statement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\asyncpg\connection.py", line 432, in _get_statement
    statement = await self._protocol.prepare(
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "asyncpg\\protocol\\protocol.pyx", line 165, in prepare
asyncpg.exceptions.UndefinedTableError: relation "user_roles" does not exist

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\engine\default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 572, in execute
    self._adapt_connection.await_(
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\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 "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 196, in greenlet_spawn
    value = await result
            ^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 550, in _prepare_and_execute
    self._handle_exception(error)
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 501, in _handle_exception
    self._adapt_connection._handle_exception(error)
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 784, in _handle_exception
    raise translated_error from error
sqlalchemy.dialects.postgresql.asyncpg.AsyncAdapt_asyncpg_dbapi.ProgrammingError: <class 'asyncpg.exceptions.UndefinedTableError'>: relation "user_roles" does not exist

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\starlette\routing.py", line 693, in lifespan
    async with self.lifespan_context(app) as maybe_state:
  File "C:\Program Files\Python311\Lib\contextlib.py", line 210, in __aenter__
    return await anext(self.gen)
           ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\fastapi\routing.py", line 133, in merged_lifespan
    async with original_context(app) as maybe_original_state:
  File "C:\Program Files\Python311\Lib\contextlib.py", line 210, in __aenter__
    return await anext(self.gen)
           ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\fastapi\routing.py", line 133, in merged_lifespan
    async with original_context(app) as maybe_original_state:
  File "C:\Program Files\Python311\Lib\contextlib.py", line 210, in __aenter__
    return await anext(self.gen)
           ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\fastapi\routing.py", line 133, in merged_lifespan
    async with original_context(app) as maybe_original_state:
  File "C:\Program Files\Python311\Lib\contextlib.py", line 210, in __aenter__
    return await anext(self.gen)
           ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\fastapi\routing.py", line 133, in merged_lifespan
    async with original_context(app) as maybe_original_state:
  File "C:\Program Files\Python311\Lib\contextlib.py", line 210, in __aenter__
    return await anext(self.gen)
           ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\phoenix\server\app.py", line 485, in lifespan
    await res
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\phoenix\db\facilitator.py", line 46, in __call__
    await fn(session)
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\phoenix\db\facilitator.py", line 57, in _ensure_enums
    existing = set([_ async for _ in await session.stream_scalars(select(distinct(column)))])
                                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\ext\asyncio\session.py", line 749, in stream_scalars
    result = await self.stream(
             ^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\ext\asyncio\session.py", line 694, in stream
    result = await greenlet_spawn(
             ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 201, in greenlet_spawn
    result = context.throw(*sys.exc_info())
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\orm\session.py", line 2362, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\orm\session.py", line 2247, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\orm\context.py", line 305, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\sql\elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 2355, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\engine\default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 572, in execute
    self._adapt_connection.await_(
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\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 "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 196, in greenlet_spawn
    value = await result
            ^^^^^^^^^^^^
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 550, in _prepare_and_execute
    self._handle_exception(error)
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 501, in _handle_exception
    self._adapt_connection._handle_exception(error)
  File "C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 784, in _handle_exception
    raise translated_error from error
sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.UndefinedTableError'>: relation "user_roles" does not exist
[SQL: SELECT DISTINCT user_roles.name 
FROM user_roles]
(Background on this error at: https://sqlalche.me/e/20/f405)

ERROR:    Application startup failed. Exiting.
🌍 To view the Phoenix app in your browser, visit http://localhost:6006/
💽 Your data is being persisted to postgresql://postgres:pass@host-url:5432/data-warehouse
📖 For more information on how to use Phoenix, check out https://docs.arize.com/phoenix

C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Scripts\python.exe C:\Users\USUARIO\Desktop\Repos\clay-rag\utils\run_phoenix_app.py C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Lib\site-packages\phoenix\otel\otel.py:227: UserWarning: Could not infer collector endpoint protocol, defaulting to HTTP. warnings.warn("Could not infer collector endpoint protocol, defaulting to HTTP.") OpenTelemetry Tracing Details | Phoenix Project: gracia-chatbot | Span Processor: SimpleSpanProcessor | Collector Endpoint: http://localhost:4317/ | Transport: HTTP | Transport Headers: {'api_key': '****'} | | Using a default SpanProcessor. `add_span_processor` will overwrite this default. | | `register` has set this TracerProvider as the global OpenTelemetry default. | To disable this behavior, call `register` with `set_global_tracer_provider=False`. ⚠️ PHOENIX_COLLECTOR_ENDPOINT is set to http://localhost:6006/. ⚠️ This means that traces will be sent to the collector endpoint and not this app. ⚠️ If you would like to use this app to view traces, please unset this environmentvariable via e.g. `del os.environ['PHOENIX_COLLECTOR_ENDPOINT']` ⚠️ You will need to restart your notebook to apply this change. agents_telemetry

(venv) PS C:\Users\USUARIO\Desktop\Repos\clay-rag> C:\Users\USUARIO\Desktop\Repos\clay-rag\venv\Scripts\python.exe -m phoenix.server.main serve Running migrations on the database. --------------------------- 2025-01-28 13:30:41,931 INFO sqlalchemy.engine.Engine select pg_catalog.version() 2025-01-28 13:30:41,932 INFO sqlalchemy.engine.Engine [raw sql] {} 2025-01-28 13:30:42,222 INFO sqlalchemy.engine.Engine select current_schema() 2025-01-28 13:30:42,222 INFO sqlalchemy.engine.Engine [raw sql] {} 2025-01-28 13:30:42,506 INFO sqlalchemy.engine.Engine show standard_conforming_strings 2025-01-28 13:30:42,507 INFO sqlalchemy.engine.Engine [raw sql] {} 2025-01-28 13:30:43,732 INFO sqlalchemy.engine.Engine BEGIN (implicit) 2025-01-28 13:30:43,742 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace WHERE pg_catalog.pg_class.relname = %(table_name)s::VARCHAR AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR, %(param_3)s::VARCHAR, %(param_4)s::VARCHAR, %(param_5)s::VARCHAR]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s::VARCHAR 2025-01-28 13:30:43,744 INFO sqlalchemy.engine.Engine [generated in 0.00173s] {'table_name': 'alembic_version', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'} 2025-01-28 13:30:43,893 INFO sqlalchemy.engine.Engine SELECT alembic_version.version_num FROM alembic_version 2025-01-28 13:30:43,893 INFO sqlalchemy.engine.Engine [generated in 0.00055s] {} 2025-01-28 13:30:44,094 INFO sqlalchemy.engine.Engine COMMIT --------------------------- Migrations completed in 3.562 seconds. INFO: Started server process [7216] INFO: Waiting for application startup. v7.9.2 | | Join our Community | https://join.slack.com/t/arize-ai/shared_invite/zt-1px8dcmlf-fmThhDFD_V_48oU7ALan4Q | | Leave us a Star | https://github.com/Arize-ai/phoenix | | Documentation | https://docs.arize.com/phoenix | | Phoenix Server | Phoenix UI: http://0.0.0.0:6006 | Authentication: False

ERROR: Application startup failed. Exiting. 🌍 To view the Phoenix app in your browser, visit http://localhost:6006/ 💽 Your data is being persisted to postgresql://postgres:pass@host-url:5432/data-warehouse 📖 For more information on how to use Phoenix, check out https://docs.arize.com/phoenix

15 comments